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

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

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...85&Product=xlw
Macrofun.exe File Available on Online Services
xl97 (old winhelp format)

http://support.microsoft.com/default...66&Product=xlw
XL97: Macro97.exe File Available on Online Services
xl97 (old winhelp format)

--
Regards,
Tom Ogilvy



"Michael Moser" wrote:

> 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
>
>
>

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


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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...85&Product=xlw
> Macrofun.exe File Available on Online Services
> xl97 (old winhelp format)
>
> http://support.microsoft.com/default...66&Product=xlw
> XL97: Macro97.exe File Available on Online Services
> xl97 (old winhelp format)
>
> --
> Regards,
> Tom Ogilvy
>
...

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.:
----------------------
....
Application.Run "ATPVBAEN.XLA!Histogram", , , , _
False, False, False, False
....
----------------------

Is there some "more complete" recording mode or some trick to do this?

Michael


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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...85&Product=xlw
> Macrofun.exe File Available on Online Services
> xl97 (old winhelp format)
>
> http://support.microsoft.com/default...66&Product=xlw
> XL97: Macro97.exe File Available on Online Services
> xl97 (old winhelp format)
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Michael Moser" wrote:
>
>> 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
>>
>>
>>