0

In Excel, you cannot use the Application.Evaluate function to process a term such as "=true and true". It would have to be written as "=AND(TRUE,TRUE)". This presents a problem in that you cannot use standard syntax for human readable boolean terms and have Excel VBA do an evaluation. I have some fairly long strings of boolean terms which can have a dynamic relationship set, such as "(true and (true or false)) or false or false". Writing this in the Excel format would be confusing, and developing a parsing tool which can interpret any combination of terms is probably more work that it's worth here.

Is anyone aware of a method which simplifies this, or a tool or code which has already been written to bridge this gap?

CodeTrance
  • 43
  • 8
  • application.evaluate processes formulas. Is there a way to feed a string of boolean directly into VBA? – CodeTrance Apr 30 '21 at 16:53
  • 1
    [Seems similar](https://stackoverflow.com/questions/25126196/evaluate-a-string-boolean-expression-with-vba). I don't think you have many good options. – BigBen Apr 30 '21 at 16:55
  • I thought maybe you could convert AND to * and OR to + like you do with array formulas? It seems to work, but then if you have NOT in the evaluation I'm not sure how you would do it. – Tom Sharpe Apr 30 '21 at 17:29
  • Yes, pity question is closed, I would like to have had the chance of submitting the idea of (say) Evaluate(Replace(Replace(logic, "and", "*"), "or", "+")) where logic is a string containing the boolean expression even if it got shot down. – Tom Sharpe Apr 30 '21 at 20:02
  • Tom, I do have nots to account for in the formulas. I like the idea of using the new features of array formulas, but unfortunately some of our users are still on Excel 2016. So even if you could make that work, it wouldn't be my solution. – CodeTrance Apr 30 '21 at 20:06
  • 1
    @TomSharpe - I've voted to reopen, hopefully this gets traction so you can post an answer. (Though now that I reread, I guess you'd have to handle the `NOT`s). – BigBen Apr 30 '21 at 20:33
  • Thanks! I don't think there should be a problem with NOT actually, but it does seem a bit too simplistic. – Tom Sharpe Apr 30 '21 at 21:42

1 Answers1

1

After reviewing some related posts which BigBen provided the lead to, I decided to just write the solution.

I'm sure it could benefit from some efficiency optimizations, and it does not account for extended boolean logic like XOR, etc. It only handles AND, OR, and NOT. But this is more than what I could find anywhere else.

The methodology in this code is to break the full string into one-layer strings (as determined by parentheses) then process each layer of logic using an order of operations of ANDs then ORs. Next, it replaces each one-layer term with a single resulting true or false, then proceeds to the next layer until getting to the top layer of logic. The Function returns a string so that it can call itself and seamlessly feeds results into the original string evaluation.

Public Function EvaluateBooleanString(BooleanString As String) As String
Dim StrPos As Integer
Dim TermStartPos As Integer
Dim TermEndPos As Integer
Dim DepthCounter As Integer
Dim SubBool As String
Dim OrArray() As String
Dim AndArray() As String
Dim OrCounter As Integer
Dim AndCounter As Integer
Dim AndArgs As String
Dim OrArgs As String

TermStartPos = 0
TermEndPos = 0

For StrPos = 1 To Len(BooleanString)

    If Mid(BooleanString, StrPos, 1) = "(" Then
        DepthCounter = DepthCounter + 1
        If DepthCounter = 1 Then TermStartPos = StrPos
    End If

    If Mid(BooleanString, StrPos, 1) = ")" Then
        If DepthCounter = 1 Then
            TermEndPos = StrPos
            SubBool = EvaluateBooleanString(Mid(BooleanString, TermStartPos + 1, (TermEndPos - TermStartPos) - 1))
            BooleanString = Left(BooleanString, TermStartPos - 1) & SubBool & Right(BooleanString, Len(BooleanString) - TermEndPos)
            TermStartPos = 0
            TermEndPos = 0
            StrPos = 1
        End If
        DepthCounter = DepthCounter - 1
    End If
Next StrPos

BooleanString = Replace(BooleanString, "OR", "|", , , vbTextCompare)
BooleanString = Replace(BooleanString, "AND", "&", , , vbTextCompare)

OrArray = Split(BooleanString, "|")
For OrCounter = LBound(OrArray) To UBound(OrArray)
    AndArray = Split(OrArray(OrCounter), "&")
    If LBound(AndArray) < UBound(AndArray) Then
        For AndCounter = LBound(AndArray) To UBound(AndArray)
            AndArgs = AndArgs & AndArray(AndCounter) & ","
        Next AndCounter
        AndArgs = Left(AndArgs, Len(AndArgs) - 1)
    Else
        AndArgs = AndArray(0)
    End If
    AndArgs = Replace(AndArgs, "not true", "false", , , vbTextCompare)
    AndArgs = Replace(AndArgs, "not false", "true", , , vbTextCompare)
    OrArray(OrCounter) = Application.Evaluate("=AND(" & AndArgs & ")")
    AndArgs = ""
    OrArgs = OrArgs & OrArray(OrCounter) & ","
Next OrCounter
OrArgs = Left(OrArgs, Len(OrArgs) - 1)
OrArgs = Replace(OrArgs, "not true", "false", , , vbTextCompare)
OrArgs = Replace(OrArgs, "not false", "true", , , vbTextCompare)
If LBound(OrArray) < UBound(OrArray) Then
EvaluateBooleanString = Application.Evaluate("=OR(" & OrArgs & ")")
Else
EvaluateBooleanString = OrArgs
End If
End Function
Wumbolo
  • 5
  • 4
CodeTrance
  • 43
  • 8