Evaluate a string boolean expression with VBA

Question

I need to evaluate a string boolean expression in VBA such as: "1 and (0 or 0 or 1)" (This should evaluate to "1".)

I am looking for something like the Python "eval" function. Is there a simple way to do this with VBA? Thanks!

Answer 1

In addition to omegastripes' answer, please note that Script Control is 32-bit. With 64-bit becoming the norm, I would be reluctant to use any solution that is 32-bit only.

For a 64-bit Script Control alternative, please see:

Microsoft Script Control 64 bit?

But that is still an awkward solution, and I would not recommend it. It requires that the user install a new DLL, and requires the VBA programmer to call JavaScript, even though JS does have a relatively safe "eval" function. This is probably not the best approach from a security standpoint, and since Microsoft has no 64 bit version, the 32 bit version is deprecated, or at least deprecatING.

Here are some other approaches that I like better:

1. Get fancier and use RegEx:

How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

However, RegEx works much better with strings than it does with numbers. (If you want to use it with numbers, then you might try something similar to what I demonstrate below, i.e., doing the number evaluation separately.)

2. Use a VBA script that handles boolean expressions, such as this one for search strings from Ashley Harris. Harris' function works for text or for plain booleans (expressions with True and False). If you need to evaluate numbers, then you will have to try something else, or add a layer.

My simplest suggestion for adding a layer is to just enclose each short number expression in brackets and evaluate it before passing it along. Below, I show a simple example of evaluating an expression within such brackets (in this case "[[" and "]]"), so the user can write his custom expression as something like "[[(20 > (15+1) )]] AND ([[7>2]] OR [[5=4]])".

So, =BacketEval("[[(20 > (15+1) )]] AND ([[7>2]] OR [[5=4]])") would just simplify that to "(True) AND (True OR False)", which you can plug into the boolean parser. A similar approach would work for other parsers.

Function BracketEval(BoolExp As String, Optional OpenBracket = "[[", Optional CloseBracket = "]]") As String

    Dim BoolStartPos As Integer
    Dim BoolEndPos As Integer
    Dim BoolToEval As String
    Dim BoolEvaled As String

    BoolStartPos = InStr(BoolExp, OpenBracket)
    BracketEval = BoolExp

    Do While BoolStartPos <> 0

        BoolEndPos = InStr(BoolStartPos + 1, BracketEval, CloseBracket)
        BoolToEval = Mid(BracketEval, BoolStartPos + Len(OpenBracket), BoolEndPos - BoolStartPos - Len(CloseBracket))
        BoolEvaled = CStr(Application.Evaluate(BoolToEval))

        BracketEval = Left(BracketEval, BoolStartPos - 1) _
         & BoolEvaled _
         & Right(BracketEval, Len(BracketEval) - BoolEndPos - Len(CloseBracket) + 1)

        BoolStartPos = InStr(BoolStartPos + Len(OpenBracket) - 1, BracketEval, OpenBracket)

    Loop

End Function

A full parser, or even a parser that can add brackets to a VBA-style expression, is beyond where I want to go with what is already a lengthy (although not very appreciated!) answer, and that gets too complicated, although I have dabbled with those.

Harris' boolean parser is about the best I have seen for VBA, and very clever, but note that it can freeze if it doesn't like the input. At the page for his software, I added some comments about how to avoid this: basically, add a counter and if it runs too high, then exit.

Moving on, there are some unusual solutions for boolean parsing presented here:

Evaluate Excel VBA boolean condition (not a formula)

The best of those is:

3. Evaluate an expression in VBA via an Access object, if Access is installed. This is a nifty and simple solution if you need to evaluate only a few expressions and know that the user will have Access.

You could also:

4. Create an Internet Explorer browser object and evaluate the boolean via JavaScript. Again, not super-elegant, perhaps, but the plus here is that most Windows computers do have IE installed. (If you happen to run into automation problems, try creating an InternetExplorerMedium object instead of a plain old InternetExplorer.)

And below could possibly be the fastest and most elegant answer for some, especially if you want to churn through a bunch of these using a faster engine:

5. You could call an outside script from another scripting engine, such as Python, AutoHotkey, R, PHP, or something along those lines. PowerShell could make sense except that adjusting its security settings might be a nuisance for some users. The fastest (computationally) might be to call the outside script, then directly access the Excel spreadsheet object from within that script (to get the expressions), do all the necessary evaluations, and then close the script.

From there, the available choices dwindle in quality.

6. Also as referenced in the link above, you could evaluate the expression via brute-force VBA code injection, if you are willing to lower Excel's security settings. This is almost certainly a terrible idea, but if you only use one spreadsheet, never use the internet, don't share code with anyone, and/or are a born renegade who unlike Judge Reinhold refuses to do it "by the book," then it might well be the fastest. If you use this approach (you bad mammajamma), I would probably even be prepared to disable the heuristics scanning in your antivirus software! (And if that doesn't convince you that it's a bad idea, I guess that nothing will.)

If you only have a few expressions to evaluate, and they are static, then you could just convert them to the standard Excel spreadsheet language. But if you want to say accept an expression from the user, then it is very clunky to require them to use the awkward parenthetical ANDs, ORs, and NOTs that Excel uses in its regular functional language. VBA's own evaluation of expressions is a lot more normal-looking, but VBA cannot easily pass code to itself as a string (which usually is a good thing).

Answer 2

You can invoke .Eval() method of ScriptControl ActiveX to avoid any string pre-processing:

Sub TestEvaluateExpression()
    Dim varResult As Variant
    Dim strEvalContent As String
    strEvalContent = "1 and (0 or 0 or 1)"
    varResult = Eval(strEvalContent)
    MsgBox varResult
End Sub

Function Eval(strEvalContent As String) As Variant
    With CreateObject("ScriptControl")
        .Language = "VBScript"
        Eval = .Eval(strEvalContent)
    End With
End Function