Using Histogram in Excel VBA

[ktwclark]

Hi

Any idea how to use the histogram from the Analysis Toolpak in Excel through VBA. I can use it fine manually but not through VBA. I recorded a macro of the steps I took to manually produce one, but the code doesn't work when I make it part of my macro.

Here's the code

Application.Run "ATPVBAEN.XLA!Histogram", _ ActiveSheet.Range("$Z$2:$Z$" & Trim(LastCell)), _
ActiveSheet.Range("$AB$1&quot, , False, False, False, False

I got an error saying it couldn't find the histogram macro so I changed the path to below but still get the same response.

Application.Run "C:\Program Files\Microsoft _ Office\Office\Library\Analysis\ATPVBAEN.XLA!Histogram", _ ActiveSheet.Range("$Z$2:$Z$" & Trim(LastCell)), _ ActiveSheet.Range("$AB$1&quot, , False, False, False, False

Lastcell holds the last cell in the range to process. Column Z from row 2 to lastcell holds the data, AB1 is the output cell, but I don't see where the bins are in the code. They should be from AE1 to AO1 and go from -5 to +5.

Help. I don't want to create my own macro to do it when Excel has one built in.

Another challenge for you.........

 

[xlhelp]

This is one of those silly questions:

Have you made sure that under Tools, Add-ins, "Analysis Toolpak - VBA" is installed?

Also, make sure when you go to the VB Editor window you see ATPVBAEN.XLA is loaded. Member AAAA (Association Against Acronym Abuse)

 

[ETID]

As an alternate...have you tried the "frequency array" formula?

Then chart that range as a column chart.

I've done this with great success....

 

[ktwclark]

Yes, I've installed the Analysis Toolpak, yes, atpvbaen.xla is loaded. Did you try and record a macro while manually using the Histogram? See what happens and post your code if you can get it working in VBA. I don't think it's as straight forward as that, though.

I've written my own code that does the job, only took 10 minutes, but for future reference.......