Syntax Laurent Longre's Morefunc VSORT with arrays?
To make matters it a bit clearer I thought it would be better to start a new
thread about this. Previous thread was: Fastest way to sort large 2-D arrays? I am trying to figure out how to use Laurent Longre's VSORT in the .xll add-in MoreFunc. The help file makes it clear for ranges, but not for VBA arrays. Say I have a 10 column array that I want sorted ascending on column 2 and descending on column 5 what would the syntax be for that? The only thing that I have got working sofar is this: Sub Test() Dim arr(1 To 10000, 1 To 5) As Long Dim arr2 Dim i As Long Dim c As Long For i = 1 To 10000 arr(i, 1) = Int((i * Rnd) + 1) For c = 2 To 5 arr(i, c) = i Next Next arr2 = Application.Run([VSORT], arr, arr, 0) 'this works End Sub This will sort descending on column 1. Thanks for any advice. RBS |
Syntax Laurent Longre's Morefunc VSORT with arrays?
OK, I understand now.
The key has to be a range or array, just holding the values to sort on. So if I supply an array to be sorted I have to make another array holding just the values of the column to sort on. RBS "RB Smissaert" wrote in message ... To make matters it a bit clearer I thought it would be better to start a new thread about this. Previous thread was: Fastest way to sort large 2-D arrays? I am trying to figure out how to use Laurent Longre's VSORT in the .xll add-in MoreFunc. The help file makes it clear for ranges, but not for VBA arrays. Say I have a 10 column array that I want sorted ascending on column 2 and descending on column 5 what would the syntax be for that? The only thing that I have got working sofar is this: Sub Test() Dim arr(1 To 10000, 1 To 5) As Long Dim arr2 Dim i As Long Dim c As Long For i = 1 To 10000 arr(i, 1) = Int((i * Rnd) + 1) For c = 2 To 5 arr(i, c) = i Next Next arr2 = Application.Run([VSORT], arr, arr, 0) 'this works End Sub This will sort descending on column 1. Thanks for any advice. RBS |
Syntax Laurent Longre's Morefunc VSORT with arrays?
I think I worked this all out now.
Although it will need making an extra (the array holding the values to sort on) it is still 4 to 5 times faster than a QuickSort. I have made a simple wrapper function that makes sorting arrays with this a bit easier. I only needed it to sort up to 3 fields, but you could alter it to go up to 14 fields. Function VSORTArray(ByRef arr As Variant, _ ByVal btCol1 As Byte, _ ByVal strSortType1 As String, _ Optional ByVal btCol2 As Byte = 0, _ Optional ByVal strSortType2 As String = "", _ Optional ByVal btCol3 As Byte = 0, _ Optional ByVal strSortType3 As String = "") As Variant '------------------------------------------------------------------ 'http://longre.free.fr/english/ 'Uses Laurent Longre's VSort function in the .xll add-in MoreFunc 'Will be about 4 to 5 times faster than a quicksort and can sort 'on multiple columns. 'Done up to 3 columns here, but can be done up to 14 columns '------------------------------------------------------------------ 'will sort an 0-based or 1-based 2-D array with up to 3 sort keys 'the field key has to be supplied as a byte, where the first column 'of the array is 1, even if it is an 0-based array 'the sort type has to be given as "a", "A" , "b" or "B" 'examples: 'sorting on 1 field: arr2 = VSORTArray(arr, 1, "A") 'sorting on 2 fields: arr2 = VSORTArray(arr, 2, "D", 5, "A") '------------------------------------------------------------------ Dim i As Long Dim LB1 As Long Dim UB1 As Long Dim arrKey1 Dim arrKey2 Dim arrKey3 Dim btSortType1 As Byte Dim btSortType2 As Byte Dim btSortType3 As Byte Dim arrFinal LB1 = LBound(arr) UB1 = UBound(arr) 'make the array for key 1 '------------------------ ReDim arrKey1(LB1 To UB1, LB1 To LB1) For i = LB1 To UB1 arrKey1(i, LB1) = arr(i, btCol1 - (1 - LB1)) Next 'set the sort type for key 1 '--------------------------- If UCase(strSortType1) = "A" Then btSortType1 = 1 Else btSortType1 = 0 End If If Not btCol2 = 0 Then 'make the array for key 2 '------------------------ ReDim arrKey2(LB1 To UB1, LB1 To LB1) For i = LB1 To UB1 arrKey2(i, LB1) = arr(i, btCol2 - (1 - LB1)) Next 'set the sort type for key 2 '--------------------------- If UCase(strSortType2) = "A" Then btSortType2 = 1 Else btSortType2 = 0 End If End If If Not btCol3 = 0 Then 'make the array for key 3 '------------------------ ReDim arrKey3(LB1 To UB1, LB1 To LB1) For i = LB1 To UB1 arrKey3(i, LB1) = arr(i, btCol3 - (1 - LB1)) Next 'set the sort type for key 3 '--------------------------- If UCase(strSortType3) = "A" Then btSortType3 = 1 Else btSortType3 = 0 End If End If If Not strSortType3 = "" Then '3 fields to sort on '------------------- arrFinal = Application.Run([VSORT], arr, _ arrKey1, btSortType1, _ arrKey2, btSortType2, _ arrKey3, btSortType3) Else '2 fields to sort on '------------------- If Not strSortType2 = "" Then arrFinal = Application.Run([VSORT], arr, _ arrKey1, btSortType1, _ arrKey2, btSortType2) Else '1 field to sort on '------------------ arrFinal = Application.Run([VSORT], _ arr, arrKey1, btSortType1) End If End If VSORTArray = arrFinal End Function RBS "RB Smissaert" wrote in message ... To make matters it a bit clearer I thought it would be better to start a new thread about this. Previous thread was: Fastest way to sort large 2-D arrays? I am trying to figure out how to use Laurent Longre's VSORT in the .xll add-in MoreFunc. The help file makes it clear for ranges, but not for VBA arrays. Say I have a 10 column array that I want sorted ascending on column 2 and descending on column 5 what would the syntax be for that? The only thing that I have got working sofar is this: Sub Test() Dim arr(1 To 10000, 1 To 5) As Long Dim arr2 Dim i As Long Dim c As Long For i = 1 To 10000 arr(i, 1) = Int((i * Rnd) + 1) For c = 2 To 5 arr(i, c) = i Next Next arr2 = Application.Run([VSORT], arr, arr, 0) 'this works End Sub This will sort descending on column 1. Thanks for any advice. RBS |
Syntax Laurent Longre's Morefunc VSORT with arrays?
Come across some problems with this.
Firstly, it doesn't seem to like columns with non-integer numeric data, causing a type mismatch error. Secondly, it seems to change the base of the array from 0-bound to 1-bound I could convert the base back with one of Alan Beban's array functions, but not sure if there is then much speed gain left. This is a pity as it is otherwise nice and fast and simple. Any advice about this? RBS "RB Smissaert" wrote in message ... To make matters it a bit clearer I thought it would be better to start a new thread about this. Previous thread was: Fastest way to sort large 2-D arrays? I am trying to figure out how to use Laurent Longre's VSORT in the .xll add-in MoreFunc. The help file makes it clear for ranges, but not for VBA arrays. Say I have a 10 column array that I want sorted ascending on column 2 and descending on column 5 what would the syntax be for that? The only thing that I have got working sofar is this: Sub Test() Dim arr(1 To 10000, 1 To 5) As Long Dim arr2 Dim i As Long Dim c As Long For i = 1 To 10000 arr(i, 1) = Int((i * Rnd) + 1) For c = 2 To 5 arr(i, c) = i Next Next arr2 = Application.Run([VSORT], arr, arr, 0) 'this works End Sub This will sort descending on column 1. Thanks for any advice. RBS |
Syntax Laurent Longre's Morefunc VSORT with arrays?
It is not non-integer numerical data, but empty array elements that are the
problem. It also appears very difficult to pick up these elements and change them to 0 or "" It all seemed a bit too good to be true now. RBS "RB Smissaert" wrote in message ... Come across some problems with this. Firstly, it doesn't seem to like columns with non-integer numeric data, causing a type mismatch error. Secondly, it seems to change the base of the array from 0-bound to 1-bound I could convert the base back with one of Alan Beban's array functions, but not sure if there is then much speed gain left. This is a pity as it is otherwise nice and fast and simple. Any advice about this? RBS "RB Smissaert" wrote in message ... To make matters it a bit clearer I thought it would be better to start a new thread about this. Previous thread was: Fastest way to sort large 2-D arrays? I am trying to figure out how to use Laurent Longre's VSORT in the .xll add-in MoreFunc. The help file makes it clear for ranges, but not for VBA arrays. Say I have a 10 column array that I want sorted ascending on column 2 and descending on column 5 what would the syntax be for that? The only thing that I have got working sofar is this: Sub Test() Dim arr(1 To 10000, 1 To 5) As Long Dim arr2 Dim i As Long Dim c As Long For i = 1 To 10000 arr(i, 1) = Int((i * Rnd) + 1) For c = 2 To 5 arr(i, c) = i Next Next arr2 = Application.Run([VSORT], arr, arr, 0) 'this works End Sub This will sort descending on column 1. Thanks for any advice. RBS |
Syntax Laurent Longre's Morefunc VSORT with arrays?
Just curious; does the VSORT function retain the type of the array?
Alan Beban RB Smissaert wrote: Come across some problems with this. Firstly, it doesn't seem to like columns with non-integer numeric data, causing a type mismatch error. Secondly, it seems to change the base of the array from 0-bound to 1-bound I could convert the base back with one of Alan Beban's array functions, but not sure if there is then much speed gain left. This is a pity as it is otherwise nice and fast and simple. Any advice about this? RBS "RB Smissaert" wrote in message ... To make matters it a bit clearer I thought it would be better to start a new thread about this. Previous thread was: Fastest way to sort large 2-D arrays? I am trying to figure out how to use Laurent Longre's VSORT in the .xll add-in MoreFunc. The help file makes it clear for ranges, but not for VBA arrays. Say I have a 10 column array that I want sorted ascending on column 2 and descending on column 5 what would the syntax be for that? The only thing that I have got working sofar is this: Sub Test() Dim arr(1 To 10000, 1 To 5) As Long Dim arr2 Dim i As Long Dim c As Long For i = 1 To 10000 arr(i, 1) = Int((i * Rnd) + 1) For c = 2 To 5 arr(i, c) = i Next Next arr2 = Application.Run([VSORT], arr, arr, 0) 'this works End Sub This will sort descending on column 1. Thanks for any advice. RBS |
Syntax Laurent Longre's Morefunc VSORT with arrays?
It doesn't with me.
When the array was 0-based before the sort it becomes 1-based after. 1-based arrays stay the same. I hope I can fix this as it is a very nice function, fast and with the ability to sort on multiple fields. RBS "Alan Beban" wrote in message ... Just curious; does the VSORT function retain the type of the array? Alan Beban RB Smissaert wrote: Come across some problems with this. Firstly, it doesn't seem to like columns with non-integer numeric data, causing a type mismatch error. Secondly, it seems to change the base of the array from 0-bound to 1-bound I could convert the base back with one of Alan Beban's array functions, but not sure if there is then much speed gain left. This is a pity as it is otherwise nice and fast and simple. Any advice about this? RBS "RB Smissaert" wrote in message ... To make matters it a bit clearer I thought it would be better to start a new thread about this. Previous thread was: Fastest way to sort large 2-D arrays? I am trying to figure out how to use Laurent Longre's VSORT in the .xll add-in MoreFunc. The help file makes it clear for ranges, but not for VBA arrays. Say I have a 10 column array that I want sorted ascending on column 2 and descending on column 5 what would the syntax be for that? The only thing that I have got working sofar is this: Sub Test() Dim arr(1 To 10000, 1 To 5) As Long Dim arr2 Dim i As Long Dim c As Long For i = 1 To 10000 arr(i, 1) = Int((i * Rnd) + 1) For c = 2 To 5 arr(i, c) = i Next Next arr2 = Application.Run([VSORT], arr, arr, 0) 'this works End Sub This will sort descending on column 1. Thanks for any advice. RBS |
Syntax Laurent Longre's Morefunc VSORT with arrays?
The following will show the problem:
Sub test2() Dim arr(0 To 10000, 0 To 4) Dim arr2 Dim i As Long Dim c As Long ReDim arr2(0) Randomize For i = 0 To 10000 arr(i, 0) = Int((i * Rnd) + 1) Next 'start at 1000 to get some empty array elements For i = 1000 To 10000 For c = 1 To 4 arr(i, c) = Int((i * Rnd) + 1) Next Next 'will give 0 and 0 MsgBox LBound(arr2), , UBound(arr2) arr2 = VSORTArray(arr, 1, "A") 'will give 1 and 10001 MsgBox LBound(arr2), , UBound(arr2) End Sub Function VSORTArray(ByRef arr As Variant, _ ByVal btCol1 As Byte, _ ByVal strSortType1 As String, _ Optional ByVal btCol2 As Byte = 0, _ Optional ByVal strSortType2 As String = "", _ Optional ByVal btCol3 As Byte = 0, _ Optional ByVal strSortType3 As String = "") As Variant '------------------------------------------------------------------ 'http://longre.free.fr/english/ 'Uses Laurent Longre's VSort function in the .xll add-in MoreFunc 'Will be about 4 to 5 times faster than a quicksort and can sort 'on multiple columns. 'Done up to 3 columns here, but can be done up to 14 columns '------------------------------------------------------------------ 'will sort an 0-based or 1-based 2-D array with up to 3 sort keys 'the field key has to be supplied as a byte, where the first column 'of the array is 1, even if it is an 0-based array 'the sort type has to be given as "a", "A" , "b" or "B" 'examples: 'sorting on 1 field: arr2 = VSORTArray(arr, 1, "A") 'sorting on 2 fields: arr2 = VSORTArray(arr, 2, "D", 5, "A") '------------------------------------------------------------------ 'One major problem is that it will always produce a 1-based array 'even when the provided array is 0-based. Maybe this was done to 'work with sheet ranges, but it causes problems with VBA arrays '------------------------------------------------------------------ Dim i As Long Dim LB1 As Long Dim UB1 As Long Dim arrKey1 Dim arrKey2 Dim arrKey3 Dim btSortType1 As Byte Dim btSortType2 As Byte Dim btSortType3 As Byte Dim arrFinal LB1 = LBound(arr) UB1 = UBound(arr) 'make the array for key 1 '------------------------ ReDim arrKey1(LB1 To UB1, LB1 To LB1) For i = LB1 To UB1 arrKey1(i, LB1) = arr(i, btCol1 - (1 - LB1)) Next 'set the sort type for key 1 '--------------------------- If UCase(strSortType1) = "A" Then btSortType1 = 1 Else btSortType1 = 0 End If If Not btCol2 = 0 Then 'make the array for key 2 '------------------------ ReDim arrKey2(LB1 To UB1, LB1 To LB1) For i = LB1 To UB1 arrKey2(i, LB1) = arr(i, btCol2 - (1 - LB1)) Next 'set the sort type for key 2 '--------------------------- If UCase(strSortType2) = "A" Then btSortType2 = 1 Else btSortType2 = 0 End If End If If Not btCol3 = 0 Then 'make the array for key 3 '------------------------ ReDim arrKey3(LB1 To UB1, LB1 To LB1) For i = LB1 To UB1 arrKey3(i, LB1) = arr(i, btCol3 - (1 - LB1)) Next 'set the sort type for key 3 '--------------------------- If UCase(strSortType3) = "A" Then btSortType3 = 1 Else btSortType3 = 0 End If End If If Not btCol3 = 0 Then '3 fields to sort on '------------------- arrFinal = Application.Run([VSORT], arr, _ arrKey1, btSortType1, _ arrKey2, btSortType2, _ arrKey3, btSortType3) Else '2 fields to sort on '------------------- If Not btCol2 = 0 Then arrFinal = Application.Run([VSORT], arr, _ arrKey1, btSortType1, _ arrKey2, btSortType2) Else '1 field to sort on '------------------ arrFinal = Application.Run([VSORT], arr, _ arrKey1, btSortType1) End If End If VSORTArray = arrFinal End Function RBS "Alan Beban" wrote in message ... Just curious; does the VSORT function retain the type of the array? Alan Beban RB Smissaert wrote: Come across some problems with this. Firstly, it doesn't seem to like columns with non-integer numeric data, causing a type mismatch error. Secondly, it seems to change the base of the array from 0-bound to 1-bound I could convert the base back with one of Alan Beban's array functions, but not sure if there is then much speed gain left. This is a pity as it is otherwise nice and fast and simple. Any advice about this? RBS "RB Smissaert" wrote in message ... To make matters it a bit clearer I thought it would be better to start a new thread about this. Previous thread was: Fastest way to sort large 2-D arrays? I am trying to figure out how to use Laurent Longre's VSORT in the .xll add-in MoreFunc. The help file makes it clear for ranges, but not for VBA arrays. Say I have a 10 column array that I want sorted ascending on column 2 and descending on column 5 what would the syntax be for that? The only thing that I have got working sofar is this: Sub Test() Dim arr(1 To 10000, 1 To 5) As Long Dim arr2 Dim i As Long Dim c As Long For i = 1 To 10000 arr(i, 1) = Int((i * Rnd) + 1) For c = 2 To 5 arr(i, c) = i Next Next arr2 = Application.Run([VSORT], arr, arr, 0) 'this works End Sub This will sort descending on column 1. Thanks for any advice. RBS |
Syntax Laurent Longre's Morefunc VSORT with arrays?
Hi RB,
Why not use the sorting properties of Excel ? Sub Tri2DArray() Application.ScreenUpdating = False Dim arr As Variant, Tps As Single Dim i As Long, c As Long Randomize ReDim arr(1 To 10000, 1 To 4) For i = 1 To 10000 arr(i, 1) = Int((i * Rnd) + 1) 'Cells(i, 1) = arr(i, 1) If i 1000 Then For c = 2 To 4 arr(i, c) = Int((i * Rnd) + 1) 'Cells(i, c) = arr(i, c) Next c End If Next Tps = Timer Worksheets.Add Set Rng = Range("A1:D" & UBound(arr)) Rng.value = arr Rng.Sort Key1:=Range("A1"), Order1:=1, Key2:=Range("B1") _ , Order2:=1, Key3:=Range("C1"), Order3:=1, Header:=0 _ , OrderCustom:=1, MatchCase:=False, Orientation:=1 arr = Rng.value Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True MsgBox Timer - Tps Set Rng = Nothing 'For i = 1 To UBound(arr) 'Cells(i, 6) = arr(i, 1) 'Cells(i, 7) = arr(i, 2) 'Cells(i, 8) = arr(i, 3) 'Cells(i, 9) = arr(i, 4) 'Next i End Sub MP "RB Smissaert" a écrit dans le message de ... To make matters it a bit clearer I thought it would be better to start a new thread about this. Previous thread was: Fastest way to sort large 2-D arrays? I am trying to figure out how to use Laurent Longre's VSORT in the .xll add-in MoreFunc. The help file makes it clear for ranges, but not for VBA arrays. Say I have a 10 column array that I want sorted ascending on column 2 and descending on column 5 what would the syntax be for that? The only thing that I have got working sofar is this: Sub Test() Dim arr(1 To 10000, 1 To 5) As Long Dim arr2 Dim i As Long Dim c As Long For i = 1 To 10000 arr(i, 1) = Int((i * Rnd) + 1) For c = 2 To 5 arr(i, c) = i Next Next arr2 = Application.Run([VSORT], arr, arr, 0) 'this works End Sub This will sort descending on column 1. Thanks for any advice. RBS |
Syntax Laurent Longre's Morefunc VSORT with arrays?
Simply because there are often too many rows for the sheet.
RBS "Michel Pierron" wrote in message ... Hi RB, Why not use the sorting properties of Excel ? Sub Tri2DArray() Application.ScreenUpdating = False Dim arr As Variant, Tps As Single Dim i As Long, c As Long Randomize ReDim arr(1 To 10000, 1 To 4) For i = 1 To 10000 arr(i, 1) = Int((i * Rnd) + 1) 'Cells(i, 1) = arr(i, 1) If i 1000 Then For c = 2 To 4 arr(i, c) = Int((i * Rnd) + 1) 'Cells(i, c) = arr(i, c) Next c End If Next Tps = Timer Worksheets.Add Set Rng = Range("A1:D" & UBound(arr)) Rng.value = arr Rng.Sort Key1:=Range("A1"), Order1:=1, Key2:=Range("B1") _ , Order2:=1, Key3:=Range("C1"), Order3:=1, Header:=0 _ , OrderCustom:=1, MatchCase:=False, Orientation:=1 arr = Rng.value Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True MsgBox Timer - Tps Set Rng = Nothing 'For i = 1 To UBound(arr) 'Cells(i, 6) = arr(i, 1) 'Cells(i, 7) = arr(i, 2) 'Cells(i, 8) = arr(i, 3) 'Cells(i, 9) = arr(i, 4) 'Next i End Sub MP "RB Smissaert" a écrit dans le message de ... To make matters it a bit clearer I thought it would be better to start a new thread about this. Previous thread was: Fastest way to sort large 2-D arrays? I am trying to figure out how to use Laurent Longre's VSORT in the .xll add-in MoreFunc. The help file makes it clear for ranges, but not for VBA arrays. Say I have a 10 column array that I want sorted ascending on column 2 and descending on column 5 what would the syntax be for that? The only thing that I have got working sofar is this: Sub Test() Dim arr(1 To 10000, 1 To 5) As Long Dim arr2 Dim i As Long Dim c As Long For i = 1 To 10000 arr(i, 1) = Int((i * Rnd) + 1) For c = 2 To 5 arr(i, c) = i Next Next arr2 = Application.Run([VSORT], arr, arr, 0) 'this works End Sub This will sort descending on column 1. Thanks for any advice. RBS |
Syntax Laurent Longre's Morefunc VSORT with arrays?
This will be faster if it is a 1-based array:
Function VSORTArray(ByRef arr As Variant, _ ByVal btCol1 As Byte, _ ByVal strSortType1 As String, _ Optional ByVal btCol2 As Byte = 0, _ Optional ByVal strSortType2 As String = "", _ Optional ByVal btCol3 As Byte = 0, _ Optional ByVal strSortType3 As String = "") As Variant '------------------------------------------------------------------ 'http://longre.free.fr/english/ 'Uses Laurent Longre's VSort function in the .xll add-in MoreFunc 'Will be about 2 times faster than a quicksort and can sort 'on multiple columns. 'Done up to 3 columns here, but can be done up to 14 columns '------------------------------------------------------------------ 'will sort an 0-based or 1-based 2-D array with up to 3 sort keys 'the field key has to be supplied as a byte, where the first column 'of the array is 1, even if it is an 0-based array 'the sort type has to be given as "a", "A" , "b" or "B" 'examples: 'sorting on 1 field: arr2 = VSORTArray(arr, 1, "A") 'sorting on 2 fields: arr2 = VSORTArray(arr, 2, "D", 5, "A") '------------------------------------------------------------------ Dim i As Long Dim c As Long Dim LB1 As Long Dim UB1 As Long Dim LB2 As Long Dim UB2 As Long Dim arrKey1 Dim arrKey2 Dim arrKey3 Dim btSortType1 As Byte Dim btSortType2 As Byte Dim btSortType3 As Byte Dim arrFinal Dim arrFinal2 LB1 = LBound(arr) UB1 = UBound(arr) LB2 = LBound(arr, 2) UB2 = UBound(arr, 2) 'make the array for key 1 '------------------------ ReDim arrKey1(LB1 To UB1, LB1 To LB1) For i = LB1 To UB1 arrKey1(i, LB1) = arr(i, btCol1 - (1 - LB1)) Next 'set the sort type for key 1 '--------------------------- If UCase(strSortType1) = "A" Then btSortType1 = 1 Else btSortType1 = 0 End If If Not btCol2 = 0 Then 'make the array for key 2 '------------------------ ReDim arrKey2(LB1 To UB1, LB1 To LB1) For i = LB1 To UB1 arrKey2(i, LB1) = arr(i, btCol2 - (1 - LB1)) Next 'set the sort type for key 2 '--------------------------- If UCase(strSortType2) = "A" Then btSortType2 = 1 Else btSortType2 = 0 End If End If If Not btCol3 = 0 Then 'make the array for key 3 '------------------------ ReDim arrKey3(LB1 To UB1, LB1 To LB1) For i = LB1 To UB1 arrKey3(i, LB1) = arr(i, btCol3 - (1 - LB1)) Next 'set the sort type for key 3 '--------------------------- If UCase(strSortType3) = "A" Then btSortType3 = 1 Else btSortType3 = 0 End If End If If Not strSortType3 = "" Then '3 fields to sort on '------------------- arrFinal = Application.Run([VSORT], arr, _ arrKey1, btSortType1, _ arrKey2, btSortType2, _ arrKey3, btSortType3) Else '2 fields to sort on '------------------- If Not strSortType2 = "" Then arrFinal = Application.Run([VSORT], arr, _ arrKey1, btSortType1, _ arrKey2, btSortType2) Else '1 field to sort on '------------------ arrFinal = Application.Run([VSORT], _ arr, arrKey1, btSortType1) End If End If If LB1 = 0 Then 'to revert back to an 0-based array '---------------------------------- ReDim arrFinal2(LB1 To UB1, LB2 To UB2) For i = LBound(arrFinal) To UBound(arrFinal) For c = LBound(arrFinal, 2) To UBound(arrFinal, 2) arrFinal2(i - (1 - LB1), c - (1 - LB2)) = arrFinal(i, c) Next Next VSORTArray = arrFinal2 Else VSORTArray = arrFinal End If End Function One thing I noticed that the number of rows in the array to sort can't go above 65536, the number of rows in the sheet. It seems that these functions are geared towards sheet ranges (always produce 1-based arrays, limit of 65536 rows) even though they can be used for arrays. The benefit speedwise would come into play with arrays larger than this, unless maybe you have slow hardware. The main benefit though is that you can sort on multiple fields. RBS "Alan Beban" wrote in message ... Just curious; does the VSORT function retain the type of the array? Alan Beban RB Smissaert wrote: Come across some problems with this. Firstly, it doesn't seem to like columns with non-integer numeric data, causing a type mismatch error. Secondly, it seems to change the base of the array from 0-bound to 1-bound I could convert the base back with one of Alan Beban's array functions, but not sure if there is then much speed gain left. This is a pity as it is otherwise nice and fast and simple. Any advice about this? RBS "RB Smissaert" wrote in message ... To make matters it a bit clearer I thought it would be better to start a new thread about this. Previous thread was: Fastest way to sort large 2-D arrays? I am trying to figure out how to use Laurent Longre's VSORT in the .xll add-in MoreFunc. The help file makes it clear for ranges, but not for VBA arrays. Say I have a 10 column array that I want sorted ascending on column 2 and descending on column 5 what would the syntax be for that? The only thing that I have got working sofar is this: Sub Test() Dim arr(1 To 10000, 1 To 5) As Long Dim arr2 Dim i As Long Dim c As Long For i = 1 To 10000 arr(i, 1) = Int((i * Rnd) + 1) For c = 2 To 5 arr(i, c) = i Next Next arr2 = Application.Run([VSORT], arr, arr, 0) 'this works End Sub This will sort descending on column 1. Thanks for any advice. RBS |
All times are GMT +1. The time now is 03:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com