CODEWITHSUNDEEP
excelvba
Andrew Perry
Andrew Perry

Reputation: 765

Can I call a variable by constructing it from another variable?

Hypothetical example, because the full example gets way too complicated:

Let's say I have quantities for three sizes of rolltrailer; Bolster, 40RT and 62RT. I store those quantities in the variables VarBol, Var40RT and Var62RT. The type of rolltrailer would then be selected by another variable, which we'll call RTVar.

Is there any way of constructing a variable to use elsewhere based on that? For instance (and I realize this syntax won't work), something like

MsgBox "Quantity is " & ("Var" & RTVar)

Or will I end up needing to just a bunch of IF statements?

Upvotes: 2

Views: 37

Answers (1)

John Coleman
John Coleman

Reputation: 51998

As @Rory suggested, use a dictionary if you want to access data using strings:

Sub test()
    Dim D As Object, s As String

    Set D = CreateObject("Scripting.Dictionary")
    D.Add "Bol", 0 'this is sort of like Dim Bol as Long
    D.Add "40RT", 0
    D.Add "62R", 0

    'can assign like this
    D("Bol") = 5
    D("40RT") = D("Bol") + 7

    'can use like this
    MsgBox D("Bol")

    'or even like this:

    s = "40RT"
    MsgBox D(s)

End Sub

Upvotes: 3

Related Questions