top of page
  • John

Sorting Strings in VBA

Updated: Sep 12, 2023

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!

10 comments

Recent Posts

See All

10 Comments


J. Woolley
J. Woolley
Jun 21, 2023

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,…


Like
John
John
Jun 26, 2023
Replying to

And with SortStrings.Sort you can have either (in your words) the 'academic' or the 'practical'. Not sure how that can be something to be unhappy about!


I'm not sure where in the Microsoft documentation you find something that says Word and Excel perform case-sensitive sorting. I'm assuming that, in saying this, you're still referring to WordBasic.SortArray and ArrayList.Sort (note that neither of these are 'in Excel')?


With these example inputs: xyz, XYZ, JKL, jkl, abc, ABC …


* A case-sensitive sort results in: ABC, JKL, XYZ, abc, jkl, xyz

* A case-insensitive sort results in: abc, ABC, jkl, JKL, xyz, XYZ (though each of the 3 'pairs' can be mixed around as they are identical in a case-insensitive comparison)

*…

Like

J. Woolley
J. Woolley
Jun 18, 2023

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)


Like
J. Woolley
J. Woolley
Jun 19, 2023
Replying to

Thank you for your interest. As mentioned in my comment, the test functions demonstrate both methods are case-sensitive. WordBasic.SortArray truncates strings longer than 255 characters.

Like

J. Woolley
J. Woolley
Jun 13, 2023

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.


Like
J. Woolley
J. Woolley
Jun 18, 2023
Replying to

I'm not sure you need to create an instance of Excel; can't you simply add Tools > References > Microsoft Excel 16.0 Object Library.


Like
bottom of page