There is often a need to sort data within a VBA routine, but VBA does not have a built in sort function, so I recently had a look for what is on offer on the Internet.
Chip Pearson has an article on Sorting Array in VBA, which provides a method to transfer data to a worksheet, sort it, and bring it back, which has some disadvantages which he discusses in the article. It also has an implementation of the Quick Sort method which I will have a closer look at when time allows.
John Walkenbach has an article at Daily-Dose-of Excel on Dynamic Sorting with a UDF, which whilst interesting has the disadvantages that it doesn’t work in Excel 2007, and is not supposed to work (but does) in earlier versions.
The procedure I ended up adapting comes from Xtreme Visual Basic Talk, and is an implementation of a comb sort. It was written in VB for arrays of longs, but was easily adapted to VBA, with the additional features:
- It can be used as a VBA function to sort an array, or on a worksheet as a User Defined Function (UDF).
- It will sort multi-column ranges or arrays, with a selected column as the sort key.
- The sort can be ascending or descending.
- The data can be numbers, text, or mixtures of both.
When used as a UDF the function must be entered as an array function:
- select the range for the sorted data
- enter the function
- Press ctrl-shift enter
When used with a VBA array the array must be specified with 2 dimensions; i.e a vector array with n entries must be converted to an nx1 array .
The comb sort was chosen because it is reasonably fast, was simple to convert to variant arrays, and was found to be stable with large data sets. Sorting a single column array of 1 million random numbers takes about 20 seconds. For best performance on very large data sets the Radix method is superior. If anyone knows of an implementation of this method that will work on a mixed list of floating point numbers, longs, and strings I would be interested to see it.
A sample worksheet with open source code can be downloaded from: Sort Func.zip
Screen shot of the function used as a UDF (click image for full size view):