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

Johan de Kok wrote:
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)}