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

Question

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

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

For example, suppose I have a line with the following data: Worksheets("RAW DATA").Range("A1").QueryTable .

Here is what I can try to do where the above data to enter strParam below:

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

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

Answer 1

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

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

Answer 2

Active Scripting Engine can help you. Activate ScriptControl ActiveX, use the .AddObject() method to add a reference to the Excel Application object in the script management runtime, set the third parameter to True to make all Application members accessible. Then simply use the .Eval() method to evaluate any property or method that is a member of Application . The following example shows an evaluation of the Worksheets() property:

 Sub TestQueryTable() Dim objQueryTable As QueryTable Dim strEvalContent As String strEvalContent = "Worksheets(""RAW DATA"").Range(""A1"").QueryTable" Set objQueryTable = EvalObject(strEvalContent) objQueryTable.Refresh MsgBox objQueryTable.Connection End Sub Function EvalObject(strEvalContent As String) As Object With CreateObject("ScriptControl") .Language = "VBScript" .AddObject "app", Application, True Set EvalObject = .Eval(strEvalContent) End With End Function 

If you are in 64-bit Office, this answer may help you get ScriptControl to work.

Answer 3

There is a "Rate" method (or [] brackets). I don’t think it will do exactly what you expect - like in the VBA code found in the line. You can find it in the VBA help menu.