CSE with function returning an array?
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.
|