Artithmetic Operators

In this blog post we will be examining Operators and how they can help you to construct if statements in VBA.


An expression is a single or collection of variables and operators that ultimately evaluate to True or False.  Here we will list all the arithmetic operators with example code and introduce some operators you’ll need when programming.

Boolean as an Expression

In If statements, the expression being investigated must evaluate to true or false.

Sub expressionEqualsTrue()
    Dim TrueOrFalse As Boolean
    TrueOrFalse = True
    
    If TrueOrFalse Then
    'We only need to provide the variable here
    'We do not need to write:
    '--If TrueOrFalse = true then--
    
        Debug.Print "The expression is True"
    Else
        Debug.Print "The expression is False"
    End If
End Sub

The output to the immediate window will be:

The expression is True
Sub expressionEqualsFalse()
    Dim TrueOrFalse As Boolean
    TrueOrFalse = False
    
    If TrueOrFalse Then
    'We only need to provide the variable here
    'We do not need to write:
    '--If TrueOrFalse = true then--
        Debug.Print "The expression is True"
    Else
        Debug.Print "The expression is False"
    End If
End Sub

The output to the immediate window will be:

The expression is False

Arithmetic Operators

Arithmetic operators work by comparing two expressions.

 

A=B

Equal To

Tests for value equality

A>B

Greater Than

Evaluates to True when A is Greater Than B

A>=B

Greater Than or
Equal To

Evaluates to True when A is at least the value of B

A<B

Less Than

Evaluates to True when A is Less Than B

A<=B

Less Than or
Equal To

Evaluates to True when A is at most B

A<>B

Great than Or Less than or, Doesn’t Equal

Evaluates to True when A doesn’t equal B

Examples of use:

Sub arithmeticOperators1()
    Dim A As Integer, B As Integer
    
    ' Test Greater Than
    A = 20
    B = 21
    
    If A > B Then
        Debug.Print "A is Greater than B"
    Else
        Debug.Print "B is Greater than A"
    End If
    
    ' Test Less Than
    A = 20
    B = 19
    
    If A < B Then
        Debug.Print "A is Less Than B"
    Else
        Debug.Print "B is Less Than A"
    End If
    
    ' Test Not Equal To
    A = 20
    B = 50
    
    If A <> B Then
        Debug.Print "A and B are Not Equal."
    Else
        Debug.Print "A and B are Equal"
    End If

End Sub

The output to the immediate window will be:

B is Greater than A
B is Less Than A
A and B are Not Equal.

We can also compare Strings.

Sub arithmeticOperators2()
    'When comparing strings, we are actually asking Access
    'to say which value comes first. Imagine a list of values
    'like this:
    '  0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M etc.
    'D comes after 4 so D is "greater than" 4

    Dim A As String
    Dim B As String
    Dim C As String
    Dim D As String
    
    A = "farming"
    If (A = "farming") Then
        Debug.Print "A equals " & A
    Else
        Debug.Print "A does not equal " & A
    End If
    
    
    A = "1"
    B = "02"
    If (A > B) Then
        Debug.Print "A is higher than B"
    Else
        Debug.Print "B is higher than A"
    End If
    
    C = "a"
    D = "1"
    If (C >= D) Then
        Debug.Print C & " is equal to or greater than " & D
    Else
        Debug.Print C & " is less than " & D
    End If

End Sub

The output to the immediate window will be:

A equals farming
A is higher than B
a is equal to or greater than 1

Understanding arithmetic operators will help you to write better, more compact if statements.

Related Posts

Logical Operators
Using The Like Operator In Queries

Leave a Reply

Your email address will not be published. Required fields are marked *

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube