Drawing a histogram using a macro

[dcwm]

I want to draw some histograms of data on a worksheet, and I want a macro to do this.

I switched on "record macro", and drew a histogram of some of the data using Data > Data Analysis > Select Histogram and then filling in the ranges and so on. Then I stopped recording and looked at the resulting macro.

This looked like :
<code>
macro3
Application.Run "ATPVBAEN.XLAM!Histogram", ActiveSheet.Range(suchandsuch).........
.....(more stuff)
</code>

So then I rubbed out the histogram data columns, I rubbed out the chart of the histogram, saved everything, close the Excel workbook, re-open the Excel workbook.

Then I ran macro3, expecting it to re-draw the histogram.

But it didn't. It gave a debug error, highlighting in yellow the item I have summarised between <code>..</code>

Could someone explain this to me, please ? What should I have done ?

[tony h]

I think there is something missing from your post.

The first line assumes there is an active sheet. Is there? use sheet("name").ativate.

[dcwm]

Tony,

I ensured that the sheet was active by commenting out the Appl;ication.run... temporarily and making the macro write something in a cell. But when I try again I still get Run-time error 1004. Methos 'Run' of object '_Application' failed

[buran]

Histogram is part of Analysis Toolpak (ATP) addin.
To use in macro you have to set reference to Analysis toolpak vba, which is another addin. Look the last para here:
http://www.cpearson.com/excel/Callin...ionsInVBA.aspx

[dcwm]

This is what I have at the moment, and it gives an error
<code>
Sub Macro3()
'
' This is the macro for drawing a histogram
'
' Row=1 .Range("$L$11:$L$104") is the source of the data which is in R11 - R104 col=12
' 12=L
'
' Row=2 .Range("$CV$1:$CV$40") is the Bin values which in this case are in col=100
' rows 1 to 40
'
' Row=2 .Range("$DP$40") is the point at which the figures for the histogram
' start to be written. The figure is drawn level and to the right
'
' The last "True" is something to do with labels but I have not figured it out yet
'
' Something strange, if I run this as a macro it does not work and gives a debug error
'
Dim sj As String, sfilename As String
Dim Res As Variant
'
sfilename = ActiveWorkbook.Name
Set wkb1 = Workbooks(sfilename)
sj = "ExpData"
wkb1.Worksheets(sj).Activate
'
' ActiveSheet.Cells(10, 103) = "DOG" ' This worked so I must have an active sheet
'
Application.DisplayAlerts = False

Application.Run "ATPVBAEN.XLAM!Histogram", ActiveSheet.Range("$L$11:$L$104") _
, ActiveSheet.Range("$DP$40"), ActiveSheet.Range("$CV$1:$CV$40"), False, _
False, True, True



End Sub

</code>

Can anyone assist further, please ?

[shg]

Please take a few minutes to read the forum rules and correct your code tags.

Thanks.

[dcwm]

This is what I have at the moment, and it gives an error

Please Login or Register  to view this content.

Can anyone assist further, please ?

[shg]

There's an example at http://support.microsoft.com/kb/270844.

However, I think the ATP functions are very poorly documented, and would just use the FRQUENCY function. If you want to automate it, record a macro.

[dcwm]

This is the problem, if you record a macro while you are constructing the histogram, then if you subsquently try to run the macro it gives this error.

I've pretty well abandoned this now - it obviously requires more knowledge than I possess.

Thanks for your help.