CODEWITHSUNDEEP
vbaexcel
Ravi Kannan
Ravi Kannan

Reputation: 343

Evaluate Excel VBA boolean condition (not a formula)

I have text in a cell A1 as below

((True And False Or True) And (True And (Not True))) And (False Or True)

I need to evaluate this text and put Boolean result (True / False) in another cell B1 using VBA code.

I tried to use Evaluate function, it is not working.

When I read this cell, it always return string type with double quote enclose in both side. Hence it is treated as string and not evaluating the Boolean expression.

"((True And True Or True) And (True And (True))) And (True Or True)"

I want to write this way, but it is not working

If Range("A1").Value = True Then
    Range("B1").Value = True
Else
    Range("B1").Value = False
End If

I tried to store in Boolean variable also

Dim Result as Boolean
Result = CBool(Range("A1").Value)

as it is string, I am getting type mismatch when I tried to convert using CBool.

Upvotes: 4

Views: 3221

Answers (3)

Robin Mackenzie
Robin Mackenzie

Reputation: 19299

Further to the accepted answer for this question you can use this code:

Option Explicit

Sub Test()

    Debug.Print VBABooleanEvaluateOnTheFly("((True And False Or True) And (True And (Not True))) And (False Or True)")
    Debug.Print VBABooleanEvaluateOnTheFly("((True And True Or True) And (True And (True))) And (True Or True)")
    Debug.Print VBABooleanEvaluateOnTheFly("True")
    Debug.Print VBABooleanEvaluateOnTheFly("False")
    Debug.Print VBABooleanEvaluateOnTheFly("False Or True")

End Sub

Function VBABooleanEvaluateOnTheFly(strExpression As String) As Boolean

    Dim blnResult As Boolean
    Dim objVBComponent As Object

    Set objVBComponent = ThisWorkbook.VBProject.VBComponents.Add(1)
    With objVBComponent
        .CodeModule.AddFromString "Function foo() As Boolean: foo = " & strExpression & ": End Function"
        If Application.Run(.Name & ".foo") Then
            blnResult = True
        Else
            blnResult = False
        End If
    End With
    ThisWorkbook.VBProject.VBComponents.Remove objVBComponent

    VBABooleanEvaluateOnTheFly = blnResult

End Function

You will need to tick the Trust access to the VBA project object model checkbox in the Trust Center settings.

enter image description here

Just to note a couple of things with this technique:

  • it is slow
  • there are likely a lot of ways it will break other things
  • it is vulnerable to code injection by a malicious user e.g. they may enter something like Sheet1.Cells.Delete instead of (True And False etc)

Upvotes: 3

Kostas K.
Kostas K.

Reputation: 8508

You could try something like this, taking advantage of the Eval function in Access.

Public Function EvaluateExpression(Value As String) As Boolean
    With CreateObject("Access.Application")
        EvaluateExpression = .Eval(Value)
    End With
End Function

Public Sub T()
    Debug.Print EvaluateExpression("((True And True Or True) And (True And (True))) And (True Or True)")
End Sub

'True

Upvotes: 3

Vityata
Vityata

Reputation: 43595

This is what you can do with VBA .Evaluate:

Option Explicit

Public Sub TestMe()

    Dim cell01  As Range
    Dim cell02  As Range

    Set cell01 = Range("A1")
    Set cell02 = Range("A2")

    Range("A1") = "1+2+3+4+5"
    Range("A2") = "TRUE and FALSE"

    Debug.Print Evaluate(CStr(cell01))
    'Debug.Print CBool(cell02) - this will be an error!
    Debug.Print Evaluate(CBool("True") And CBool("False"))

    Debug.Print Evaluate("=AND(TRUE,FALSE)")
    Debug.Print Evaluate("=AND(TRUE,TRUE)")
    Debug.Print Evaluate("=OR(TRUE,TRUE)")

End Sub

If you want to parse the TRUE and FALSE thing (commented in my answer), try to build a formula out of it and to evaluate it.

E.g., TRUE AND FALSE, should be translated to =AND(TRUE,FALSE). This gets evaluated easily by VBA as it is an Excel Formula. The translation is not a trivial task, but an interesting one.

Upvotes: 1

Related Questions