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

Question

In VB.NET, I can iterate through a dictionary's key/value pairs:

Dictionary<string, string> collection = new Dictionary<string, string>();
collection.Add("key1", "value1");
collection.Add("key2", "value2");

foreach (string key in collection.Keys)
{
    MessageBox.Show("Key: " + key + ".  Value: " + collection[key]);
}

I know in VBA I can iterate through the values of a Collection object:

Dim Col As Collection
Set Col = New Collection
Dim i As Integer
Col.Add "value1", "key1"
Col.Add "value2", "key2"

For i = 1 To Col.Count
    MsgBox (Col.Item(i))
Next I

I also know that I do this with a Scripting.Dictionary VBA object, but I was wondering if this is possible with collections.

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

As I know, you can't..You should use `Dictionary` if you want to iterate through key/value pairs.. — Dmitry Pavliv, Jan 29 '14 at 13:17

score 43 · Accepted Answer · answered Jan 29 '14 at 13:50

43

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)
    Next
End Sub

answered Jan 29 '14 at 13:50

Peter Albert

16,917
5
64
88

1

Thanks for the early binding, new Dictionary didn't work. But New Scripting.Dictionary did :D Thx again. – Melroy van den Berg Jun 04 '14 at 11:29
doesn't work in my VBA script. Am I needing a lib or something? – Johnson Jason Oct 02 '15 at 20:24
1

@JohnsonJason: yes, to Microsoft scripting runtime as mentioned in the commented code - or use the commented late binding code, then no reference is required – Peter Albert Oct 03 '15 at 21:40
2

This is an excellent answer and for the fact that you showed the example with both early and late binding - making it clear and also informing others of the difference . Wish I could plus 10 you! – Ken Mar 08 '16 at 22:56

score 0 · Answer 2 · answered Jan 28 '21 at 21:23

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

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

2 Answers2

Linked