VBA Creating a variable name by concatenating string and variable?

Question

I am currently writing some VBA script with the final aim of comparing two dictionaries.

I have a large loop loading information into a dictionary from an xml file. To cut a long story short it ends with something like.

    Dictionary.Add index, info

Now i would like to add the functionality to have 2 dictionaries (dictionary_1 and dictionary_2) and choose which dictionary to write the information to by incorporating it in some kind of loop. This is what im thinking

for i=1 to 2
    ("dictionary_" & i).add key, info
next i

If this worked it would obviously just put the same information in the two dictionaries but that's not the point here, my question is:

How do i reference a variable this way concatenating some kind of string and variable?

Any thoughts or is this not possible/not a good idea for some reason that is beyond me?

Answer 1

You can't construct variables on the fly like that, but you can create an array of dictionaries:

Sub test()
    Dim dictionary_1 As Object
    Dim dictionary_2 As Object
    Dim dictionaries As Variant

    Set dictionary_1 = CreateObject("Scripting.Dictionary")
    Set dictionary_2 = CreateObject("Scripting.Dictionary")

    ReDim dictionaries(1 To 2)
    Set dictionaries(1) = dictionary_1
    Set dictionaries(2) = dictionary_2

    dictionaries(1).Add "a", 1
    dictionaries(2).Add "a", 2

    Debug.Print dictionary_1("a") 'prints 1
    Debug.Print dictionary_2("a") 'prints 2
End Sub

You can also do things like create a dictionary of dictionaries, but in your case an array of dictionaries seems natural.

Note that if you are going this route, you can dispense with the individual dictionaries and just use the array, like this:

Sub test()
    Dim dictionaries As Variant

    ReDim dictionaries(1 To 2)
    Set dictionaries(1) = CreateObject("Scripting.Dictionary")
    Set dictionaries(2) = CreateObject("Scripting.Dictionary")

    dictionaries(1).Add "a", 1
    dictionaries(2).Add "a", 2

    Debug.Print dictionaries(1)("a") 'prints 1
    Debug.Print dictionaries(2)("a") 'prints 2
End Sub