Array function

  • Thread starter Thread starter Johan de Kok
  • Start date Start date
J

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