How to pass a string with parameters for evaluation in Excel?

Question

In VBA, is there a way to create a function which receives one of it's parameters as a string with a condition to be evaluated by an IF block?

This should give an idea of what I am looking for, but I now it's not that simple:

Function StringAsCondition(a As String) As Boolean

    Dim result As Boolean
    Dim b As Long
    Dim c As Long
    b = 4
    c = 2
    If a Then
        result = True
    End If
    StringAsCondition = result

End Function

Sub Test()

    Dim a As String
    a = "b >= c"
    Dim functionresult As Boolean
    functionresult = StringAsCondition(a)
    MsgBox functionresult

End Sub

I'm afraid that Application.Evaluate doesn't work for the case in discussion... — FaneDuru, Jan 04 '20 at 21:05
If you work on a 32 bit SO it is a relatively simple solution (using MSScriptControl). Otherwise, there is an a little more complicated one, involving creation of ActiveX objects like ScriptControl (in VBA) and use the VBSript ability to deal with such an Evaluation. Or VBSCript object itself... I did not built it. I found it here and I have it in my test modules collection. Unfortunately, I do not remember who provided it. — FaneDuru, Jan 04 '20 at 21:13
@BigBen `Evaluate` is intended to evaluate formulas and names, but not VBA expressions. Even if simple expression conforms formula syntax, additional check is needed to restrict single numerics from evaluating, since they may return not what is expected. Here is the example to reproduce that. In blank workbook place a button on a worksheet (Developer tab - Insert - Form Controls - Button). Go to Immediate window, type `? TypeName(Evaluate("1")). — omegastripes, Jan 07 '20 at 12:08
@FaneDuru you can use ScriptControl in 64 bit Office, check [this question](https://stackoverflow.com/q/9725882/2165759). Also check [this solution](https://stackoverflow.com/a/51378286/2165759). Another thing, the variables passed to evaluation within string won't be resolved, so variables transfer should be implemented some way. — omegastripes, Jan 07 '20 at 13:06
@omegastripes: At that (first) solution I was referring when wrote "involving creation of ActiveX objects like ScriptControl (in VBA)"... The second solution can be replaced by simply using `MsgBox Application.Evaluate("1+2+3^4")` — FaneDuru, Jan 07 '20 at 13:21
Sorry, please don’t expand the scope of an existing question, whatever someone may have asked in comments. If you have a follow-up problem, post a new question. — Martijn Pieters, Jan 14 '20 at 07:55

Vityata · Accepted Answer · 2020-01-05T15:54:43.537

The evaluation of the string is actually a rather easy task in the "fancy" programming languages (all but VBA). There, you would simply use some type of string formatting, replace and evaluate.

In VBA, you may build your own string formatting (credits Is there an equivalent of printf or String.Format in Excel) and use it:

Sub Test()

    Dim condition As String
    Dim b As Long, c As Long
    b = 4
    c = 2

    condition = "{0} >= {1}"
    Debug.Print Application.Evaluate(StringFormat(condition, b, c))

End Sub

Public Function StringFormat(mask As String, ParamArray tokens()) As String

    Dim i As Long
    For i = LBound(tokens) To UBound(tokens)
        mask = Replace$(mask, "{" & i & "}", tokens(i))
    Next
    StringFormat = mask

End Function

score 0 · Answer 2 · answered Jan 04 '20 at 21:41

My answer just tries to present the real solution (of @Vitiata) in a way to directly answer the request:

Function StringAsCondition(cond As String) As Boolean
    Dim result As Boolean
    Dim b As Long
    Dim c As Long

    b = 4
    c = 2
    result = CBool(Application.Evaluate(StringFormat(cond, b, c)))

    StringAsCondition = result
End Function
Sub TestEV()
    Dim a As String
    Dim functionresult As Boolean
    a = "{0} >= {1}" 'instead of a = "b >= c"

    functionresult = StringAsCondition(a)

    MsgBox functionresult
End Sub
Private Function StringFormat(mask As String, ParamArray tokens()) As String
    Dim i As Long
    For i = 0 To UBound(tokens)
        mask = Replace$(mask, "{" & i & "}", tokens(i))
    Next
    StringFormat = mask
End Function

score 0 · Answer 3 · answered Jan 14 '20 at 11:13

Allow placeholding variables in any order

Just in addition to @Vityata 's clever solution, a similar solution with a mask definition closer to common mathematical formulae with variables in any order.

So an expression (condition) of e.g.

y >= x*c would be transformed easily to a mask string like
expression = "{y} >= {x}*{c}"

Sub TestStringFormat()
' Purp: evaluate mask with place holding variables in any order
    Dim expression As String
    expression = "{y} >= {x}*{c}"               ' "17 >= 4*2"  ~~> True

    Dim myVars As String: myVars = "c,x,y"      '     "c,x,y", c  x  y
    Debug.Print Application.Evaluate(StrFrm(expression, myVars, 2, 4, 17))

End Sub

Public Function StrFrm(mask As String, myVars As String, ParamArray tokens()) As String
' Purpose: mask with place holding variables in any order  ' e.g.      y >= x*c
' Note: modified by T.M.; credit to https://stackoverflow.com/users/246342/alex-k
' Site: https://stackoverflow.com/questions/17233701/is-there-an-equivalent-of-printf-or-string-format-in-excel
    Dim vars: vars = Split(myVars, ",") ' variables c,x,y
    Dim i As Long
    For i = LBound(vars) To UBound(vars)
        mask = Replace$(mask, "{" & vars(i) & "}", tokens(i))
    Next
    StrFrm = mask
    Debug.Print StrFrm
End Function

How to pass a string with parameters for evaluation in Excel?

3 Answers3