variable variables

[Pablo Cardellino]

Is there any kind of variable variables in VBA? I mean, it is possible to
evaluate the value of a variable as being the name of another one, giving
access to its value?

Example:

a = "hi"
var = "a"

MyRange.Text = ????? ' something pointing to the value of var, "a",
considered as the variable a. This would evaluate as "hi"

Regards

 

[Jay Freedman]

Is there any kind of variable variables in VBA? I mean, it is possible to
evaluate the value of a variable as being the name of another one, giving
access to its value?

Example:

a = "hi"
var = "a"

MyRange.Text = ????? ' something pointing to the value of var, "a",
considered as the variable a. This would evaluate as "hi"

Regards

Hi Pablo,

You can use a Collection object this way. The Add method associates the item's
value with a string 'key', and you can use a string variable to hold the keys.
Here's a quick demonstration:

Sub x()
Dim coll As New Collection
Dim myVar As String
Dim rsult As String

coll.Add "hi", "a"
coll.Add "hello", "b"
coll.Add "goodbye", "c"

myVar = "a"
rsult = coll(myVar)
MsgBox rsult ' MyRange.Text = rsult

myVar = "c"
rsult = coll(myVar)
MsgBox rsult ' MyRange.Text = rsult
End Sub

 

[Pablo Cardellino]

Thanks, Jay,
this can work.

Best regards,