如何调用名称作为文本传递给另一个变量的变量的值?

I need to call a value of a variable as another variable. E.g.

我需要把变量的值称为另一个变量。如。

I assign the FirstVariable = "One"

我将FirstVariable赋值为" 1 "

and then I asssign the Name as Text to

然后我将名字作为文本发送给

SecondVaribale = "FirstVariable" (Note here it is the "TEXT")

SecondVaribale = "FirstVariable"(注意这里是"TEXT")

So now can I call or assign the SecondVariable to return the value as One in any ways?

现在我可以调用或分配第二个变量以以任何方式返回值为1吗?

Means this should return One:

意味着这个应该返回一个:

 Range("A1").Value = SecondVariable 

is that possible?

这有可能吗?

Because I have about 40 such variables to be done in around 4 - 6 instances which I want to drive through a mapping sheet in Excel.

因为我有大约40个这样的变量需要在4 - 6个实例中完成，我想在Excel中通过映射表来完成。

The easy way out is assigning the variables manually which would require manual intervention in future which I want to avoid.

最简单的方法是手动分配变量，这需要手工干预，我希望避免这种情况。

1 个解决方案

#1

You can create your own custom Dictionary or Collection in VBA for Excel 2007. Then you can "name" your variables, and use another string variable to indirectly access those "named variables". Choice of using Dictionary or Collection is how easy you need it to change the value of a "named variable".

您可以为Excel 2007在VBA中创建自己的自定义字典或集合。然后您可以“命名”您的变量，并使用另一个字符串变量来间接访问这些“命名变量”。使用字典或集合的选择是您需要它更改“已命名变量”的值的简单程度。

A Dictionary allows you to add, read, change, and remove key/value pairs. A Collection only allows add, read, and remove; you have to use a subroutine to change a key/value pair. A Collection lets you use a numeric index (like an array) to access the key/value pairs; a Dictionary does not have an array-like feature. A pretty thorough comparison is at http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html

字典允许您添加、读取、更改和删除键/值对。集合只允许添加、读取和删除;您必须使用子例程来更改键/值对。一个集合允许您使用数字索引(比如数组)来访问键/值对;字典没有数组的特征。在http://www.experts- exchange.com/software/office_productivity/office_suites/ms_office/a_3391-use -the- dictionaryclassin - vba.html有一个非常全面的比较

So to adapt your example, and to also show a change in value of a "named variable", here is some example code:

因此，为了适应您的示例，也为了显示一个“已命名变量”的值的变化，这里有一些示例代码:

Public Function test() As String
    ' Dictionary example
    Dim myDictionary, SecondVariable As String
    Set myDictionary = CreateObject("scripting.dictionary")
    myDictionary.Add "FirstVariable", "Four"
    myDictionary.Add "AnotherVariable", "Two"

    SecondVariable = "FirstVariable"

    ' note that "FirstVariable" must be already defined in the Dictionary else an error will occur; from your example this seemed to be the case
    ' if this was not the case then will need a more complex line using: If myDictionary.exists(SecondVariable) Then ... Else ...
    myDictionary.Item(SecondVariable) = "One"
    test = myDictionary.Item(SecondVariable) 'function returns "One"; the current value of "FirstVariable" in the Dictionary
End Function

Public Function test2() As String
    ' Collection example
    Dim myCollection As New Collection, SecondVariable As String
    myCollection.Add "Four", "FirstVariable"
    myCollection.Add "Two", "AnotherVariable"

    SecondVariable = "FirstVariable"

    'myCollection(SecondVariable) = "One"     'Cannot do this with a Collection; have to use a Sub like the example below
    Call setCollectionValue(myCollection, SecondVariable, "One")
    test2 = myCollection(SecondVariable)  'function returns "One"; the current value of "FirstVariable" in the Collection
End Function

Private Sub setCollectionValue(collect As Collection, key As String, value As String)
    On Error Resume Next
    collect.Remove key
    On Error GoTo 0

    collect.Add value, key
End Sub