Get VBA variable value with variable name in string

[Zari_the_girl]

Dear VBA programmers,

I've searched all over the internet but have not yet found the solutions to the following issue:

I want to dynamically get the value of global variables by dynamically using the variable name.

So imagine there are global variables defined in a VBA Excel application (add-in) e.g.,

Public gsTestString as String
Public gsAnotherTest as String
etc etc.

that have values assigned to them at some point in some module, e.g.
gsTestString = "Hello"
gsAnotherTest = "Please help"

Now I want to make a function where one can enter the name of the global variable (as a string) and get the value of that variable back.

So for instance making a function GetVariableValue where GetVariableValue("gsTestString") would return "Hello", but GetVariableValue("gsAnotherTest") would return "Please help".

I don't want to put all variables and their values in arrays or anything similar, as the application has lots of different variables over different modules so putting all of those in arrays would be far from ideal.

Does anybody know how to refer to a variable if you only have its name in a String?

Thanks a lot in advance!

 

[Fazza]

what about

Function GetValueString(ByVal strInput As String) As String
  GetValueString = strInput
End Function

 

[Zari_the_girl]

Hello Fazza,

Thank you for the quick response!

But that just returns the name of the variable, so if I use your function and then use it as
GetValueString("gsTestString")

Then it just returns the string gsTestString and not it's value/content (so not "Hello"), it still sees the variable name as a string and not a variable.

 

[Norie]

Why would you need this to access global variables?

Variables declared globally can be accessed anywhere by using their name.

 

[Zari_the_girl]

Hello Norie!

There are users who want to access variables in Excel (in a cell), and there you can access functions but for as far as I know you can't directly ask for the variables.
We had a similar problem before when we needed to access a global variable in a different project (apparently they're not THAT global) and then we used a function like 'GetThisSpecificVar()' but we do not want to make lots of separate functions like that, either in different projects or in Excel.

 

[Norie]

If you want the values available in both Excel and VBA why not create names for them?

 

[mikerickson]

You could use a UDF like

Function ValueOfArg(argName as String) As Variant
    Select Case argName
        Case "gsTestString" 
            ValueOfArg = gTestString
        Case "gsAnotherTest"
            ValueOfArg = gsAnotherTest
        ' ...
        Case Else
            ValueOfArg = vbNullString
    End Select
End Function

users who want to access variables in Excel (in a cell)

I find this a very odd situation.
I suspect that many of your global variables could be replaced by UDF's.

 

[Norie]

Mike

Could a class, in combination with CallByName, be used for what the OP wants to do?

 

[Zari_the_girl]

Hi Mike and Norie,

As the variables already exist, putting them in an array or creating names for them or making a big case statement would require an action per variable, I was hoping to go around that.

For now I put a lot of variables with both name and value in a public array and then I can retrieve the value by using the name with that. But to do this I need to put all the values in an array first, if I get new global variables I'll have to put those in the array too, etc. I think the same would go for the case statement (put all of them in a case statement, update the case statement whenever new global variables are used etc.) or I'd have to create names. So it does work, but I'm looking for a solution where it is fully dynamic and I don't need to do something specifically with every variable.

But maybe I just want the impossible and should just go with one of the work-arounds maybe

I'm not sure what Norie means with the class in combination with CallByName, maybe that's something, if so please let me know!

Thanks for your thoughts!

 

[Kyle123]

@Norie, no you'd need a property for each variable.

The situation is indeed odd, global variables should be avoided at the best of times and having them "over different modules" sounds even worse

How about a collection? A lot better choice than an array, true, they'll still need adding, but at least they're directly accessible by string

That said, a class would be a nicer solution for this as at least it gives you immutability - though UDFs would do this as well and are accessible from cells