如何使用变量来设置VBA（Excel）中的属性 [英] How do I use variables to set properties in VBA (Excel)

问题描述

使用以下代码：

With ActiveSheet.Shapes.AddShape(msoShapeRectangle, x, y, w, h).TextFrame
  .Parent.Line.Visible = False
  .Parent.Fill.ForeColor.RGB = RGB(r, g, b)
End With

有没有任何VBA的方式来执行或评估，就像在perl / python / $ c> .Parent.Line.Visible 可以从变量（或单元格值）中绘制，而不是硬编码？

Is there any VBA way to "execute" or "evaluate" like can be done in perl/python/... such that the text .Parent.Line.Visible can be drawn from a variable (or cell value), rather than hard coded?

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

编辑：我发现提供评论的Access 的MSDN信息，但是当我执行我的代码时aysUndefined Sub或Function，指向Eval（Excel似乎不知道此功能）。

I found MSDN information for Access that 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).

编辑2：找到在SO上的决定（否定）答案。

编辑3：好像毕竟有一个答案，因为我没有经过一般的解决方案来执行任意代码。感谢GSerg帮助使用CallByName。

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.

推荐答案

解决方案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的应用程序可扩展性的引用XX 。

创建一个名为 ModuleForCrap 的模块。

添加动态构造的子/函数：

Add a dynamically constructed sub/function:

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

这篇关于如何使用变量来设置VBA（Excel）中的属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！