using Frequency in Excel 2002 VBA

[Guest]

I have a bonehead question about using the Frequency function: how do I use
it in VBA? I have address strings for both the data and bin ranges and used
the following code:

Cells(2, jDiff + 5) = Application.WorksheetFunction.Frequency(DataStr, BinStr)

with Binstr = $AI$2:$AI$26 and DataStr = $AE$2:$AE$217; jDiff = 31 (AE)

When I run it I get the error: " Unable to get Frequency property of the
WorksheetFunction class".

Could someone please provide me with one or two lines of code (hopefully) to
show how to use it correctly?

Thanks for your help!
BonsaiBill

 

[Mike Middleton]

Bonsai Bill -

FREQUENCY is an array-entered worksheet function.

I use VBA's FormulaArray property to enter it.

You may be able to adapt one of these examples for your purposes:

(a) use the macro recorder to see what's generated when you array-enter the
function, i.e., Control+Shift+Enter instead of Enter

(b) download my Better Histogram add-in from the Histogram page at
www.treeplan.com and examine the code in module Module1Main to see how I
used FormulaArray

- Mike
www.MikeMiddleton.com

 

[Guest]

Thanks Mike! I looked at your macro and see the error of my ways. I am
interested in building a histogram so I may utilize much more of your code.