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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Johan de Kok" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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)}
>
>

[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

--
Dana DeLouis
Win XP & Office 2003


"Johan de Kok" <[email protected]> wrote in message
news:[email protected]...
> 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)}
>
>

[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

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