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 said:
Just curious; does the VSORT function retain the type of the array?
Alan Beban
RB said:
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
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
" & UBound(arr))