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

Answer 1

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