Can I loop through key/value pairs in a VBA collection?

you cannot retrieve the name of the key from a collection. Instead, you'd need to use a Dictionary Object:

Sub LoopKeys()    Dim key As Variant    'Early binding: add reference to MS Scripting Runtime    Dim dic As Scripting.Dictionary    Set dic = New Scripting.Dictionary    'Use this for late binding instead:    'Dim dic As Object    'Set dic = CreateObject("Scripting.Dictionary")    dic.Add "Key1", "Value1"    dic.Add "Key2", "Value2"    For Each key In dic.Keys        Debug.Print "Key: " & key & " Value: " & dic(key)    NextEnd Sub

This answwer is not iterating over keys of a collection - which seems to be impossible, but gives some more workarounds if you do not want to use a Dictionary.

You can do a collection of KeyValues as outlined in https://stackoverflow.com/a/9935108/586754 . (Create keyvalue class and put those into the collection.)

In my (non Excel but SSRS) case I could not add a class and did not want to add a .net reference.

I used 2 collections, 1 to store keys and 1 to store values, and then kept them in sync when adding or deleting.

The following shows the add as an example - though it is limited to string/int key/value, and the int value s not stored but added to previous values, which was needed for me aggregating values in SSRS. This could be easily modified though to not add but store values.

ck key collection, cv value collection.

 Private Sub StoreAdd(ck As Collection, cv As Collection, k As String, v As Integer)    Dim i As Integer    Dim found As Boolean = false    Dim val As Integer = v    For i = 1 to ck.Count        if k = ck(i)            ' existing, value is present            val = val + cv(i)            ' remove, will be added later again            ck.Remove(i)            cv.Remove(i)        End If        if i <= ck.Count            ' relevant for ordering            If k > ck(i)                ' insert at appropriate place                ck.Add(k, k, i)                cv.Add(val, k, i)                found = true                Exit For            End If        End If    Next i    if not found        ' insert at end         ck.Add(k, k)        cv.Add(val, k)    End If End Sub