CODEWITHSUNDEEP
vbaexcelfunctionvariables
Daz
Daz

Reputation: 13

Calling a variable by a string's value in VBA

I've got a pickle I can't seem to find a solution for.

Let's say I have the following code in VBA, which basically, whatever is given for the function Example as a string argument, calls the variable with the same name as the string argument and returns the value of the variable that's been called (if my explanation made any sense):

Private EnterMenu, ExitMenu as String

Function Example(name)

    Select Case name
        Case "EnterMenu"
            Example = EnterMenu
        Case "ExitMenu"
            Example = ExitMenu
        'and so on
    End Select
End Function

That's fine and all, but is there a way without writing a case for every single possibility, for example

Function Example(name)

    Example=Value(name)

End Function

So that it just grabs the value of the string "name", gets the variable with the same name and returns the value of that variable?

Upvotes: 1

Views: 4683

Answers (2)

tigeravatar
tigeravatar

Reputation: 26650

You could declare your public variable as an object and set it up as a dictonary, like so:

Public diValues As Object

Sub InitPubVar()

    Set diValues = CreateObject("Scripting.Dictionary")
    diValues.Add "EnterMenu", "Opening the menu"
    diValues.Add "ExitMenu", "Closing the menu"

End Sub

Now that your global dictionary has been created and populated, you can pull the values from it using the keys you set, which seems to be what you're looking for:

Sub Test()

    MsgBox diValues("EnterMenu")  '--> shows a box displaying the string "Opening the menu"

End Sub

Note that if you are on an older version of Excel, you may need to add a reference to Microsoft Scripting Runtime. This can be done within the VB Editor -> Tools -> References.

Upvotes: 3

Chilangosta
Chilangosta

Reputation: 101

Excel doesn't have an easy way to dynamically call string variables. You can use the CallByName function for objects, which gets you there if you are willing to create a custom class object for your variable. Or you can look at creating a named range for each variable you plan on using, setting the range's value to equal that of the variable and then calling it with theRange("YourVariable").Value within your code.

Some developers feel it's best to avoid using Select Case; if you have a lot of cases you're switching between, you might want to reconsider your logic flow. This is a topic of debate among developers, and I don't know that I can pick a side, but Wikipedia explains some of the potential disadvantages and alternatives others have come up with to avoid switch-based logic.

Upvotes: 1

Related Questions