CODEWITHSUNDEEP
vbaexcelcollections
Peter Rankin
Peter Rankin

Reputation: 733

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

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?

Upvotes: 24

Views: 71742

Answers (2)

Andreas Reiff
Andreas Reiff

Reputation: 8404

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

Upvotes: 0

Peter Albert
Peter Albert

Reputation: 17505

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

Upvotes: 44

Related Questions