Sorting text is different from sorting numbers because in a text sort 10 will precede 2 on account of the character 1 having a lower ASCII ranking than the character 2, just like “Ab” would precede “B” in the dictionary. The following procedure sorts text strings.
- Code: Select all
Option Base 0
Private Sub SortTextArray(ByRef A As Variant)
Dim Tmp As String
Dim Done As Boolean
Dim i As Long
Do
Done = True
For i = 1 To UBound(A)
If StrComp(CStr(A(i)), CStr(A(i - 1)), _
vbTextCompare) < 0 Then
Tmp = CStr(A(i))
A(i) = A(i - 1)
A(i - 1) = Tmp
Done = False
End If
Next i
Loop Until Done
End Sub
The sort order is ascending. However, if you wish to sort descending just change the
< sign in this line of code to a
< sign. That’s all.
- Code: Select all
If StrComp(CStr(A(i)), CStr(A(i - 1)), _
vbTextCompare) < 0 Then
To call the procedure you need to provide a variant of array type such as you could generate with this code:
- Code: Select all
Dim MyArray As Variant
MyArray = Split("one,two,three,four", ",")
If you need to read your strings from a worksheet code like this will do the job:
- Code: Select all
Dim MyArray() As Variant
Dim R As Long
Dim i As Long
For R = FirstRow To LastRow
ReDim MyArray(i)
MyArray(i) = Cells(R, “A”).Value
i = i + 1
Next R
It is important that your array starts with a lower bound of 0. Therefore you should not omit the
Option Base 0 statement at the top of your module.
Use this kind of code to call the procedure:
- Code: Select all
SortTextArray MyArray
The array is passed to the sub ByRef, meaning that only the address is provided, rather than the array itself. That saves time by not transferring potentially large amount of data. It also means that the original sequence of the MyArray elements is irretrievably lost during the sort. The only way to get it back would be to read it again from the source. If, for some reason, you need the original, unsorted version, send a copy thereof for sorting. In the above procedure call MyArray is itself different before and after.
The procedure here offered was designed for sorting validation lists. It will do lists from a few dozen to a few hundred strings efficiently. Yet, it can’t match Excel’s own sorting capabilities. If you have thousands of items to sort you would probably find it a bit slow.
Have a great day!
Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)