Logical Operators in VBA

by Sola Bode
in VBA Coding Constructs
February 25, 2021

VBA logical operators - symbols that instruct the compiler to perform logical operations like conjunction and disjunction - are explored herein.

9 minutes read

VBA Operators

VBA operators are special tokens that instruct the compiler to “operate” on values or program identifiers. They are also referred to as program symbols or code elements. Also, the program identifiers they operate on (e.g., addition) must be capable of holding values.

You may have heard of the term operand. These are the values (i.e., literals) or identifiers (e.g., variables and constants) that operators act on. Based on their number of operands, operators are either unary or binary.

Unary VBA operators are operators that only act on a single operand at a time. Binary VBA operators are those that only act on two operands at a time.

VBA supports the following categories of operators:

This article takes an in-depth look at the VBA logical operators.

VBA Operator Precedence

Often, an operation involves more than one operator, sometimes of different categories. So, it is worthwhile paying special attention to the order of precedence of the VBA operators. Otherwise, VBA’s default operator precedence applies.

The default precedence is as follows: arithmetic first, then concatenation, followed by comparison, then logical, and finally assignment operators.

Moreover, there is a pre-set precedence for operators within each category. How do you override this default order and remove any ambiguity? Easy, set the preferred precedence with open and close parentheses, ( ).

Logical Operators

These operators perform logical negation, conjunction, disjunction, exclusion, equivalence, and implication operations. They do so on either one operand (unary operation) or a pair of operands (binary operation).

The table below summarizes the various such operators supported by VBA.

Operator	Description	Return Data Type	Order
Not	*Logical Negation* (unary) operator returns the reverse logical result of an expression.	*Boolean* – *True* (i.e., *≠ 0) if the expression is False* (i.e., *= 0); Boolean* – *False* if the expression is *True; Null* if the expression is *Null*.	1
And	*Logical Conjunction* (binary) operator performs a logical conjunction on two expressions.	*Boolean* – *True* if both expressions are *True; Boolean* – *False* if either expression is *False; Null* if either expression is *Null*.	2
Or	*Logical Disjunction* (binary) operator performs a logical disjunction on two expressions.	*Boolean* – *True* if either expression is *True; Boolean* – *False* if both expressions are *False; Null* if both expressions are *Null, or one expression is Null* and the other is *False*.	3
Xor	*Logical Exclusion* (binary) operator performs a logical exclusion on two expressions.	*Boolean* – *True* if one, and only one, expression is *True; Boolean* – *False* if both expressions are *True* or *False; Null* if either expression is *Null*.	4
Eqv	*Logical Equivalence* (binary) operator performs a logical equivalence on two expressions.	*Boolean* – *True* if both expressions are *True* or *False; Boolean* – *False* if one expression is *True* and the other is *False, and vice versa; Null* if either expression is *Null*.	5
Imp	*Logical Implication* (binary) operator performs a logical implication on two expressions.	*Boolean* – *True* if: Both expressions are *True* or *False; First expression is False, and the second expression is True* or *Null; First expression is Null, and the second expression is True. Boolean* – *False* if, and only if, the first expression is *True, and the second expression is False; Null* if either expression is *Null*.	6

As shown in the table, these operators all have different precedence. So, when several of them appear in an expression, evaluation proceeds in the order of appearance from left to right.

Also, enclosing a part of an expression in parentheses causes it to be evaluated ahead of other parts.

Logical Operators: Negation (Not)

The sample code below demonstrates the logical negation (Not) operator’s usage.

