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