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?

Answer 1

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