Sub LogicalOperator_Not_Example01()
    
    Dim lVar_Var1 As Variant, lVar_Var2 As Variant, lVar_Var3 As Variant
    
    'negate a True, False, and Null expressions
    lVar_Var1 = True:   lVar_Var2 = False:  lVar_Var3 = Null
    Debug.Print "Not "; lVar_Var1; " = "; Not lVar_Var1
    Debug.Print "Not "; lVar_Var2; " = "; Not lVar_Var2
    Debug.Print "Not "; lVar_Var3; " = "; Not lVar_Var3
    
    'negate equality operator
    Debug.Print "Not("; lVar_Var1; " = "; lVar_Var2; ") = Not "; _
        (lVar_Var1 = lVar_Var2); " = "; Not (lVar_Var1 = lVar_Var2)
    'negate inequality operator
    lVar_Var1 = 23: lVar_Var2 = 36
    Debug.Print "Not("; lVar_Var1; " <> "; lVar_Var2; ") = Not "; _
        (lVar_Var1 <> lVar_Var2); " = "; Not (lVar_Var1 <> lVar_Var2)
        
    'negate greater than operator
    Debug.Print "Not("; lVar_Var1; " > "; lVar_Var2; ") = Not "; _
        (lVar_Var1 > lVar_Var2); " = "; Not (lVar_Var1 > lVar_Var2)
    'negate less than operator
    Debug.Print "Not("; lVar_Var1; " < "; lVar_Var2; ") = Not "; _
        (lVar_Var1 < lVar_Var2); " = "; Not (lVar_Var1 < lVar_Var2)
    
    'negate greater than or equal to operator
    Debug.Print "Not("; lVar_Var2; " >= "; lVar_Var1; ") = Not "; _
        (lVar_Var2 >= lVar_Var1); " = "; Not (lVar_Var2 >= lVar_Var1)
    'negate less than or equal to operator
    Debug.Print "Not("; lVar_Var1; " <= "; lVar_Var2; ") = Not "; _
        (lVar_Var1 <= lVar_Var2); " = "; Not (lVar_Var1 <= lVar_Var2)
    
End Sub

Sub LogicalOperator_Not_Example01()

Dim lVar_Var1 As Variant, lVar_Var2 As Variant, lVar_Var3 As Variant

'negate a True, False, and Null expressions

lVar_Var1 = True: lVar_Var2 = False: lVar_Var3 = Null

Debug.Print "Not "; lVar_Var1; " = "; Not lVar_Var1

Debug.Print "Not "; lVar_Var2; " = "; Not lVar_Var2

Debug.Print "Not "; lVar_Var3; " = "; Not lVar_Var3

'negate equality operator

Debug.Print "Not("; lVar_Var1; " = "; lVar_Var2; ") = Not "; _

(lVar_Var1 = lVar_Var2); " = "; Not (lVar_Var1 = lVar_Var2)

'negate inequality operator

lVar_Var1 = 23: lVar_Var2 = 36

Debug.Print "Not("; lVar_Var1; " <> "; lVar_Var2; ") = Not "; _

(lVar_Var1 <> lVar_Var2); " = "; Not (lVar_Var1 <> lVar_Var2)

'negate greater than operator

Debug.Print "Not("; lVar_Var1; " > "; lVar_Var2; ") = Not "; _

(lVar_Var1 > lVar_Var2); " = "; Not (lVar_Var1 > lVar_Var2)

'negate less than operator

Debug.Print "Not("; lVar_Var1; " < "; lVar_Var2; ") = Not "; _

(lVar_Var1 < lVar_Var2); " = "; Not (lVar_Var1 < lVar_Var2)

'negate greater than or equal to operator

Debug.Print "Not("; lVar_Var2; " >= "; lVar_Var1; ") = Not "; _

(lVar_Var2 >= lVar_Var1); " = "; Not (lVar_Var2 >= lVar_Var1)

'negate less than or equal to operator

Debug.Print "Not("; lVar_Var1; " <= "; lVar_Var2; ") = Not "; _

(lVar_Var1 <= lVar_Var2); " = "; Not (lVar_Var1 <= lVar_Var2)

End Sub

Sample code illustrating the Not operator’s usage. — Sample code illustrating the ***Not*** logical operator’s usage.

Logical Operators: Conjunction (And)

The sample code below illustrates the logical conjunction (And) operator’s usage.

