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 |
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 |
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 OperatorsUsing The Like Operator In Queries Operators
