如何使用变量在 VBA (Excel) 中设置属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5706791/
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 do I use variables to set properties in VBA (Excel)
提问by asoundmove
Take this code:
拿这个代码：
With ActiveSheet.Shapes.AddShape(msoShapeRectangle, x, y, w, h).TextFrame
.Parent.Line.Visible = False
.Parent.Fill.ForeColor.RGB = RGB(r, g, b)
End With
Is there any VBA way to "execute" or "evaluate" like can be done in perl/python/... such that the text .Parent.Line.Visiblecan be drawn from a variable (or cell value), rather than hard coded?
是否有任何 VBA 方法可以“执行”或“评估”，例如可以在 perl/python/... 中完成，这样文本.Parent.Line.Visible可以从变量（或单元格值）中提取，而不是硬编码？
ParentLine = ".Parent.Line.Visible"
ParentLineValue = "False"
With ActiveSheet.Shapes.AddShape(msoShapeRectangle, x, y, w, h).TextFrame
**eval**(ParentLine & "=" & ParentLineValue)
.Parent.Fill.ForeColor.RGB = RGB(r, g, b)
End With
EDIT: I found MSDN information for Accessthat mentions Eval, but when I execute my code it says "Undefined Sub or Function", pointing at Eval (Excel does not seem to know this function).
编辑：我找到了提到 Eval 的Access 的 MSDN 信息，但是当我执行我的代码时，它说“未定义的子或函数”，指向 Eval（Excel 似乎不知道这个函数）。
EDIT 2: Found the definitive (negative) answer on SO.
编辑 2：在 SO 上找到了明确的（否定的）答案。
EDIT 3: Seems like there is an answer after all, as I am not after a general solution for arbitrary code execution. Thanks to GSerg for helping with using CallByName.
编辑 3：似乎毕竟有一个答案，因为我不是在追求任意代码执行的通用解决方案。感谢 GSerg 帮助使用 CallByName。
回答by GSerg
Solution 1.
解决方案1。
Use CallByName.
使用CallByName.
Option Explicit
Private Type Callable
o As Object
p As String
End Type
Public Sub SetProperty(ByVal path As String, ByVal Value As Variant, Optional ByVal RootObject As Object = Nothing)
With GetObjectFromPath(RootObject, path)
If IsObject(Value) Then
CallByName .o, .p, VbSet, Value
Else
CallByName .o, .p, VbLet, Value
End If
End With
End Sub
Public Function GetProperty(ByVal path As String, Optional ByVal RootObject As Object = Nothing) As Variant
With GetObjectFromPath(RootObject, path)
GetProperty = CallByName(.o, .p, VbGet)
End With
End Function
Public Function GetPropertyAsObject(ByVal path As String, Optional ByVal RootObject As Object = Nothing) As Object
With GetObjectFromPath(RootObject, path)
Set GetPropertyAsObject = CallByName(.o, .p, VbGet)
End With
End Function
Private Function GetObjectFromPath(ByVal RootObject As Object, ByVal path As String) As Callable
'Returns the object that the last .property belongs to
Dim s() As String
Dim i As Long
If RootObject Is Nothing Then Set RootObject = Application
Set GetObjectFromPath.o = RootObject
s = Split(path, ".")
For i = LBound(s) To UBound(s) - 1
If Len(s(i)) > 0 Then
Set GetObjectFromPath.o = CallByName(GetObjectFromPath.o, s(i), VbGet)
End If
Next
GetObjectFromPath.p = s(UBound(s))
End Function
Usage:
用法：
? getproperty("activecell.interior.color")
16777215
SetProperty "activecell.interior.color", vbYellow
'Sets yellow background
? getproperty("names.count", application.ActiveWorkbook)
0
? getproperty("names.count", GetPropertyAsObject("application.activeworkbook"))
0
Solution 2.
解决方案 2。
Dynamically add code.
Don't do this. It's wrong and it requires having that "Allow access to VB project"tick set.
动态添加代码。
不要这样做。这是错误的，它需要设置“允许访问 VB 项目”。
Add a reference to Microsoft Visual Basic for Applications Extensibility X.X.
添加对Microsoft Visual Basic for Applications Extensibility X.X.
Create a module called ModuleForCrap.
创建一个名为ModuleForCrap.
Add a dynamically constructed sub/function:
添加动态构造的子/函数：
ThisWorkbook.VBProject.VBComponents("ModuleForCrap").CodeModule.AddFromString _
"function foobar() as long" & vbNewLine & _
"foobar = 42" & vbNewLine & _
"end function"`
Call it:
称它为：
msgbox application.run("ModuleForCrap.foobar")
Delete it:
删除它：
With ThisWorkbook.VBProject.VBComponents("ModuleForCrap").CodeModule
.DeleteLines .ProcStartLine("foobar", vbext_pk_Proc), .ProcCountLines("foobar", vbext_pk_Proc)
End With
回答by Tim Williams
You could try looking at CallByName, but I don't think it's going to do what you want (at least, not easily if you're going to want to evaluate multi-dot object/property references).
您可以尝试查看CallByName，但我认为它不会做您想做的事情（至少，如果您要评估多点对象/属性引用，这并不容易）。
回答by Harry
False evaluates to Zero. You can construct an integer variable to equate to zero and have it turn out the same way as False.
False 计算为零。您可以构造一个整型变量等于零，并使其结果与 False 相同。
