In excel VBA, how can you sort a dictionary by a field of the Item (similar to C# orderby)?

Question

I have a dictionary in excel VBA where the key is a string (SS #) and the value is an object that has 3 properties (Name, Birthdate and Job Name)

Dim d as Dictionary
Set d = new Dictionary

d.Add "123", obj
d.Add "234", obj2
d.Add "342", obj3

I want to print out a table by in order of Birthdate. In C#, i would do something like this

for each (var item in dict.Items.Orderby(r=>r.Birthdate))

but i can't figure out in VBA how i can sort this dictionary by the Birthdate of the item in that dictionary.

Is this possible in Excel VBA?

@BigBen - thanks for the link but i don't see how you tell the sorted list on how to sort the objects by tihs specific property — leora, Mar 26 '21 at 02:40
You need to implement your own sorting algorithm, and in order to retain the flexibility of picking a member to sort by, you could implement an `IComparer` interface that takes two such objects and determines whether the first comes before (function returns -1) or after (function returns 1) the second (or if they're sorted the same; function returns 0). Some `BirthdateComparer` class could then be passed to your `OrderBy(Dictionary, IComparer)` method, which would proceed to sort the items accordingly. — Mathieu Guindon, Mar 26 '21 at 02:54
...or use `CallByName` and pass in the name of the property to use when comparing items. — Tim Williams, Mar 26 '21 at 03:02
The obvious way is also the shortest. Create an array with SS# and DoB, sort on DoB and use the SS# to retrieve other data from the dictionary in the sequence of the sorted array. All shortcuts are longer. — Variatus, Mar 26 '21 at 03:29
I guess what I meant was that "similar to C#" needs to predate LINQ, and before LINQ and lambdas were a thing in C# you had that kind of `IComparer` stuff, and it's pretty robust. `CallByName` works nicely until you need to sort by `Foo.Bar.Something`, or perhaps you'd want to sort by `Year(r.BirthDate)`. As I said, *in order to retain the flexibility*. Depends if you're looking for a one-shot deal to sort one thing in one place, or if sorting is a concern that needs serious attention. Assuming the items come from a worksheet, why not sort the sheet *then* populate the dictionary? — Mathieu Guindon, Mar 26 '21 at 04:19
Does this answer your question? [How do I sort a collection?](https://stackoverflow.com/q/3587662/11683) — GSerg, Mar 26 '21 at 14:29

score 1 · Answer 1 · answered Mar 26 '21 at 21:28

Here's one approach:

Sub Tester()

    Dim dict As Object, i As Long, dt As Date, itms, e

    Set dict = CreateObject("scripting.dictionary")
    'some test data
    For i = 1 To 10
        dt = Now - Application.RandBetween(500, 5000)
        dict.Add "Object_" & i, GetTestObject("Name_" & i, dt, "Job_" & i)
    Next i
    itms = dict.items
    
    'Stop
    SortObjects itms, "BirthDate"
    Debug.Print "---------Birthdate-------"
    For Each e In itms
        Debug.Print e.Name, e.BirthDate, e.JobName
    Next e
    
    SortObjects itms, "JobName"
    Debug.Print "---------JobName-------"
    For Each e In itms
        Debug.Print e.Name, e.BirthDate, e.JobName
    Next e
    
End Sub

Function GetTestObject(nm As String, dt As Date, jb As String)
    Dim obj As New clsTest
    obj.Name = nm
    obj.BirthDate = dt
    obj.JobName = jb
    Set GetTestObject = obj
End Function

'Sort an array of objects using a given property 'propName'
Sub SortObjects(list, propName As String)
    Dim First As Long, Last As Long, i As Long, j As Long, vTmp, oTmp As Object, arrComp()
    First = LBound(list)
    Last = UBound(list)
    'fill the "compare" array...
    ReDim arrComp(First To Last)
    For i = First To Last
        arrComp(i) = CallByName(list(i), propName, VbGet)
    Next i
    'now sort by comparing on `arrComp` not `list`
    For i = First To Last - 1
        For j = i + 1 To Last
            If arrComp(i) > arrComp(j) Then
                vTmp = arrComp(j)          'swap positions in the "comparison" array
                arrComp(j) = arrComp(i)
                arrComp(i) = vTmp
                Set oTmp = list(j)             '...and in the original array
                Set list(j) = list(i)
                Set list(i) = oTmp
            End If
        Next j
    Next i
End Sub

score 0 · Answer 2 · answered Mar 26 '21 at 14:23

A Dictionary cannot be sorted in the way you suggest out of the box.

Aside from the suggestions proposed by others in the comments, you could consider using an ADODB.Recordset which has rich and fast sorting features.

Please refer to this website for further guidance ADODB.Recordset

This way you loose some handy features of a Dictionary, but for your purpose, I understand you don't need them.

In excel VBA, how can you sort a dictionary by a field of the Item (similar to C# orderby)?

2 Answers2