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 collection = new Dictionary(); 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?

Accepted Answer

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
```

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

Solution

Problem

Related problems