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):
Filed under: Arrays, Excel, UDFs, VBA | Tagged: comb sort, Excel UDF, sort UDF, VBA
Hi Doug -
A Radix sort is listed here:
http://69.20.37.124/showthread.php?p=403676
that points to here:
http://www.xtremevbtalk.com/showpost.php?postid=388016&postcount=13
Also from Xtreme Visual Basic Talk.
…mrt
Thanks for the response mrt. That’s the same guy who wrte the comb sort routine I based my function on. The problem with the radix sorts I have found is that they are all either written for longs, or for strings, and because the sorting is not based on comparisons of values it’s not entirely straightforward getting one that will do both and doubles as well. There are also variants depending on whether they start with the “least significant digit” (LSD, right hand end), or “most significant digit”.
I have actually converted Squirm’s routine to sort doubles, just by changing dim statements from long to double. It works, but I doubt that it is bullet proof. Anyway, I’ll have a closer look at these things when I have time.
For those interested in how a radix sort works without comparing values, there is a nice little animation here:
http://www.cs.auckland.ac.nz/software/AlgAnim/radixsort.html
(go to the bottom of the page and click the “run the animation” button).
Hi Doug – maybe that’s why those animations looked like lemons
That website was most helpful.
I hadn’t realized that about Radix sorts. I’ve been rightly accused of making all the Euler problems string problems (it’s the way I think) but that might be the way for doubles. Put them in a common scientific notation, pad or lead with zeros to align the decimal points, and sort them as strings.
Tomorrow will be fine
…mrt
Mar 24 next year maybe
From the bit of experimenting I’ve done it does seem that radix sorts are amazingly fast, but there isn’t a lot on the Web directly related to VBA (or even VB) implementations, and what there is is confusing. I will be taking another look at this when I get a bit of time though.
[...] NewtonExcelBach — check out Doug’s VBA sorting function. [...]
[...] NewtonExcelBach — check out Doug’s VBA sorting function. [...]
Can this function be modified to ignore blank cells in the sort column?
Regards,
Steve
Steve – it could, but what do you want to do with the blank cells? Do you just want to leave the blank cells where they are, and sort all the data around them, or do you want to treat the data between blank cells as separate groups to be indiviually sorted?
Hi Doug -
I used SortV to do Euler Problem 124. It handled an array E(1 to100000,1 to 2) of longs in under a second and a half.
Two things I found: 1. SortV doesn’t like zero-based arrays, probably because it really wants a range, and 2: I couldn’t get the syntax SortV(E,2) to work. SortV SortRange:=E, SortBy :=2 worked fine. I’ve had problem 2 before in other places, and I don’t know what makes a difference. Problem 1 meant I couldn’t use Split(). No biggie.
if I get the time, I’ll write up #124 over at DDoE this weekend. I’ll send them here to get SortV.
…mrt
Hi Michael.
Glad you found a use for sortv. I’ve fixed it up so it should work with zero based arrays (as well as 1 based).
Please let me know if you have any problems.
Doug
[...] interest. To sort E() I used blogmeister and fellow Eulerian Doug Jenkins' SortV function. Get that here. It worked fine (Doug has since improved it). The whole routine, including the sort of 100,000 [...]
[...] The random number array is sorted using the combsort routine. [...]