How do I use variables to set properties in VBA (Excel)

by

Solution 1.

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

Dynamically add code.
Don’t do this. It’s wrong and it requires having that “Allow access to VB project” tick set.

Add a reference to Microsoft Visual Basic for Applications Extensibility X.X.

Create a module called 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

More Related Contents:

  1. delete Autofilter Field resullt using the header Name
  2. Somehow it’s not copying [closed]
  3. VBA macro that search for file in multiple subfolders
  4. So, I have 6 “master” files to then divide into 40 separate files
  5. Getting ScriptControl to work with Excel 2010 x64
  6. Application.Match gives type mismatch
  7. Count and Highlight keywords within phrases
  8. Protecting cells in Excel but allow these to be modified by VBA script
  9. Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]
  10. Detect merged cells in VBA Excel with MergeArea
  11. Using VBA code, how to export Excel worksheets as image in Excel 2003?
  12. Parse HTML content in VBA
  13. How to add a DocumentProperty to CustomDocumentProperties in Excel?
  14. How do I name a spreadsheet automatically, by referencing two cells?
  15. Excel VBA Delete Rows
  16. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  17. Should I turn .CutCopyMode back on before exiting my sub procedure?
  18. VBA: How to delete filtered rows in Excel?
  19. How to use VBA SaveAs without closing calling workbook?
  20. How to continue the code on the next line in VBA
  21. Excel VBA Userform – Execute Sub when something changes
  22. Getting Excel to refresh data on sheet from within VBA
  23. Finding first blank row, then writing to it
  24. Calling regasm without administrative rights for COM interop in Excel VBA
  25. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  26. How do I activate a specific workbook and a specific sheet?
  27. Start VBA macro when editing a cell
  28. VBA Run-time error 438 appears when “paste” runs
  29. If Not function proceeds when value *is* the one specified
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment