CODEWITHSUNDEEP
excelvbavariablesexcel-2007
Deepak J
Deepak J

Reputation: 13

How to call a value of a variable whose name is passed as text to another variable?

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

I assign the FirstVariable = "One"

and then I asssign the Name as Text to

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

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

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.

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

Upvotes: 1

Views: 371

Answers (1)

cybermike
cybermike

Reputation: 1147

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

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

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

Upvotes: 1

Related Questions