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 | jamiebull1@gmail.com

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 useful […]

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 is […]

7 Comments on “Sorting functions for VBA”

  • David says:

    Can you show a sample implementation of the algo? I.e., what are the arguments?

    • Jamie Bull says:

      A sample would be mySortedArray = CombSort(inputArray, 0, Descending = False) which will return an array sorted in ascending order by the values in column 0.

  • Martin Rüegg says:

    thank you for your code!

    I’ve found, that your current CombSort would not correctly sort Array(“test2″,”test3″,”test1”) unless you removing the the following line

    Do Until (i + Gap) >= UBound(TempArray)

    to

    Do Until (i + Gap) > UBound(TempArray)

    additionally in both functions you could simply use

    Dim Temp As Variant
    '...
    Temp = TempArray(i, Col)
    '...
    TempArray(i + Gap, Col) = Temp

    instead of

    Dim Temp() As Variant
    '...
    ReDim Temp(0 To UBound(TempArray, 2))
    '...
    Temp(Col) = TempArray(i, Col)
    '...
    TempArray(i + Gap, Col) = Temp(Col)

    as you store only one field at the time anyway, which would lower the memory footprint.

    furthermore you can shorten both functions by using

    If ((TempArray(i, SortIndex) TempArray(i + Gap, SortIndex)) And Descending = False) Then

    instead of your if … elseif … end if-statement and thus have to write the actual copy-block only once.
    brgds,
    martin.

    • Martin Rüegg says:

      actually, the code

      If ((TempArray(i, SortIndex) TempArray(i + Gap, SortIndex)) And Descending = False) Then

      can be further compacted by simply using

      If TempArray(i, SortIndex) TempArray(i + Gap, SortIndex) Then

      brgds,
      martin.

      PS: my code was – and will probably again – not displayed correctly. some parts in the if-clause has simply been discarded. trying to use -pre- instead of -code- in the following sample

      • Martin Rüegg says:

        aha, > has to be written as >. horrible.

        so my short version would read:

        the longer version was:

  • Martin Rüegg says:

    … of course, the short version is wrong …! 😀

Leave a Reply

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