VBA is it possible to set public variable name as another variable value?

Question

I have written a code that is able to generate a variable string that matches another variable name. But when I try to use that uppermost variable as cell value it pastes the variable name but not it's value.

So I have something like:

Public General as string
General = 10

and at some point I end up with a variable

dim CurrentValue as string 
CurrentValue = General

Where I mean to get that "10" but not "General" when I paste that CurrentValue as cell value, so I wonder if it's possible to do and if so then how could I do that?

Use a dictionary. Add an item with a key of "General" and value of 10. Then you can look up "General" from the dictionary and return 10. — BigBen
– BigBen, Commented Sep 2, 2021 at 13:44
"General" (a String that looks like a variable) is not the same as General (a variable that is a String). Your current setup won't work. So use a dictionary with all your current public variables and their corresponding values. — BigBen
– BigBen, Commented Sep 2, 2021 at 13:49
A dictionary is a very old and very common object in most programming languages. Definitely something worth familiarizing yourself with. It's worth noting that anytime you think "Variable variables" or "Variable variable names" you are headed hard and fast towards a very bad time. The answer is almost always "Use an array", or in this case "Use a dictionary". — JNevill
– JNevill, Commented Sep 2, 2021 at 14:00

Tim Williams · Accepted Answer · 2021-09-02 15:44:14Z

1

If you put your Global in the ThisWorkbook module (a worksheet module would also work) you could do something like this:

Option Explicit

Public General As Long

Function GetVar(varName As String)
    GetVar = CallByName(Me, varName, VbGet)
End Function

Then in a regular module:

Sub Tester()
    ThisWorkbook.General = 10
    Debug.Print ThisWorkbook.GetVar("General") ' >> 10
End Sub

In general though, a use case like this really signals a need to rethink things...

answered Sep 2, 2021 at 15:44

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

2 Comments

Tim Williams Over a year ago

FYI - do not take my example as an endorsement of what you're doing here... Referring to variables by their names as strings is generally a code smell....

BigBen Over a year ago

That last sentence needs bolded, highlighted, underlined, italicized...

Collectives™ on Stack Overflow

VBA is it possible to set public variable name as another variable value?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related