evaluate string as code VBA

Question

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

I believe it should be Evaluate(), but I'm completely uncertain whether or not this would work — Victor Moraes
– Victor Moraes, Commented Jan 11, 2017 at 13:25
evaluate() is an excel specific function that works on worksheet formulae rather than vba code (e.g. evaluate("sum(a1:d4))" ) — Some_Guy
– Some_Guy, Commented Jan 11, 2017 at 13:46
Even in excel eval/Evaluateis just a mechanism to invoke a function. What you are trying to to do in your example is Set properties to an object. As far as I know, VBA doesn't supports such feature. To tweak a property you will first need an instance of the object and then reflect it to replace/ add the property values. .Net / Java support reflection, VBA doesn't. — cyboashu
– cyboashu, Commented Jan 11, 2017 at 15:07
So what do you recommend I do? I could create a new sub for each combination of properties I end up using (involves a lot of code duplication)? I could create a behemoth of a sub with a bunch of optional arguments for every property I might care to use in the future (better, but still ugly) Or is there a smarter way that achieves a similar purpose, that is to say invoke a subroutine which includes a method within it defined by multiple parameters, without specifying all of these potentially used parameters in the arguments of the subroutine? — Some_Guy
– Some_Guy, Commented Jan 11, 2017 at 15:19
@cyboashu turns out this is in fact possible, see my answer below. — Some_Guy
– Some_Guy, Commented Jan 19, 2017 at 16:35

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 17, 2017 at 0:22

Some_Guy

5038 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Coleman Over a year ago

Good answer, but perhaps "It's not possible to evaluate a string as code in vba" should read "There is no straight-forward way to evaluate a string as code in vba". VBA is Turing complete. You could write your own parser if worse comes to worse. Also -- the VBA editor is scriptable, and you can use it to dynamically generate code. But, there is nothing which is as immediately usable as e.g. Python's exec() function.

Sancarn Over a year ago

@JohnColeman Exactly! And write our own parser, compiler and evaluator we did! :D

Sancarn · Accepted Answer · 2021-11-10 14:56:24Z

A bit late to the party but in this case I would use stdLambda.

stdLambda is an external library, developed (mostly by myself) to bring a full parser, compiler and evaluator to VBA. We parse VBA-like code, convert it into byte code, and then evaluate it when needed, all within the VBA runtime!

Usage should be very straight forward for this use case:

Public Sub FaR_Wild_Stories_Extras(ByVal rngStory As Word.Range, _
ByVal strSearch As String, ByVal strReplace As String, _
Optional ByVal extraFilters As stdICallable)

    Dim f as Find
    set f = rngStory.Find
    With f
       'Default search params:
       .ClearFormatting
       .Replacement.ClearFormatting
       .Text = strSearch
       .Replacement.Text = strReplace
       .Forward = True
       .MatchWildcards = True
       .MatchCase = True
       .IgnorePunct = True
       .IgnoreSpace = True
       .Format = False
       .Wrap = wdFindContinue
       Call extraFilters.Run(f)
       .Execute Replace:= wdReplaceAll
    End With
 End Sub
 Sub Main()
    Call FaR_Wild_Stories_Extras(Doc.Contents, "Hello","Bye", stdLambda.CreateMultiline( _ 
        "$1.Font.Size = 10", _ 
        "$1.MatchDiacritics = False" _ 
    ))
 End Sub

Collectives™ on Stack Overflow

evaluate string as code VBA

2 Answers 2

Best option

Earlier solution

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Best option

Earlier solution

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related