Sorting a Custom Collection Class

I’ve been following Dick’s VBHelpers Build series (1, 2, 3) and his last post reminded me that, from time to time, I need to sort a collection of items in-memory.

I don’t have to sort all that often, so my approach has changed over time. I’ve kind of settled on the following.

Let’s say I have a People collection that contains Person items.

In my Person class I’ve written a method (Function) called CompareTo. It works a lot like VBA’s StrComp, returning -1, 0 or +1 depending on whether Item 1 is less than or greater than Item 2.

I’d use it against two person items: person1.CompareTo person2

In my People collection class, I’ve created a method called Sort that returns itself in sorted order.

It’s an Insertion Sort that I converted from Wikipedia’s article into VBA. Notice how it uses the CompareTo method for deciding on item placement.

Now I get to use the above in my main code routines:

The code above is available for download. It’s an extension of the code I posted a year ago on the same topic (links 1, 2). It also includes the Enum enhancements suggested by Andy Pope way back then.

You can download

4 thoughts on “Sorting a Custom Collection Class

  1. I had a similar problem recently that I solved differently. The idea of cascading array values up to make room for the new one seemed wasteful to me. And I wondered if the feature in Collections of being able to insert before or after (which incidentally Dictionaries do not have) would make for a more efficient algorithm. I realize that if performance was an issue the bubble sort would not be chosen, so this may be moot, but does anyone think the before/after insert feature would make a difference – either from a performance perspective, or a cleanliness perspective?


  2. In these situations I’ve used a Doubly Linked List
    The trouble with implementing doubly linked lists in VBA is that you need to program it in a way that avoids a memory leak – a bit of a pain.

  3. Rob, I have been trying to use a doubly-linked list in an app I am working on. I am curious about your remark about avoiding a memory leak when trying to use such a construct. Do you have any examples of what needs to be done to prevent memory leaks?

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.