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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Public Function CompareTo(per As Person) As Long Dim i As Long If Me.LastName = per.LastName Then If Me.FirstName = per.FirstName Then i = 0 ElseIf Me.FirstName < per.FirstName Then i = -1 Else i = 1 End If ElseIf Me.LastName < per.LastName Then i = -1 Else i = 1 End If CompareTo = i End Function |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Public Function Sort() As People Dim i As Long, j As Long, k As Long, bln As Boolean Dim lngCount As Long, arr() As Long, ppl As People lngCount = Me.Count If lngCount > 0 Then ReDim arr(0 To lngCount - 1) For i = 0 To lngCount - 1: arr(i) = i + 1: Next For i = 1 To lngCount - 1 k = arr(i) j = i - 1 bln = False Do If Me(arr(j)).CompareTo(Me(k)) > 0 Then arr(j + 1) = arr(j) j = j - 1 If j < 0 Then bln = True Else bln = True End If Loop Until bln arr(j + 1) = k Next End If Set ppl = New People For i = 0 To lngCount - 1: ppl.Add Me(arr(i)): Next Set Sort = ppl End Function |
Now I get to use the above in my main code routines:
|
1 2 3 4 5 6 7 8 9 |
Sub test3() Dim ppl As People, per As Person Set ppl = New People ppl.FillFromSheet ActiveSheet For Each per In ppl.Sort Debug.Print per.FirstName; vbTab; per.LastName; vbTab; per.Gender.ToString; vbTab; per.City Next End Sub |
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 SortClass.zip
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?
–Charlie
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.
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?
Vernon: take a look at this comment from a few years back.