如何在VBA中将字符串计算为对象？

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.

在我之前的问题中,如何为运行时在VBA中提供属性名称的属性赋值?,我学会了在运行时使用CallByName在类中设置属性。

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.

例如,假设我有一个包含以下数据的字符串:工作表(“RAW DATA”)。范围(“A1”)。QueryTable。

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

这是我可能尝试做的事情,其中上面的数据是strParam的输入如下:

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?

在这种情况下,GetObject应在根据工作表(“RAW DATA”)进行评估时返回QueryTable。范围(“A1”)。QueryTable。 VBA中有什么东西可以取代上面的例子中的SomeFunction吗?

3 个解决方案

#1

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

这次你运气不好。没有VBA等效的eval(不管是在Excel中......还是在Access VBA中)。

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

(Application.Evaluate()将字符串计算为Excel表达式,而不是VBA代码。)

#2

Active Scripting Engine can help you. Instantiate ScriptControl ActiveX, use .AddObject() method to add reference to Excel's Application object to the script control's execution environment, set the third parameter to True to make all Application's members accessible too. Then just use .Eval() method to evaluate any property or method, which is the Application's member. The example below shows evaluation of Worksheets() property:

Active Scripting Engine可以为您提供帮助。实例化ScriptControl ActiveX,使用.AddObject()方法将对Excel的Application对象的引用添加到脚本控件的执行环境,将第三个参数设置为True以使所有Application成员也可访问。然后使用.Eval()方法来评估任何属性或方法,它是应用程序的成员。下面的示例显示了Worksheets()属性的评估:

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 on 64-bit Office, this answer may help you to get ScriptControl to work.

如果您使用的是64位Office,这个答案可能会帮助您使ScriptControl工作。

#3

-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.

有“评估”方法(或[]括号)。我认为它不会完全符合您的预期 - 就像在字符串中找到的运行VBA代码一样。您可以在VBA帮助菜单中查找。

#1