Sub LogicalOperator_And_Example01()
    
    Dim lVar_Var1 As Variant, lVar_Var2 As Variant, lVar_Var3 As Variant
    
    'conjunct True, False, and Null expressions
    lVar_Var1 = True:   lVar_Var2 = False:  lVar_Var3 = Null
    Debug.Print lVar_Var1; " And "; lVar_Var1; " = "; _
        (lVar_Var1 And lVar_Var1)
    Debug.Print lVar_Var1; " And "; lVar_Var2; " = "; _
        (lVar_Var1 And lVar_Var2)
    Debug.Print lVar_Var2; " And "; lVar_Var2; " = "; _
        (lVar_Var2 And lVar_Var2)
    Debug.Print lVar_Var1; " And "; lVar_Var3; " = "; _
        (lVar_Var1 And lVar_Var3)
    Debug.Print lVar_Var2; " And "; lVar_Var3; " = "; _
        (lVar_Var2 And lVar_Var3)
    Debug.Print lVar_Var3; " And "; lVar_Var3; " = "; _
        (lVar_Var3 And lVar_Var3)
    
    'numeric example
    lVar_Var1 = 2: lVar_Var2 = 3: lVar_Var3 = 4
    Debug.Print vbCrLf; lVar_Var1 < lVar_Var2 And lVar_Var1 > lVar_Var2
    
    'breaking down the inner working of the example operation above
    Debug.Print lVar_Var1; " < "; lVar_Var2; " And "; lVar_Var1; " > "; _
        lVar_Var2; " = "; lVar_Var1 < lVar_Var2; " And "; lVar_Var1 > _
        lVar_Var2; " = "; lVar_Var1 < lVar_Var2 And lVar_Var1 > lVar_Var2
    
End Sub

Sub LogicalOperator_And_Example01()

Dim lVar_Var1 As Variant, lVar_Var2 As Variant, lVar_Var3 As Variant

'conjunct True, False, and Null expressions

lVar_Var1 = True: lVar_Var2 = False: lVar_Var3 = Null

Debug.Print lVar_Var1; " And "; lVar_Var1; " = "; _

(lVar_Var1 And lVar_Var1)

Debug.Print lVar_Var1; " And "; lVar_Var2; " = "; _

(lVar_Var1 And lVar_Var2)

Debug.Print lVar_Var2; " And "; lVar_Var2; " = "; _

(lVar_Var2 And lVar_Var2)

Debug.Print lVar_Var1; " And "; lVar_Var3; " = "; _

(lVar_Var1 And lVar_Var3)

Debug.Print lVar_Var2; " And "; lVar_Var3; " = "; _

(lVar_Var2 And lVar_Var3)

Debug.Print lVar_Var3; " And "; lVar_Var3; " = "; _

(lVar_Var3 And lVar_Var3)

'numeric example

lVar_Var1 = 2: lVar_Var2 = 3: lVar_Var3 = 4

Debug.Print vbCrLf; lVar_Var1 < lVar_Var2 And lVar_Var1 > lVar_Var2

'breaking down the inner working of the example operation above

Debug.Print lVar_Var1; " < "; lVar_Var2; " And "; lVar_Var1; " > "; _

lVar_Var2; " = "; lVar_Var1 < lVar_Var2; " And "; lVar_Var1 > _

lVar_Var2; " = "; lVar_Var1 < lVar_Var2 And lVar_Var1 > lVar_Var2

End Sub

Sample code illustrating the And Logical Operator’s usage. — Sample code illustrating the ***And*** logical operator’s usage.

Logical Operators: Disjunction (Or)

The sample code below shows the logical disjunction (Or) operator’s usage.

Sub LogicalOperator_Or_Example01()
    
    Dim lVar_Var1 As Variant, lVar_Var2 As Variant, lVar_Var3 As Variant
    
    'disjunct True, False, and Null expressions
    lVar_Var1 = True:   lVar_Var2 = False:  lVar_Var3 = Null
    Debug.Print lVar_Var1; " Or "; lVar_Var1; " = "; _
        (lVar_Var1 Or lVar_Var1)
    Debug.Print lVar_Var1; " Or "; lVar_Var2; " = "; _
        (lVar_Var1 Or lVar_Var2)
    Debug.Print lVar_Var2; " Or "; lVar_Var2; " = "; _
        (lVar_Var2 Or lVar_Var2)
    Debug.Print lVar_Var1; " Or "; lVar_Var3; " = "; _
        (lVar_Var1 Or lVar_Var3)
    Debug.Print lVar_Var2; " Or "; lVar_Var3; " = "; _
        (lVar_Var2 Or lVar_Var3)
    Debug.Print lVar_Var3; " Or "; lVar_Var3; " = "; _
        (lVar_Var3 Or lVar_Var3)
    
    'numeric example
    lVar_Var1 = 2: lVar_Var2 = 3: lVar_Var3 = 4
    Debug.Print vbCrLf; lVar_Var1 < lVar_Var2 Or lVar_Var1 > lVar_Var2
    
    'breaking down the inner working of the example operation above
    Debug.Print lVar_Var1; " < "; lVar_Var2; " Or "; lVar_Var1; " > "; _
        lVar_Var2; " = "; lVar_Var1 < lVar_Var2; " Or "; lVar_Var1 > _
        lVar_Var2; " = "; lVar_Var1 < lVar_Var2 Or lVar_Var1 > lVar_Var2
    
