VBA looping through dictionary keys and items

Question

Noob question. I realize this has probably been asked before, but I still need some help as I have never worked with dictionaries in VBA.

I found a nice piece of code which turns columns into a dictionary which is exactly what i need (see below) but once it converts a range into a Dictionary, I can not figure out how to do anything with it. even simple Debug.Print or loop through the keys values. I went through countless articles and youtube videos (explaining dictionaries, but nothing "this advanced"), I'm missing something.

Sub Test()
    RangeToDict2 Range("H2:I36")

    Debug.Print RangeToDict2.Item("a") 'FAILS HERE
End Sub
Function RangeToDict2(ByVal R As Range) As Dictionary
    Set RangeToDict2 = New Dictionary
    i = 1
    Do Until i >= (R.Rows.Count * R.Columns.Count)
        RangeToDict2.Add R(i), R(i + 1)
        Debug.Print R(i) & ", " & R(i + 1)
        i = i + 2
    Loop
End Function

https://stackoverflow.com/questions/1296225/iterate-over-vba-dictionaries — braX, Sep 19 '19 at 09:14
So keys are the `R(i)` from your function and the item is returned when you type `RangeToDict2(Key)`. To loop through a dictionary, the easiest way is to declare a `Variant Variable` like `Dim Key As Variant` and loop through the keys with: `For Each Key in RangeToDict2.Keys` then `Key` will get the value of each key everytime. — Damian, Sep 19 '19 at 09:19
@Damian, Did exactly what you said yet I still get an error ```Compile Error: Argument not optional``` and it highlights ```RangeToDict2.Keys``` in ```For Each Key in RangeToDict2.Keys``` line of code — Mr.Riply, Sep 19 '19 at 09:29

score 1 · Accepted Answer · answered Sep 19 '19 at 09:29

1

You should read a tutorial or two on scripting dictionaries, as suggested by braX.

A particular issue in your example is that RangeToDict2 is a function with a Range parameter and which returns a dictionary so Debug.Print RangeToDict2.Item("a") doesn't really make sense as Item("a") is not a range. You are likely to need something more like this:

Sub Test()

Dim d As Object

Set d = RangeToDict2(Range("A1:B3"))
Debug.Print d.Item("a") 'FAILS HERE

End Sub

You can then loop through the keys or items of d.

answered Sep 19 '19 at 09:29

SJR

22,986
6
18
26

1

thank you for your help, when I try to print ```key's``` ```For Each Key In d.key``` its works , but for ```items``` ```For Each itm In d.Item``` i get this fun error ```Compile Error: Argument not optional``` – Mr.Riply Sep 19 '19 at 10:05
1

It's `Items` (and `Keys`). https://excelmacromastery.com/vba-dictionary/ – SJR Sep 19 '19 at 10:23

VBA looping through dictionary keys and items

1 Answers1