如何将字符串评估为 VBA 中的对象？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1745415/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I evaluate a string into an object in VBA?
提问by Ben McCormack
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.
例如，假设我有一个包含以下数据的字符串：Worksheets("RAW DATA").Range("A1").QueryTable.
Here's what I might try to do where the data above is the input for strParambelow:
这是我可能会尝试做的事情，上面的数据是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 SomeFunctionfrom the example above?
在这种情况下，GetObject 在对 进行评估时应该返回一个 QueryTable Worksheets("RAW DATA").Range("A1").QueryTable。VBA 中有什么东西可以代替SomeFunction上面的例子吗？
采纳答案by jtolle
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 代码。）
回答by omegastripes
Active Scripting Engine can help you. Instantiate ScriptControlActiveX, use .AddObject()method to add reference to Excel's Applicationobject to the script control's execution environment, set the third parameter to Trueto 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 可以为您提供帮助。实例化ScriptControlActiveX，使用.AddObject()方法将ExcelApplication对象的引用添加到脚本控件的执行环境中，将第三个参数设置True为也使所有Application的成员都可以访问。然后只需使用.Eval()method 来评估任何属性或方法，即Application的成员。下面的示例显示了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 answermay help you to get ScriptControlto work.
如果您使用的是 64 位 Office，此答案可能会帮助您开始ScriptControl工作。
回答by SnakeWasTheNameTheyGaveMe
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 帮助菜单中查找。
