How can I evaluate a string into an object in VBA?

Question

In my previous question, How do I assign a value to a property where the property name is supplied at runtime in VBA?, I learned to use CallByName to set a property in a class at run time.

This time, however, I'm trying to figure out how to get an object at run time from a string.

For example, let's say I have a string with the following data: Worksheets("RAW DATA").Range("A1").QueryTable.

Here's what I might try to do where the data above is the input for strParam below:

Function GetObject(strParam As String) As Object
    GetObject = SomeFunction(strParam)
End Function

In this case, GetObject should return a QueryTable when evaluated against Worksheets("RAW DATA").Range("A1").QueryTable. Is there anything in VBA that could take the place of SomeFunction from the example above?

Answer 1

There's the "Evaluate" method (or [ ] brackets). I don't think it will do exactly what you expect - as in run VBA code found in a string. You can look it up in the VBA help menu.

Answer 2

This time you're out of luck. There is no VBA equivalent of eval (not in Excel anyway...there is in Access VBA).

(Application.Evaluate() evaluates strings as Excel expressions, not as VBA code.)