Reading a VBA Array

[SMS - John Howard]

The worksheet formula:
=Frequency(A2:A10,B2:B5) applied to the below sample worksheet works fine
and produces the correct array 1, 2, 4, 2

A B
1 Scores Bins
2 79 70
3 85 79
4 78 89
5 85
6 50
7 81
8 95
9 88
10 97

Yet this VBA Macro;

Dim Frq() As Variant
Sub Freq()

Frq = Evaluate("=Frequency(A2:A10,B2:B5)")
x = UBound(Frq)
ReDim Frq(x)
For c = LBound(Frq) To UBound(Frq)
Debug.Print Frq(c)
Next c

End Sub

produces a null for each element of the For Next Loop.

Can anyone tell me why?

TIA
John Howard

 

[Doug Glancy]

John,

Before reading your post I did not really understand the Frequency function,
so I just got an education on a couple of fronts. I think your problem will
be solved by changing Redim to Redim Preserve and transposing the array.
I'm not completely sure why on the transpose, just something bumping around
the old brainpan from reading this group. Anyways, I changed a couple of
other things and this works for me:

Option Explicit
Option Base 1
Sub Freq()
Dim Frq() As Variant, c As Integer, x As Integer
With Worksheets("Sheet1")
Frq =
WorksheetFunction.Transpose(WorksheetFunction.Frequency(.Range("A2:A10"),
..Range("B2:B5")))
x = UBound(Frq)
ReDim Preserve Frq(x)
For c = LBound(Frq) To UBound(Frq)
Debug.Print Frq(c)
Next c
End With
End Sub

hth,

Doug Glancy

 

[RB Smissaert]

The array produced by frequency is a two-dimensional one column array.
This will work:

Sub FreqTest()

Dim arr1()
Dim arr2()
Dim Frq()
Dim c As Long
Dim element 'array element

arr1 = Range(Cells(2, 1), Cells(10, 1))
arr2 = Range(Cells(2, 2), Cells(4, 2))

Frq = WorksheetFunction.Frequency(arr1, arr2)

For c = LBound(Frq) To UBound(Frq)
Debug.Print Frq(c, 1)
Next

Debug.Print Chr(13)

For Each element In Frq
Debug.Print element
Next

End Sub



RBS

 

[SMS - John Howard]

Dough and RBS,

Many thanks for the effort you put into your solutions.

Both work admirably.

I have experimented with them both and whilst I now understand How they work
I need to explore more into Why they work.

Once again many thanks
You have enabled solve a long standing work problem

Regards

John