Search code examples
vbareplaceevalms-wordexecute

evaluate string as code VBA


I have a subroutine with optional parameters

Public Sub FaR_Wild_Stories_Extras(ByVal rngStory As Word.Range, _
    ByVal strSearch As String, ByVal strReplace As String, _
    Optional extra1 As String = "", Optional extra2 As String = "")
  With rngStory.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = strSearch
    .Replacement.Text = strReplace
    .Forward = True
    .MatchWildcards = True
    .MatchCase = True
    .IgnorePunct = True
    .IgnoreSpace = True
    .Format = False
    .Wrap = wdFindContinue
    If extra1 <> "" Then eval(extra1)
    If extra2 <> "" Then eval(extra2)
    .Execute Replace:=wdReplaceAll
  End With
End Sub

I want to be able to place something like ".font.size=14" or .MatchDiacritics = False for example in extra1 and evaluate it, so that, when invoking a find and replace subroutine if there are specific extra params I want to include in the find and replace as a one time thing, I don't have to create a whole separate sub for it.

Is there a way to do this? Eval() doesn't seem to exist in word VBA. Object.string isn't possible, is there some smarter way I could structure my code other than duplicating the subroutine or having a subroutine with 10s of rarely used, optional arguments?

For a more generalised case, is it possible to call a sub like this, or structure code by passing arguments cleverly to have the same effect

...
call Example , true, ".prop1=5", ".prop2=6"
...

Sub Example1(x, Optional param1 As String, Optional param2 As String)
    With Application.ExampleObject
        .property1 = "foo"
        .property2 = x
        Expand (param1)
        Expand (param2)
        .excecute
    End With
End Sub

Solution

  • Best option

    CallByName allows you to set a Property or invoke a Method with an Object named by a string.

    Sub Example(x, Optional Method1 As String ="", _
                Optional Param1 As String = "", Optional Param1Value as Variant)
    
        Dim MyObject as Object 
        Set MyObject = Application.ExampleObject
    
        'invoke a method from its name
        if method1 <> "" Then CallByName MyObject, Method1, VbMethod
        'set a property from its name and a variant representing the value to set
        if param1 <> "" Then CallByName MyObject, Param1, VbSet, Param1Value
    
        With MyObject
            .property1 = "foo"
            .property2 = x       
            .excecute
        End With
    End Sub
    

    (For info on how to handle properties with more than one value elegantly, see this thread http://www.vbforums.com/showthread.php?405366-RESOLVED-Using-CallByName-with-variable-number-of-arguments)

    Earlier solution

    Farm out rarely-used properties to external subroutine. Still could be useful in combination with the above if you don't want to clutter up the arguments of your Main Subroutine.

    Sub Example(x, Optional ExternalSub as String)
        'pull any additional rarely used properties from another sub
        Application.Run(ExternalSub)
    
        With Application.ExampleObject
            .property1 = "foo"
            .property2 = x       
            .excecute
        
    End Sub
    
    Sub External_MoreProperties
        Application.ExampleObject.property3 = "bar"
    End Sub