Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)} |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)} |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)} |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)} |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array function - I think! | Excel Worksheet Functions | |||
OR function in array-entered IF function | Excel Worksheet Functions | |||
array function | Excel Worksheet Functions | |||
AND in an array function? | Excel Worksheet Functions | |||
Array Function | Excel Worksheet Functions |