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):
Newton Excel Bach, not (just) an Excel Blog 
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
LikeLike
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).
LikeLike
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
LikeLike
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.
LikeLike
Pingback: Q1 2009 Wrap up - Code For Excel And Outlook Blog
Pingback: Q1 2009 Wrap up
Can this function be modified to ignore blank cells in the sort column?
Regards,
Steve
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
Pingback: Daily Dose of Excel » Blog Archive » Euler Problem 124
Pingback: Randomising a list « Newton Excel Bach, not (just) an Excel Blog
clever article! I think that assists me nicely. Answers a few concerns for me. TY!
LikeLike
Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog
Pingback: Q1 2009 Wrap up - JP Software Technologies
Pingback: Sorting with Python | Newton Excel Bach, not (just) an Excel Blog
Pingback: Dynamic sorting with Excel, VBA, and Python | Newton Excel Bach, not (just) an Excel Blog
Hello,
Is it possible to ignore blanks? If you make sort Descending for SortV than first will come blank rows and in end of table sorted values. It is ok to fill table with blank values in end of table.
Thanks
LikeLike
Hi metroxx – so you want rows with a blank in the index column to be sorted to the end?
It’s certainly possible, but it would need a re-write of the function because as written the function converts blank cells to a value of 0. The Excel sort routine works that way, so you could have a look at the Chip Pearson page, linked at the top of the article.
LikeLike
Hello,
I have done it with built in RANK function (ignore blanks as well) for sorting column and INDEX,MATCH for finding rest data of table. I just founded that for small tables it works faster than SortV function. Don’t know why.
(I have very complicated spreadsheet with a lots of built in functions in VBA and if I use SortV than PC starts “thinking” for 1,2 sec. after updates. With RANK it is not so expressed)
LikeLike
Pingback: New sort functions and sorting to groups | Newton Excel Bach, not (just) an Excel Blog