Sorting Strings in VBA

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!