One of the things I find most frustrating about VBA for Excel is the lack of a native sorting method for arrays. Perhaps it’s because there’s the option of sorting it on the worksheet, but that’s not always viable.

This post is mainly just a store for VBA implementations of sorting algorithms and will probably be updated as and when I come across other algorithms that seem like they’d be useful in VBA code.

Each of the functions has been implemented in a way which allows you to specify the sort order, with a default of descending. It also allows you to specify what column of the array to sort on. The syntax is myArray = AlgorithmName(inputArray, 0, Descending = False) which will return an array sorted in ascending order by the values in the first column.

Worth noting is that although comb sort is generally faster than bubble sort, it is not “stable”, meaning that identical items will not necessarily preserve their order from before sorting. This means that if you want to sort by one column and then by another, while keeping the results of the first sort in their sorted order you will need to use bubble sort. Where this isn’t important, comb sort is probably a better choice.

Bubble sort

In developing the Pareto frontier code I had to search around on the net for a good sort method and came across Andy Pope’s implementation of bubble sort. This code is based on that.

Comb sort

Bubble sort is well known for being a slow algorithm as the length of the list to be sorted increases so I decided to take a look around for other algorithms to implement. The Wikipedia page for sorting algorithms came up. The summary of comb sort seemed promising, particularly given the comment that it took only a small amount of code.

The following code is based on the pseudocode on the Wikipedia page for comb sort.

Happy sorting!

Jamie Bull |

Related Posts

WiGLE is a popular platform which can be used for finding the location of a device using the names of WiFi networks in its vicinity. I’ve written about this before, and wrote some Python code to interact with their API. This API has since been retired and replaced with a new one, as of December […]

Just a quick post to point out a couple of really useful tools. The first is a web-based tool for finding weather files for a location of interest. It’s similar to the Excel EPW finder tool we created a few years back, but much more modern looking. It is however missing a few of the […]

Eppy is a really useful library which I’ve written about several times, since before I really had anything to offer in terms of contributing code. Over the past year or so though, I’ve started to contribute back some of the changes and additions I’ve made while using eppy on academic and commercial projects. This post […]

Leave a Reply

Your email address will not be published. Required fields are marked *