How to make a list of variable names in VBA for Excel

Question

I have a list of a couple of dozen variable names (type integer). I want to assign values to them. The values are in an array. It seems there should be something more elegant than a couple of dozen statements like Vrbl_1 = ValArray(1), Vrbl_2 = ValArray(2), etc.

I want to keep the individual variable names because they have mnemonic value. I tried creating an array consisting of the variable names, being careful to put them in order corresponding to the values in theValArray, but a For Loop using namesArray(i) = ValArray(i) doesn't work. It puts the values into the array of names instead of putting the values into the variable name.

I think what I'm doing wrong has something to do with what are called pointers in the C language. Is there perhaps some way to do this using a Collection?

Answer 1

You can put them in a Dictionary...

set dictionary = CreateObject("scripting.dictionary")

With dictionary
  .Add "Vrbl_1", ValArray(1)
  .Add "Vrbl_2", ValArray(2)
End With

...and then refer to them by name.

dictionary.Item("Vrbl_1")

As you can see, it's a bit of work. Personally, I'd just stick with discrete variable names if there are only a couple dozen, or use the ValArray directly and provide generous comments.