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?

John Coleman · Accepted Answer · 2016-02-24 12:50:06Z

4

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

answered Feb 24, 2016 at 12:50

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VBA_kid Over a year ago

Ooo Yeah something like this could work. Thank you John.

Community · Accepted Answer · 2017-05-23 12:13:41Z

0

I was working on a similar solution to the one proposed by John Coleman.

Sub dueling_dictionaries()
    Dim dDICTs() As Object, vVALs As Variant, i As Long, j As Long

    vVALs = Range("A1:B4").Value2

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

    dDICTs(1).comparemode = vbTextCompare
    dDICTs(2).comparemode = vbTextCompare

    For i = LBound(vVALs, 1) To UBound(vVALs, 1)
        For j = LBound(vVALs, 2) To UBound(vVALs, 2)
            dDICTs(j).Add Key:=vVALs(i, j), Item:=vVALs(i, 2 + CBool(j = 2))
        Next j
    Next i

    Erase vVALs  'going to reuse this in the following loops

    For Each vVALs In dDICTs(1)
        Debug.Print "key: " & vVALs & " - Item: " & dDICTs(1).Item(vVALs)
    Next vVALs

    For Each vVALs In dDICTs(2)
        Debug.Print "key: " & vVALs & " - Item: " & dDICTs(2).Item(vVALs)
    Next vVALs

End Sub

Results from the Immediate window.

key: A - Item: 1
key: B - Item: 2
key: C - Item: 3
key: D - Item: 4
key: 1 - Item: A
key: 2 - Item: B
key: 3 - Item: C
key: 4 - Item: D

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Feb 24, 2016 at 13:03

user4039065

1 Comment

VBA_kid Over a year ago

Thank you, a case of fastest fingers first. I'm glad to see everyone is in agreement

Collectives™ on Stack Overflow

VBA Creating a variable name by concatenating string and variable?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related