How can I sort an array?
Article contributed by Jonathan West
Use:
WordBasic.SortArray
At its simplest, you can use it on a one-dimensional array as follows.
Sub SortTest()
Dim ss(2)
As String
Dim i As
Long
ss(0) = "orange"
ss(1) = "apple"
ss(2) = "banana"
WordBasic.SortArray ss()
For i = 0
To
2
Debug.Print
ss(i)
Next i
End Sub
This sorts the array in ascending alphabetical order
However, you can also sort in descending order, and sort either dimension of a two-dimension array. The full list of the SortArray arguments is as follows
SortArray ArrayName[$]() [, Order] [, From] [, To] [, SortType] [, SortKey]
|
ArrayName is the name of the array |
|
Order is 0 for ascending (by default), 1 for descending |
|
From is the first element to sort (0 by default) |
|
To is the last element to sort (by default the last element of the array) |
|
SortType determines whether you are sorting rows or columns. 0 (default) for rows, 1 for columns |
|
SortKey is applicable only to two-dimensional arrays, and indicates the row or column used as the sort key. It is 0 by default |
Note that, unlike most VBA methods, you don't use named arguments with this command; thus you can have
WordBasic.SortArray MailingList$(), 1, 1, 20, 0, 1
but not
WordBasic.SortArray ArrayName:=MailingList$(), Order:=1,
From:=1, To:=20, _
SortType:=0, SortKey:=1
Also, you cannot miss out arguments if you want to use later ones, thus you
can have
WordBasic.SortArray Test(), 0, 0, 2, 0, 1
but not
WordBasic.SortArray Test(), 0, , , , 1
There is one other limitation of the SortArray command. It will sort an array declared as such, but it will not sort an array that is contained in a Variant. If you create an array like this:
Dim vArray as Variant
vArray = Array("orange", "apple", "banana")
SortArray will not sort it.
(Also if you do not declare your array at all, it will be treated as a variant and will not be sorted).