End Sub

Sub LogicalOperator_Or_Example01()

Dim lVar_Var1 As Variant, lVar_Var2 As Variant, lVar_Var3 As Variant

'disjunct True, False, and Null expressions

lVar_Var1 = True: lVar_Var2 = False: lVar_Var3 = Null

Debug.Print lVar_Var1; " Or "; lVar_Var1; " = "; _

(lVar_Var1 Or lVar_Var1)

Debug.Print lVar_Var1; " Or "; lVar_Var2; " = "; _

(lVar_Var1 Or lVar_Var2)

Debug.Print lVar_Var2; " Or "; lVar_Var2; " = "; _

(lVar_Var2 Or lVar_Var2)

Debug.Print lVar_Var1; " Or "; lVar_Var3; " = "; _

(lVar_Var1 Or lVar_Var3)

Debug.Print lVar_Var2; " Or "; lVar_Var3; " = "; _

(lVar_Var2 Or lVar_Var3)

Debug.Print lVar_Var3; " Or "; lVar_Var3; " = "; _

(lVar_Var3 Or lVar_Var3)

'numeric example

lVar_Var1 = 2: lVar_Var2 = 3: lVar_Var3 = 4

Debug.Print vbCrLf; lVar_Var1 < lVar_Var2 Or lVar_Var1 > lVar_Var2

'breaking down the inner working of the example operation above

Debug.Print lVar_Var1; " < "; lVar_Var2; " Or "; lVar_Var1; " > "; _

lVar_Var2; " = "; lVar_Var1 < lVar_Var2; " Or "; lVar_Var1 > _

lVar_Var2; " = "; lVar_Var1 < lVar_Var2 Or lVar_Var1 > lVar_Var2

End Sub

Sample code illustrating the Or Logical Operator’s usage. — Sample code illustrating the Or logical operator’s usage.

Logical Operators: Exclusion (Xor)

The sample code below illustrates the logical exclusion (Xor) operator’s usage.

Sub LogicalOperator_Xor_Example01()
    
    Dim lVar_Var1 As Variant, lVar_Var2 As Variant, lVar_Var3 As Variant
    
    'exclusion: True, False, and Null expressions
    lVar_Var1 = True:   lVar_Var2 = False:  lVar_Var3 = Null
    Debug.Print lVar_Var1; " Xor "; lVar_Var1; " = "; _
        (lVar_Var1 Xor lVar_Var1)
    Debug.Print lVar_Var1; " Xor "; lVar_Var2; " = "; _
        (lVar_Var1 Xor lVar_Var2)
    Debug.Print lVar_Var2; " Xor "; lVar_Var2; " = "; _
        (lVar_Var2 Xor lVar_Var2)
    Debug.Print lVar_Var1; " Xor "; lVar_Var3; " = "; _
        (lVar_Var1 Xor lVar_Var3)
    Debug.Print lVar_Var2; " Xor "; lVar_Var3; " = "; _
        (lVar_Var2 Xor lVar_Var3)
    Debug.Print lVar_Var3; " Xor "; lVar_Var3; " = "; _
        (lVar_Var3 Xor lVar_Var3)
    
    'numeric example
    lVar_Var1 = 2: lVar_Var2 = 3: lVar_Var3 = 4
    Debug.Print vbCrLf; lVar_Var1 < lVar_Var2 Xor lVar_Var1 > lVar_Var2
    
    'breaking down the inner working of the example operation above
    Debug.Print lVar_Var1; " < "; lVar_Var2; " Xor "; lVar_Var1; " > "; _
        lVar_Var2; " = "; lVar_Var1 < lVar_Var2; " Xor "; lVar_Var1 > _
        lVar_Var2; " = "; lVar_Var1 < lVar_Var2 Xor lVar_Var1 > lVar_Var2
    
