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

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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>>
>

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" <[email protected]> wrote in message
> news:%[email protected]...
>
>> 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
>>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>
>>> 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
>>>
>>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>
>>> 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
>>>
>>

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" <[email protected]> a écrit dans le message de
news:%[email protected]...
> 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
>

Simply because there are often too many rows for the sheet.

RBS


"Michel Pierron" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> a écrit dans le message de
> news:%[email protected]...
>> 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
>>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>
>>> 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
>>>
>>