View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
joeu2004[_2_] joeu2004[_2_] is offline
external usenet poster
 
Posts: 829
Default 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.