Iterate over VBA Dictionaries?

Question

I'm using the Dictionary class in the MS Runtime Scripting library to store where labels are going to go for a report template. Is there a way to iterate over all the key value pairs in that dictionary like in Python?

I just want to use the key as the row number (It's all going in column A) and the value will be the label header.

Something like:

For Each key in dict
    Range("A" & key).Value = dict(key)
Next key

score 154 · Accepted Answer · edited Nov 29 '18 at 03:13

154

Try:

For Each varKey In oDic.Keys()
    Range("A" & varKey).Value = oDic(varKey)
Next

Note that the key iterator must be declared as a Variant.

edited Nov 29 '18 at 03:13

Egalth

962
9
22

answered Aug 18 '09 at 20:18

EBGreen

36,735
12
65
85

41

I would add that strKey should be declared `As Variant`, because the `For Each` doesn't work with `String`s. – stenci Sep 09 '13 at 16:03
1

Side note, dictionary keys are returned as an array, so iterating them should be done with a `For...Next` loop for best performance; _do_ use `For Each...Next` when iterating object collections tough. – Mathieu Guindon Jan 16 '23 at 20:25

score 1 · Answer 2 · answered Jan 16 '23 at 20:14

Not a lot out there for this but, if you have multiple values per key, such as in an array, you'll need a bit more code. This is my first time responding to a post here so apologies if I'm doing something wrong...

    Dim dict As New Scripting.Dictionary
    Dim k As Variant
    Dim v As Variant

    Set dict = New Scripting.Dictionary
    'Add code to populate your dictionary

    For Each k In dict.Keys()
        Debug.Print k, Join(dict(k), ",") 'Prints out all the details for the key
        For Each v In dict(k)
            Debug.Print v
        Next
    Next k

Part of this code comes from this post: VBA Dictionary with Dynamic Arrays

Iterate over VBA Dictionaries?

2 Answers2

Linked