Tip:
Highlight text to annotate it
X
In this lesson we will discuss relational and logical operators.
In most real-life situations, you perform different tasks based on the current set of conditions.
For example, you will buy milk at the store if you run out. Or you will press the gas pedal if the traffic light is green.
Sometimes there are multiple options available to you.
You might wear a red hat if it is Monday, a blue hat if it is Tuesday through Friday, or an orange hat if it is the weekend.
Sometimes multiple test conditions must be met to accomplish a certain task.
Perhaps you will eat at your favorite restaurant if a friend will join you and you are within 10 miles of the restaurant and you have at least $50 in
your bank account, otherwise you will eat instant noodles at home.
So far our programs have executed every statement line by line.
But often we want our programs to be more sophisticated and have the ability to selectively execute statements based on the values of
certain variables at certain points in the program.
Selective execution requires us to make comparisons of different quantities.
Relational operators allow us to make these comparisons.
There are six relational operators.
The equal symbol means “equal to”.
VBA uses this symbol for assigning values to variables and it also is the “equal to” relational operator.
The less than symbol followed by the greater than symbol means “not equal to”.
The less than symbol alone means “less than”. The greater than symbol alone means “greater than”.
The less than symbol followed by the equal symbol means “less than or equal to”.
The greater than symbol followed by the equal symbol means “greater than or equal to”.
Relational operators are used to compare two quantities in a Boolean expression.
When you use a relational operator, you are asking the question: Is the left quantity ________ the right quantity?
where the blank space where the relational operator is placed.
After comparing the two quantities, a Boolean value, either True or False, will replace the entire expression.
If the answer to the question is yes, a True will replace the Boolean expression.
If the answer to the question is no, a False will replace the Boolean expression.
Here is a simple example of how to use relational operators.
When we run the program two message boxes will appear.
The first message box states: False
The second message box states: True
Let’s discuss why these values appear in the message boxes.
The variables a and b are given a Double data type and assigned the values 5 and 6.
The first message box displays the result of the Boolean expression: a > b
Is a greater than b? Is 5 greater than 6? The answer is False and this value is displayed
in the first message box.
The second message box displays the result of the Boolean expression: 2 * a b + 50
Is 2 * a not equal to b + 50? Is 10 not equal to 56? The answer is True and this value is displayed in
the second message box.
We can store the result of a Boolean expression in a variable that has a Boolean data type.
I have altered the code to create two additional variables, named c and d, and these two variables have a Boolean data type.
This means c and d can only store the value True or False.
The value of the expression a > b, which is False, is stored in c.
The value of the expression 2 * a b + 50, which is True, is stored in d.
We can display the values of these Boolean variables using message boxes.
When we run our code, we see two message boxes appear.
The first message box states: False the value of c
The second message box states: True the value of d
Relational operators allow us to test if a condition if met by comparing two quantities.
Often we will want to check if multiple conditions are met and logical operators allow us to do that.
The three logical operators we will discuss in this course are: And, Or, Not
Let’s assume a and b are Double variables and a is assigned the value 5 while b is assigned the value 6.
When you use the And operator, you are asking the question: “Are both comparisons True?”
If the answer is yes, a True is returned.
If the answer is no, a False is returned.
For example: a less than b And b equals 6
Are both a less than b and b equals 6 True?
5 less than 6 And 6 equals 6 True And True
Since both comparisons are True, the entire expression is True.
How about, a greater than b And b equals 6?
Are both a greater than b and b equals 6 True?
5 greater than 6 And 6 equals 6 False And True
Since one of the comparisons is False, the entire expression is False.
a less than b And b equals 7
Are both a less than b and b equals 7 True?
5 less than 6 And 6 equals 7 True And False
Since one of the comparisons is False, the entire expression is False.
Finally, a greater than b And b equals 7
Are both a greater than b and b equals 7 True?
5 greater than 6 And 6 equals 7 False And False
Since at least one of the comparisons is False, the entire expression is False.
When you use the Or operator, you ask the question: “Is either comparison True?”
If the answer is yes, a True is returned. If the answer is no, a False is returned.
For example, a less than b Or b equals 6.
Is either a less than b or b equals 6 True?
5 less than 6 Or 6 equals 6 True Or True
Since at least one of the comparisons is True, the entire expression is replaced by True.
a greater than b Or b equals 6
Is either a greater than b or b equals 6 True?
5 greater than 6 Or 6 equals 6 False Or True
Since at least one of the comparisons is True, the entire expression is replaced by True.
a less than b Or b equals 7
Is either a less than b or b equals 7 True?
5 less than 6 Or 6 equals 7 True Or False
Since at least one of the comparisons is True, the entire expression is replaced by True.
Finally, a greater than b Or b equals 7
Is either a greater than b or b equals 7 True?
5 greater than 6 Or 6 equals 7 False Or False
Since none of the comparinsons are True, the entire expression is replaced by False.
The Not operator changes the result of a comparison to its opposite value.
If you place Not in front of a True comparison, False is returned.
If you place Not in front of a False comparison, True is returned.
For example, Not a less than b Not 6 less than 6
Not True The opposite of True is False.
Not a greater than b Not 5 greater than 6
Not False The opposite of False is True.
Logical expressions usually are used in decision structures, which enable your programs to selectively execute statements.
There are two main types of decision structures in VBA: If structures and Select Case structures.
We will discuss both types of structures in next few lessons.