Histogram Invalid Reference

[John.C.Hildreth]

The following is an excerpt of some code I use to generate
histograms. It is within a loop that generates a number of
histograms. It works, but I get an "Invalid Reference" error at each
execution after the first chart is made. I hit the OK button on the
error message and it creates the chart just fine...but kinda annoying
since there could be 100 or so charts, which means 100 or so error
messages.

I pass 3 ranges to the histogram generation code...1) HistData - range
containing the source data, 2) rOutput - range for the output, and 3)
rBins - range containing the bins. These ranges are defined properly
and like I said after the error it goes ahead and properly creates the
chart. So I'm more than a little confused.

I wondered if anyone had ever ran across this....or can see something
wrong with my ranges.

Thanks,
John



Set HistData = SourceData.Range(Cells(StartRow, 2),
Cells(EndRow - 1, 2))

Set rBins = SourceData.Range(Cells(1, 10), Cells(nBins, 10))

LastHist = SourceData.Range(Cells(65532, 12), Cells(65532,
12)).End(xlUp).Row
Set rOutput = SourceData.Range(Cells(LastHist + 3, 12),
Cells(LastHist + 3, 12))
With rOutput.Offset(-1, 0)
.NumberFormat = "00000"
.Value = FocusBINo
End With

'Input Range
'Output sheet name
'Bin Range
'Sorted, Cumulative Percentage, ChartOutput, Labels

Application.Run "ATPVBAEN.XLA!Histogram", _
HistData, _
rOutput, _
rBins, _
False, True, True, False

 

[Mike Middleton]

John -

Maybe you haven't set a reference to the Analysis ToolPak add-in.

If you want to consider an alternative, look at my VBA code for a "Better
Histogram," available for download at http://www.treeplan.com.

- Mike
www.MikeMiddleton.com

 

[John.C.Hildreth]

Mike,

Thanks. I've got your stuff and it is very nice. I like to really
understand what I'm doing, so I'll likely dissect it and use parts of
it.

About the reference...the toolpak is loaded, but I'm not sure what you
mean by setting a reference. Would you please elaborate?

Thanks,
John

 

[Tom Ogilvy]

in the vbe, Tools=>References, select the analysis toolpak.

That would allow you to call Histogram like it was a built in function, but
since you are using application.Run, you shouldn't need a reference.

this kind of reference:
SourceData.Range(Cells(StartRow, 2),Cells(EndRow - 1, 2))

is inconsistent.

If in a general module:
If SourceData is the activesheet, it works, but then there is no reason to
have the qualifier SourceData. If SourceData isn't the activesheet, then
Range refers to the sourcedata sheet and Cells refers the activesheet and
raises an error.

With SourceData
Set HistData = .Range(.Cells(StartRow, 2),.Cells(EndRow - 1, 2))
End with

Would be consistent and avoid errors. There is a similar type problem if
the code is in a worksheet module in that the unqualified references refer to
the sheet that contain the code.

 

[John.C.Hildreth]

Tom,

Thanks. That reference was already set and there was also a
ATPBVAEN.XLS listed that I referenced. That didn't help...and I
thought it odd to have an XLS extension since the toolpak was an add-
in.

I understand your comment about the range reference. SourceData is
the activesheet since the cells reference works. I'm an engineer and
think better in a numbered gird (R1C1 reference). How can on make
reference using the (r,c) notation on anything but the activesheet?
In other words, how can I use (r,c) without also using cells(r,c)?

Thanks,
John

 

[Tom Ogilvy]

R1C1 isn't supported as an addressing scheme in VBA.

Not sure what is wrong with Cells(row,column)

--
Regards,
Tom Ogilvy

Tom,

Thanks. That reference was already set and there was also a
ATPBVAEN.XLS listed that I referenced. That didn't help...and I
thought it odd to have an XLS extension since the toolpak was an add-
in.

I understand your comment about the range reference. SourceData is
the activesheet since the cells reference works. I'm an engineer and
think better in a numbered gird (R1C1 reference). How can on make
reference using the (r,c) notation on anything but the activesheet?
In other words, how can I use (r,c) without also using cells(r,c)?

Thanks,
John