How can I get reference to a variable by using a string, in VBA?

Question

I have a variable strFunction, then I have another string strName = "strFunction" , what I want to know is how can I get the value of strFunction by using strName.

For example, something like getValue(strName) gives me the value of strFunction. Is it possible in Access VBA?

Thanks!

EDIT:

I have a strFunction string, it's a const string. In my code I want to use Len("strFunction") to test the length of it, but what i got is the length "strFunction". So I need a get-value-out-of-variable-name function. I have tried Eval(), but it cannot do this, even I write a get_strFunction(), eval("get_strFunction()") gives me error, telling me it cannot find it.

Private Const strFunction as String = "FilterByType_1"
Private Function get_strFunction()
    get_strFunction = strFunction
End Function

Works for me, if I change Private Function to Public Function — Jacob
– Jacob, Commented Jul 11, 2011 at 8:56

HansUp · Accepted Answer · 2011-07-11 17:27:56Z

4

"I have a variable strFunction, then I have another string strName = "strFunction" , what I want to know is how can I get the value of strFunction by using strName."

Instead of a variable, strFunction could be the key for an item in a VBA collection.

Public Sub darkjh()
    Dim strName As String
    Dim col As Collection
    Set col = New Collection
    col.Add "FilterByType_1", "strFunction"
    strName = "strFunction"
    Debug.Print col(strName)
    Set col = Nothing
End Sub

Edit: Instead of a VBA collection, you could use a Scripting.Dictionary.

Dim strName As String
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "strFunction", "FilterByType_1"
strName = "strFunction"
Debug.Print dict(strName)
Set dict = Nothing

edited Jul 11, 2011 at 17:27

answered Jul 11, 2011 at 14:15

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

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

3 Comments

darkjh Over a year ago

OH just like a dictionary in Python. I'll try it out tomorrow!

HansUp Over a year ago

Yes. I added a Scripting.Dictionary example, which you might prefer if you're a Python person.

steveOw Over a year ago

These 2 methods also work in VB6 as a way to replace a variable-string name inside a string by the contents of that variable-string. (Note for VB6 users, as Mods deleted my VB6 specific question).

Jacob · Accepted Answer · 2011-07-11 08:19:06Z

0

Option Compare Database

Dim a As String
Dim b As String

Public Sub test()
    a = "b"
    b = "test-string"
    Debug.Print Eval("get" & a & "()")
End Sub

Public Function getB() As String
    getB = b
End Function

Output

>>test
test-string

eval(a) did not work, so I had to write a "getter" for the variable and eval that function: eval("get" & a & "()").

edited Jul 11, 2011 at 8:19

answered Jul 11, 2011 at 7:30

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

3 Comments

darkjh Over a year ago

eval function cannot get a value of a variable... eval("strFunction") gives me error.

Jacob Over a year ago

@darkjh update the question with your code, so we can help you.

David-W-Fenton Over a year ago

If you're using Eval(), you want eval(strFunction) instead of eval("strFunction") -- putting the quotes in forces Eval() to look at the literal string of the variable name, whereas leaving out the quotes causes Eval() to look at the string stored in the variable.

Collectives™ on Stack Overflow

How can I get reference to a variable by using a string, in VBA?

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related