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?

Solution

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

Comments

[Dmitry Pavliv]

As I know, you can't..You should use Dictionary if you want to iterate through key/value pairs..

[Melroy van den Berg]

Thanks for the early binding, new Dictionary didn't work. But New Scripting.Dictionary did :D Thx again.

[Johnson Jason]

doesn't work in my VBA script. Am I needing a lib or something?

[Peter Albert]

@JohnsonJason: yes, to Microsoft scripting runtime as mentioned in the commented code - or use the commented late binding code, then no reference is required

[Ken]

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!