Array function
 

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

Array function
 

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Johan de Kok" skrev i en meddelelse
...
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)}

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

Array 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

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