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

wrote:
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.

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

On Sunday, February 24, 2013 2:47:16 PM UTC+2, wrote:
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