VBA Operators

Precedence Order

1. 1. Parentheses
      1. Parentheses: ()
   2. Arithmetic Operators
      1. Exponentiation: ^
      2. Unary Identity and Negation: +, -
      3. Multiplication and Floating-Point Division: *, /
      4. Integer Division: \
      5. Remainder Division: Mod
      6. Addition and Subtraction: +, -
   3. Concatenation Operators
      1. String Concatenation: &, +
   4. Comparison Operators
      1. All Comparison Operators: =, <>, <, <=, >, >=, Like, Is, TypeOf...Is
   5. Logical and Bitwise Operators
      1. Not
      2. And
      3. Or
      4. Xor
      5. Eqv
      6. Imp

Associativity

Operators of equal precedence will be interpreted from left to right.

Public Sub Example()

    Debug.Print 10 / 5 * 2   'Prints: 4
    
    Debug.Print (10 / 5) * 2 'Prints: 4

End Sub

Strings

When comparing strings using comparison operators, the comparison method used is determined by the Option Compare statement. If no Option Compare statement is used, strings are compared using their underlying Binary representations by default (Same as using Option Compare Binary). The StrComp function provides finer control over string comparison by allowing the compare method to be specified.

Option Explicit
Option Compare Binary

Public Sub Example()

    Debug.Print "A" <> "a"

End Sub

Option Explicit
Option Compare Text

Public Sub Example()

    Debug.Print "A" = "a"

End Sub

Option Explicit

Public Sub Example()

    Debug.Print StrComp("A", "a", vbTextCompare) = 0

End Sub

The Like operator can be used to compare strings using Wildcard characters to match text patterns.

Public Sub Example()

    Debug.Print "Hello, World!" Like "Hello*"

End Sub

Objects

The Is keyword can be used to check if two object variables refer to the same object in memory. When objects are compared using Is, the data stored in the object's fields are not compared. A function would need to be created that compares each member of an object, given the object's type.

Public Sub Example()

    Dim C1 As Collection
    Dim C2 As Collection

    Set C1 = New Collection
    Set C2 = New Collection

    Debug.Print C1 Is C2 'Prints: False

    Set C2 = C1

    Debug.Print C1 Is C2 'Prints: True

End Sub

TypeOf...Is can be used to check an object's type. This should be used when an object is declared using the generic Object type and a certain action requires the object to be of a certain type.

Public Sub Example()

    Dim Obj As Object
    Set Obj = New Collection

    If TypeOf Obj Is Collection Then
        Debug.Print Obj.Count
    Else
        Debug.Print "Obj is not Collection object"
    End If

End Sub

And

True if both conditions are true

Or

True if at least one condition is true

Xor

True if one and only one condition is true

Not

True if False, False if True

Eqv

True if conditions are logically equivelent, otherwise False

Imp

If Condition1 is True, Condition2 must be True. If Condition1 is False, Condition2 can be True or False.

If I am a fish, I breathe water. If I am not a fish, I may breathe water or not.

And

Bitwise And reads integers as Binary and compares each bit using And logic.

Public Sub Example()

    Dim Num1    As Byte
    Dim Num2    As Byte
    Dim Result  As Byte

    Num1 = 6  '00000110 in binary
    Num2 = 12 '00001100 in binary

    Result = Num1 And Num2

    Debug.Print Result 'Prints 4 -> 00000100 in binary

End Sub

Or

Bitwise Or reads integers as Binary and compares each bit using Or logic.

Public Sub Example()

    Dim Num1    As Byte
    Dim Num2    As Byte
    Dim Result  As Byte

    Num1 = 6  '00000110 in binary
    Num2 = 12 '00001100 in binary

    Result = Num1 Or Num2

    Debug.Print Result 'Prints 14 -> 00001110 in binary

End Sub

Xor

Bitwise Xor reads integers as Binary and compares each bit using Xor logic.

Public Sub Example()

    Dim Num1    As Byte
    Dim Num2    As Byte
    Dim Result  As Byte

    Num1 = 6   '00000110 in binary
    Num2 = 12  '00001100 in binary

    Result = Num1 Xor Num2

    Debug.Print Result 'Prints 10 -> 00001010 in binary

End Sub

Not

Bitwise Not reads an integer as Binary and reverses each bit.

Public Sub Example()

    Dim Num     As Byte
    Dim Result  As Byte

    Num = 3 '00000011 in binary

    Result = Not Num

    Debug.Print Result 'Prints 252 -> 11111100 in binary

End Sub

Eqv

Bitwise Eqv reads an integer as Binary and compares each bit using Eqv logic.

Public Sub Example()

    Dim Num1    As Byte
    Dim Num2    As Byte
    Dim Result  As Byte

    Num1 = 6    '00000110 in binary
    Num2 = 12   '00001100 in binary

    Result = Num1 Eqv Num2

    Debug.Print Result 'Prints 245 -> 11110101

End Sub

Imp

Bitwise Imp reads an integer as Binary and compares each bit using Imp logic.

Public Sub Example()

    Dim Num1    As Byte
    Dim Num2    As Byte
    Dim Result  As Byte

    Num1 = 6    '00000110 in binary
    Num2 = 12   '00001100 in binary

    Result = Num1 Imp Num2

    Debug.Print Result  'Prints 253 -> 11111101 in binary

End Sub