Newbie-question: recorded macro does not work - why?

[Michael Moser]

I have enabled the add-in ("Analysis ToolPak" and ("Analysis ToolPak
VBA") in Excel (this package seems to come with Excel by default - I
didn't have to install anything special, just had to check the tick-mark
in Tools => Add-Ins). This made another Tools-menu entry appear: "Data
Analysis"

This all works fine, but now I wanted to automatize a few things to save
me some substantial typing and clicking when (re-)generating charts.

So I recorded a macro that uses a function from that add-in (Tools =>
Data Analysis => Histogram). In the recorded macro of this action shows
up as:
----------------------
....
Application.Run "ATPVBAEN.XLA!Histogram", , , , False, False,
False, _
False

....
----------------------

When I want to execute/replay the macro I always get an error popup
telling me:

"Histogram - inout range must be a contiguous reference".

I thought, that maybe the series of empty ', , , ,' indicates some
missing or non-recorded range inputs (namely the input range, the
"buckets" and the output range that I entered during recording) and
tried to fill the void so that the line read
----------------------
....
Application.Run "ATPVBAEN.XLA!Histogram", "$B$10:$K$508",
"$O$2:$O$255", "$P$1:$Q$256", False, False, False, _
False

....
----------------------
.... but that did not work either and so I am stuck.

Any idea, what to do here or what I am missing? How/where can I find
some documentation re. what parameters that Histogram-operation expects?

Any help would be greatly appreciated!

Michael

 

[Guest]

First, your arugments have to be passed as Ranges, not strings.

for instance, if I record it I get:

Application.Run "ATPVBAEN.XLA!Histogram", _
ActiveSheet.Range("$B$4:$B$27"), _
"", ActiveSheet.Range("$D$4:$D$9"), _
False, False, False, True

as an example.

You can find documentation in the help files for the Excel4 macro language:

http://office.microsoft.com/downloads/2000/Xlmacro.aspx

http://support.microsoft.com/default.aspx?scid=kb;en-us;128185&Product=xlw
Macrofun.exe File Available on Online Services
xl97 (old winhelp format)

http://support.microsoft.com/default.aspx?scid=kb;en-us;143466&Product=xlw
XL97: Macro97.exe File Available on Online Services
xl97 (old winhelp format)

 

[Michael Moser]

Thanks - that was very helpful indeed! I installed a couple of help
files re. VBA and Excel from the referenced pages.
And also your remark re. using Ranges helped a LOT, i.e. the function
now gets called and executes without error message.

However, I am still unable to steer it such that it places the result
into the same sheet from where it reads the input (something that is
possible via the dialog). In none of the help files mentioned nor when
googling nor searching MSN did I find any decent description of the
options and parameters for the misc. functions provided by the Data
Analysis Add-in.
When I found no API docu I tried to open the macros themselves hoping to
be able to find out, what parameters it expects, but the editor only
asks for a password and won't show any source code... Another example
that undocumented, closed source code can turn practically useless :-(

Does anyone have a pointer or API summary or *some* documentation for
these?

Michael

First, your arugments have to be passed as Ranges, not strings.

for instance, if I record it I get:

Application.Run "ATPVBAEN.XLA!Histogram", _
ActiveSheet.Range("$B$4:$B$27"), _
"", ActiveSheet.Range("$D$4:$D$9"), _
False, False, False, True

as an example.

You can find documentation in the help files for the Excel4 macro
language:

http://office.microsoft.com/downloads/2000/Xlmacro.aspx

http://support.microsoft.com/default.aspx?scid=kb;en-us;128185&Product=xlw
Macrofun.exe File Available on Online Services
xl97 (old winhelp format)

http://support.microsoft.com/default.aspx?scid=kb;en-us;143466&Product=xlw
XL97: Macro97.exe File Available on Online Services
xl97 (old winhelp format)

...

 

[Michael Moser]

BTW: How did you get the macro recorder to also record these ranges???

If I record when manually creating a histogram, the first few parameters
end up being empty as I described in my intial append, i.e.: