Indirectly referring to a variable

[ZipCurs]

Hello,

This is probably easy ... or impossible, but I cannot figure out how to do
this. I want to assembly a variable name in code, and then get that
variable. Separate parts of my macro tell me my prefix is "Snow" and my
suffix is "Shoe". How to I get the value of "SnowShoe"? I have a lot of
these variables. The following does not work:

VariableIWant="Snow" & "Shoe"
NumberINeed=5+VariableIWant

I ended up with a bunch of If statements. Effective but not too elegant.
Any help would be appreciated. Thank you in advance.

 

[JLGWhiz]

I am not clear on what you are after, but if you place a variable inside
quote marks, it becomes a string and the value is what you see. e.g. If the
variable Snow = 25, when you place it inside quotes then "Snow" = "Snow".
So, if you want the values of Snow and Shoe to be added it would be
VariableWant = Snow + Shoe to get VariableWant to equal the sum of Snow +
Shoe. Then you could add NumberNeed = 5 + VariableWant and get a numeric
value. But if you use quotes around the variables, the all you get is
concatenated strings.

 

[Tim Williams]

Where/how is the variable "SnowShoe" defined ?

If you really want to map data this way then you might use (eg) a
dictionary object

'**********************
dim d as object
set d=createobject("scripting.dictionary")
d.add "SnowShoe", 20
d.add "RainShoe", 10
Msgbox d.Item("Snow" & "Shoe") '20
'**********************



Tim

 

[ZipCurs]

Hello Tim,

I am not sure it matters but lets say that ShowShoe is Double. I get to a
situation where I figure out what variable I want, and then need to get it.
So I reach a point where I know I want SnowShoe, but I need a way of getting
it. I think something like what you gave me would work fine. I will try it
later.

 

[ZipCurs]

Thank you for responding. I think that Tim Williams' response is closer to
the mark. Basically, I have a bunch of variables, and am looking for an easy
way to get what I want. I want to use SnowShoe this time. Next time I might
want HikingBoot.

 

[norie]

You can't construct varibale names like this in VBA.

Using a dictionary might be a workaround, so might an array approach.

 

[ZipCurs]

Hello Tim Williams,

Your recommendation works great. The only addition is to use

d.item("SnowShoe")= NewValue

to make "SnowShoe" behave like a variable and be able to call it by
assembling the name. Thanks.