End Sub

Sub LogicalOperator_Xor_Example01()

Dim lVar_Var1 As Variant, lVar_Var2 As Variant, lVar_Var3 As Variant

'exclusion: True, False, and Null expressions

lVar_Var1 = True: lVar_Var2 = False: lVar_Var3 = Null

Debug.Print lVar_Var1; " Xor "; lVar_Var1; " = "; _

(lVar_Var1 Xor lVar_Var1)

Debug.Print lVar_Var1; " Xor "; lVar_Var2; " = "; _

(lVar_Var1 Xor lVar_Var2)

Debug.Print lVar_Var2; " Xor "; lVar_Var2; " = "; _

(lVar_Var2 Xor lVar_Var2)

Debug.Print lVar_Var1; " Xor "; lVar_Var3; " = "; _

(lVar_Var1 Xor lVar_Var3)

Debug.Print lVar_Var2; " Xor "; lVar_Var3; " = "; _

(lVar_Var2 Xor lVar_Var3)

Debug.Print lVar_Var3; " Xor "; lVar_Var3; " = "; _

(lVar_Var3 Xor lVar_Var3)

'numeric example

lVar_Var1 = 2: lVar_Var2 = 3: lVar_Var3 = 4

Debug.Print vbCrLf; lVar_Var1 < lVar_Var2 Xor lVar_Var1 > lVar_Var2

'breaking down the inner working of the example operation above

Debug.Print lVar_Var1; " < "; lVar_Var2; " Xor "; lVar_Var1; " > "; _

lVar_Var2; " = "; lVar_Var1 < lVar_Var2; " Xor "; lVar_Var1 > _

lVar_Var2; " = "; lVar_Var1 < lVar_Var2 Xor lVar_Var1 > lVar_Var2

End Sub

Sample code illustrating the Xor operator’s usage. — Sample code illustrating the ***Xor*** logical operator’s usage.

Logical Operators: Equivalence (Eqv)

The sample code below demonstrates the logical exclusion (Eqv) operator’s usage.

Sub LogicalOperator_Eqv_Example01()
    
    Dim lVar_Var1 As Variant, lVar_Var2 As Variant, lVar_Var3 As Variant
    
    'equivalence: True, False, and Null expressions
    lVar_Var1 = True:   lVar_Var2 = False:  lVar_Var3 = Null
    Debug.Print lVar_Var1; " Eqv "; lVar_Var1; " = "; _
        (lVar_Var1 Eqv lVar_Var1)
    Debug.Print lVar_Var1; " Eqv "; lVar_Var2; " = "; _
        (lVar_Var1 Eqv lVar_Var2)
    Debug.Print lVar_Var2; " Eqv "; lVar_Var2; " = "; _
        (lVar_Var2 Eqv lVar_Var2)
    Debug.Print lVar_Var1; " Eqv "; lVar_Var3; " = "; _
        (lVar_Var1 Eqv lVar_Var3)
    Debug.Print lVar_Var2; " Eqv "; lVar_Var3; " = "; _
        (lVar_Var2 Eqv lVar_Var3)
    Debug.Print lVar_Var3; " Eqv "; lVar_Var3; " = "; _
        (lVar_Var3 Eqv lVar_Var3)
    
    'numeric example
    lVar_Var1 = 2: lVar_Var2 = 3: lVar_Var3 = 4
    Debug.Print vbCrLf; lVar_Var1 < lVar_Var2 Eqv lVar_Var1 > lVar_Var2
    
    'breaking down the inner working of the example operation above
    Debug.Print lVar_Var1; " < "; lVar_Var2; " Eqv "; lVar_Var1; " > "; _
        lVar_Var2; " = "; lVar_Var1 < lVar_Var2; " Eqv "; lVar_Var1 > _
        lVar_Var2; " = "; lVar_Var1 < lVar_Var2 Eqv lVar_Var1 > lVar_Var2
    
