Calling a variable by a string's value in VBA

Question

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?

tigeravatar · Accepted Answer · 2017-09-21 22:53:10Z

3

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.

answered Sep 21, 2017 at 22:53

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chilangosta · Accepted Answer · 2017-09-21 22:49:21Z

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.

Collectives™ on Stack Overflow

Calling a variable by a string's value in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related