Array function
Just to mention...if you Transpose your horizontal array, your function
should work. For example...
Function mySort(a)
Dim i, j
Dim b()
j = a.Rows.Count
ReDim b(1 To j)
For i = 1 To j
b(i) = a(i)
Next
mySort = WorksheetFunction.Transpose(Array(b(1), b(2), b(3), b(4),
b(5)))
End Function
Just some other ideas:
Function mySort(a)
Dim b
With WorksheetFunction
b = .Transpose(a.Value)
'..your code
mySort = .Transpose(Array(b(1), b(2), b(3), b(4), b(5)))
End With
End Function
--
Dana DeLouis
Win XP & Office 2003
"Johan de Kok" wrote in message
...
Why does the following array function returns the first value of the input
array to all cells of the output array?
The function is entered with ctrl - shift - enter
How can I return a array with a variable length?
Function mySort(a)
Dim i, j
Dim b()
j = a.Rows.Count
ReDim b(1 To j)
For i = 1 To j
b(i) = a(i)
Next
mySort = Array(b(1), b(2), b(3), b(4), b(5))
End Function
Excel worksheet
6 6
20 6
10 6
21 6
5 6
With formules visible
6 {=mySort(A1:A5)}
20 {=mySort(A1:A5)}
10 {=mySort(A1:A5)}
21 {=mySort(A1:A5)}
5 {=mySort(A1:A5)}
|