CSE with function returning an array?

[vezerid]

Hi all,

I am trying to build a UDF that is the textual equivalent of FREQUENCY. The function would accept the same arguments as FREQUENCY and be array-entered over a range of cells of length one more than the length of the bins array (for 'other').

My function goes as follows:

Function c_frequency(data As range, bins As range) As Single()
nb = bins.Cells.Count
Dim results() As Single
ReDim results(1 To nb + 1)
Set entcol = data.EntireColumn
tot_rows = entcol.Rows.Count
' necessary code to adapt to my needs
Set newdata = range(entcol.Cells(range("collect").Row, 1), entcol.Cells(tot_rows, 1))
tot_found = 0
For i = 1 To nb
results(i) = WorksheetFunction.CountIf(newdata, bins(i))
tot_found = tot_found + results(i)
Next i
' count anything else
results(i) = WorksheetFunction.CountA(newdata) - tot_found
c_frequency = results
End Function

My problem: when I array-enter it, all cells have the value of result(1).

To test it, I define a name "collect" in, say, C4, and then below it enter random values with the formula
=IF(RAND()<0.5,"Heads","Tails")

Then I enter the values "Heads" and "Tails" in L8:L9 and next to them I am attempting on three cells:
=C_FREQUENCY(C:C,L8:L9)

But the returned results only reproduce the value of Heads in all three result cells.

What am I doing wrong?

TIA

 

[joeu2004]

My function goes as follows:
Function c_frequency(data As range, bins As range) As Single() [....]
Dim results() As Single
ReDim results(1 To nb + 1) [....]
c_frequency = results [....]
My problem: when I array-enter it, all cells have the value
of result(1). [....] > What am I doing wrong?

The primary problem is that you declared "results" as 1-dimensional in VBA,
and Excel interprets that as a single row.

You probably selected a column of cells in Excel for the array-entered
result.

You could select a row of cells in Excel for the array-entered result.

Or your array-entered formula could be =TRANSPOSE(C_FREQUENCY(C:C,L8:L9)).

Alternatively, you could declare "results" as 2-dimensional in VBA. But
that requires some additional changes, to wit:

Function c_frequency(data As range, bins As range) ' As Variant
[....]
ReDim results(1 To nb + 1, 1 to 1)
[....]
results(i,1) = WorksheetFunction.CountIf(newdata, bins(i))

Then you would select a column of cells in Excel for the array-entered
formula.

(Use TRANSPOSE if you decide to select a row of cells in Excel.)

If you want to get fancy, you could select the orientation of "results"
based on the orientation of the "bins" parameter.

PS: There are lot of other unrelated improvements that you could -- and
should -- make. I will post them later, unless someone else points them
out.

 

[vezerid]

Thank you Joeu, it works as you suggested.

This 2D thing when you play with ranges and arrays always gets me. I do have in mind to improve the function, so that it works with either orientation and also with array constants as function arguments - and I will, although not immediately.

In the meanwhile I am really interested in whatever suggestions for improvement you might have and I will be monitoring the board for the moment that I see you have found the time to post them.

Best regards,
Kostis