This is an implementation of the 'merge sort' algorithm - one of the most efficient sorting algorithms. It uses what's known as a 'divide and conquer' approach by dividing up a list (an array of Strings in this case) into multiple smaller lists, sorting those lists and then eventually merging all of the small lists back into one. You can read much more about how the merge sort works on various sites on the internet (e.g. Wikipedia) so I'm not going to go into detail here.
How quick is a merge sort? Very quick. On my test device (a not especially powerful laptop, 2 ish years old with 8Gb of RAM and an Intel i7 processor), it sorted 1,000 Strings in an average time of 0.004 seconds, 10,000 Strings in an average time of around 0.05 seconds and 100,000 Strings in an average time of around 0.8 seconds.
Here's the code ... when I'm using this I add the code to a standard Module named SortStrings.
This will sort both static and dynamic arrays of Strings, and works whether the lower bound is 0 or 1 (or anything else for that matter). It will perform either an 'A to Z' or a 'Z to A' sort and will perform either a case-sensitive or a case-insensitive sort.
And here's an example to use it:
As you can see, I have put the code into a (standard) Module named SortStrings, it is sorting an array of Strings called asStrings, the sort is 'A to Z' and it is a case-insensitive sort. Nice and simple, one line of code, re-usable throughout your code base if you need to sort different arrays or sort in different orders. Also, if in Excel and your thinking about using a Worksheet to sort Strings, this is so much simpler ... no adding a Worksheet, adding the values, remembering how the Sort function works and then deleting everything afterwards.
A final point is that if your array is actually a Variant (i.e. containing an array of Strings) then you can adjust the first parameter of the Sort() method to accept a Variant (instead of the array of Strings as it does above), like this:
... while this provides more flexibility (as you can pass in either a Variant containing an array of Strings OR an actual array of Strings) it also provides less compile-time type safety ... the choice is yours!
Case-sensitive sorting is an interesting subject. I tried your method in the following test function for comparison with TestSort1 and TestSort2 presented in my earlier comment:
Function TestSort3() As String() 'returns String array
Dim A() As String
A = Split("Dolor amet sit Amet dolor Sit Dulor sat abet aMet sIt dOlor")
SortStrings.Sort A, True, vbBinaryCompare
TestSort3 = A
End Function
Here are the results for case-sensitive sorting of TestArray.
TestArray: Dolor, amet, sit, Amet, dolor, Sit, Dulor, sat, abet, aMet, sIt, dOlor
TestSort3: Amet, Dolor, Dulor, Sit, aMet, abet, amet, dOlor, dolor, sIt, sat, sit
TestSort2: abet, amet, aMet, Amet, dolor, dOlor, Dolor, Dulor, sat, sit, sIt, Sit
TestSort1: abet, amet, aMet, Amet, dolor, dOlor, Dolor, Dulor, sat, sit, sIt,…
WordBasic.SortArray and ArrayList.Sort are two more methods for comparison. Both are case sensitive (vbBinaryCompare).
Function TestSort1() As String() 'returns String array
'required: Tools > References > Microsoft Word 16.0 Object Library
'max 255 char strings
Dim A() As String 'do not use WordBasic.SortArray with Variant
A = Split("Dolor amet sit Amet dolor Sit Dulor sat abet aMet sIt dOlor")
WordBasic.SortArray A '2nd arg: 0 (default) ascending, 1 descending
TestSort1 = A
End Function
Function TestSort2() As Variant() 'cannot return String array
'required: Tools > References > mscorlib.dll
Dim A() As String, n As Integer
A = Split("Dolor amet sit Amet dolor Sit Dulor sat abet aMet sIt dOlor")
With New ArrayList
For n = LBound(A) To UBound(A)
.Add A(n)
…
How does this compare to WorksheetFunction.Sort(array)?
Function TestSort()
Dim A() As String
A = Split("Dolor amet sit Amet dolor Sit Dolor sit amet")
With Application.WorksheetFunction
TestSort = .Sort(.Transpose(A))
End With
End Function
In this case, only vbTextCompare is possible.