如何在VBA for Excel中创建变量名列表

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.

我有一个包含几十个变量名称的列表(类型整数)。我想为它们分配值。值在数组中。似乎应该有一些比Vrbl_1 = ValArray(1),Vrbl_2 = ValArray(2)等几十个语句更优雅的东西。

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.

我想保留各个变量名称,因为它们具有助记符值。我尝试创建一个由变量名组成的数组,小心地按顺序将它们放在与ValeArray中的值相对应的位置,但使用namesArray(i)= ValArray(i)的For循环不起作用。它将值放入名称数组中,而不是将值放入变量名称中。

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?

我认为我做错了与C语言中的指针有关。是否有一些方法可以使用集合来做到这一点?

1 个解决方案

#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.

如你所见,这是一项工作。就个人而言,如果只有十几个,我会坚持使用离散变量名,或者直接使用ValArray并提供慷慨的评论。

#1