VBA's Collection object provides a convenient way to handle sets of data, objects etc. However, VBA does not natively implement any methods for sorting them. One solution is the Dictionary object (reference the Microsoft Scripting Runtime library); however, Dictionary objects are not always convenient to use, especially when working with VBA's built-in Collections from various MS Office object models.
Fast sorting algorithms (e.g. QuickSort, HeapSort) already exist for VBA Collection objects and free & open-source implementations are easy to find on the internet. However, most implementations only sort simple collections of variables (numbers, words etc.); and are unsuitable for sorting collections of objects based on a named property within the Object's Properties collection.
This article presents a VBA based implementation of the MergeSort sorting algorithm incorporating a number of advanced features for working with Collections of Objects and for customising the output of the algorithm (to sort in ASCending or DESCending order, or obtain DISTINCT results, for example). The VBA module inherits from MergeSort the following properties:
- Preserves original order as far as possible (stable sorting)
- Worst case performance: O(n × log(n))
- Worst case space complexity: O(n) auxiliary (does not sort in place)
- Predictable execution time and memory space requirements, with no need to consider the details of a particular application. Simple to understand, therefore simple to safely modify and cheap to maintain
- Ideal for sorting as a one-off job, but structures are unsuitable for ongoing maintenance where records are continually being inserted. For that purpose you should use a Dictionary or similar structure.
To incorporate this module into your VBA project, unzip the file, then open your VBA Project's VBA editor, and select “Import File” from the “File” menu.