End Sub

Sub LogicalOperator_Eqv_Example01()

Dim lVar_Var1 As Variant, lVar_Var2 As Variant, lVar_Var3 As Variant

'equivalence: True, False, and Null expressions

lVar_Var1 = True: lVar_Var2 = False: lVar_Var3 = Null

Debug.Print lVar_Var1; " Eqv "; lVar_Var1; " = "; _

(lVar_Var1 Eqv lVar_Var1)

Debug.Print lVar_Var1; " Eqv "; lVar_Var2; " = "; _

(lVar_Var1 Eqv lVar_Var2)

Debug.Print lVar_Var2; " Eqv "; lVar_Var2; " = "; _

(lVar_Var2 Eqv lVar_Var2)

Debug.Print lVar_Var1; " Eqv "; lVar_Var3; " = "; _

(lVar_Var1 Eqv lVar_Var3)

Debug.Print lVar_Var2; " Eqv "; lVar_Var3; " = "; _

(lVar_Var2 Eqv lVar_Var3)

Debug.Print lVar_Var3; " Eqv "; lVar_Var3; " = "; _

(lVar_Var3 Eqv lVar_Var3)

'numeric example

lVar_Var1 = 2: lVar_Var2 = 3: lVar_Var3 = 4

Debug.Print vbCrLf; lVar_Var1 < lVar_Var2 Eqv lVar_Var1 > lVar_Var2

'breaking down the inner working of the example operation above

Debug.Print lVar_Var1; " < "; lVar_Var2; " Eqv "; lVar_Var1; " > "; _

lVar_Var2; " = "; lVar_Var1 < lVar_Var2; " Eqv "; lVar_Var1 > _

lVar_Var2; " = "; lVar_Var1 < lVar_Var2 Eqv lVar_Var1 > lVar_Var2

End Sub

Sample code illustrating the Eqv Logical Operator’s usage. — Sample code illustrating the ***Eqv*** logical operator’s usage.

Logical Operator: Implication (Imp)

The sample code below illustrates the logical exclusion (Imp) operator’s usage.

Sub LogicalOperator_Imp_Example01()
    
    Dim lVar_Var1 As Variant, lVar_Var2 As Variant, lVar_Var3 As Variant
    
    'implication: True, False, and Null expressions
    lVar_Var1 = True:   lVar_Var2 = False:  lVar_Var3 = Null
    Debug.Print lVar_Var1; " Imp "; lVar_Var1; " = "; _
        (lVar_Var1 Imp lVar_Var1)
    Debug.Print lVar_Var1; " Imp "; lVar_Var2; " = "; _
        (lVar_Var1 Imp lVar_Var2)
    Debug.Print lVar_Var2; " Imp "; lVar_Var2; " = "; _
        (lVar_Var2 Imp lVar_Var2)
    Debug.Print lVar_Var1; " Imp "; lVar_Var3; " = "; _
        (lVar_Var1 Imp lVar_Var3)
    Debug.Print lVar_Var2; " Imp "; lVar_Var3; " = "; _
        (lVar_Var2 Imp lVar_Var3)
    Debug.Print lVar_Var3; " Imp "; lVar_Var3; " = "; _
        (lVar_Var3 Imp lVar_Var3)
    
    'numeric example
    lVar_Var1 = 2: lVar_Var2 = 3: lVar_Var3 = 4
    Debug.Print vbCrLf; lVar_Var1 < lVar_Var2 Imp lVar_Var1 > lVar_Var2
    
    'breaking down the inner working of the example operation above
    Debug.Print lVar_Var1; " < "; lVar_Var2; " Imp "; lVar_Var1; " > "; _
        lVar_Var2; " = "; lVar_Var1 < lVar_Var2; " Imp "; lVar_Var1 > _
        lVar_Var2; " = "; lVar_Var1 < lVar_Var2 Imp lVar_Var1 > lVar_Var2
    
End Sub