Array function

[Johan de Kok]

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)}

 

[Leo Heuser]

Johan

In your example use:

Function mySort(a)
Dim i, j
Dim b()
j = a.Rows.Count
ReDim b(1 To j, 1 To 1)
For i = 1 To j
b(i, 1) = a(i)
Next
mySort = b
End Function


"b" must be a 2-dimensional array

 

[Dana DeLouis]

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

 

[Alan Beban]

Even with the suggestions by Leo Heuser and Dana DeLouis you won't end
up with sorted data. The sample code snippet you provided doesn't do any
sorting.

Alan Beban