解决方案 1.
使用 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
使用方法:
? 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
解决方案 2。
动态添加代码。
不要这样做。这是错误的,而且需要设置 "允许访问 VB 项目" 勾选。
添加对 Microsoft Visual Basic for Applications Extensibility X.X
的引用。
创建名为 ModuleForCrap
的模块。
添加一个动态构建的子程序/函数:
ThisWorkbook.VBProject.VBComponents("ModuleForCrap").CodeModule.AddFromString _
"function foobar() as long" & vbNewLine & _
"foobar = 42" & vbNewLine & _
"end function"`
调用它:
msgbox application.run("ModuleForCrap.foobar")
删除它:
With ThisWorkbook.VBProject.VBComponents("ModuleForCrap").CodeModule
.DeleteLines .ProcStartLine("foobar", vbext_pk_Proc), .ProcCountLines("foobar", vbext_pk_Proc)
End With
ScriptControl
ActiveX 可以帮助您评估包含任何Application
成员的字符串,请查看此答案。 - omegastripes