Home and Learn - Free Excel VBA Course
3.4 Logical Operators in Excel VBA
In the previous lesson, you used the Conditional Operators. In this lesson, you'll learn about the Logic Operator.
You can have more than one condition on the same line of your If and ElseIf Statements. To test for more than one condition, you need the Logic Operators. Here's a table of them:
Only the first three are used regularly. Let's see how they works. Before doing so, however, there's one more important variable type we need to discuss - Boolean values.
Boolean Values
You can set up something called a Boolean variable. Boolean variables have only two values: either true or false. You set them up like this:
Dim BooleanFlag As Boolean
BooleanFlag = True
Here, we set up a variable called BooleanFlag. Instead of setting the variable to As Integer or As Long we've set this one up with As Boolean. The second line puts something in the variable. But notice that that something is a value of True. The only other value we could have used here is False.
You can use your Boolean values like this:
If BooleanFlag = True Then
MsgBox "It's True"
Else
MsgBox "It's False"
End If
The first line test if the variable called BooleanFlag is equal to a value of True. If it is, then we display a message. Because there are only two options, we can just have an Else part to test for all other values, as any other value will be False.
You can miss out the = True part, if you like, and just have this:
If BooleanFlag Then
VBA will then take this to mean "If BooleanFlag has a value of True".
With all that in mind, let's take a look at the Not operator.
Logical Not
The Not operator is used to test if a value or variable is NOT something. We'll use our BooleanFlag variable from above to demonstrate this concept.
Create a new Sub in your code code window from the previous section. Call it Bool_Test and add the following code:
Dim BooleanFlag As Boolean
BooleanFlag = True
If BooleanFlag = True Then
MsgBox "It's True"
Else
MsgBox "It's False"
End If
Your coding window will then look like this:
Press F5 to run your code and you should see the first message box appear saying "It's True".
Now change the first line of your If Statement to this:
If Not BooleanFlag = True Then
We're using the Logic Operator Not after the word If. Before we added Not, the If Statement read "If BooleanFlag has a value of True". By place the word Not before BooleanFlag we're now saying, "If BooleanFlag DOES NOT has a value of True". Since BooleanFlag actually does have a value of True then the Else part gets executed.
Run your code again and you should see the second message box appear, saying "It's False".
Logical And
The And operator test if two or more conditions are true. If, for example, you wanted to check if a number was greater than 20 but less than 30 you can use the And operator to test this condition:
Dim Age As Integer
Age = 21
If Age > 20 And Age < 30 Then
MsgBox "Between 20 and 30"
Else
MsgBox "Not Between 20 and 30"
End If
The word And goes between your two conditions. Be careful of doing this, though:
If Age > 20 And < 30 Then
Here, we've only used the variable Age once. But you need to type your variable twice, once before the And and once after it.
Only if both conditions evaluate to TRUE does the entire line become TRUE. If one of them is FALSE then the entire line is FALSE.
You can test for more than two conditions:
If Age > 20 And Age < 30 And BooleanFlag = True Then
In the code above, we're test three things: Age has to be greater than 20, Age has to be less than 30, BooleanFlag has to have a value of true. All three conditions have to evalute to TRUE before the entire line is TRUE.
Logical Or
With Logical And, you're testing if two or more conditions are true. Only if BOTH conditions are true does the entire If Statement become true. Logical Or, on the other hand, tests if just one of two or more conditions are true. The whole of the If Statement becomes true if at least one of the conditions are true.
To clear this up, create a new Sub in your code window. Call it Test_Or. Add the following between Sub and End Sub:
Dim FirstNumber As Integer
Dim SecondNumber As Integer
FirstNumber = 10
SecondNumber = 20
If FirstNumber = 10 Or SecondNumber = 20 Then
MsgBox "Valid Number"
Else
MsgBox "Non Valid Number"
End If
The code just sets up two integer variables, FirstNumber and SecondNumber. We have a value of 10 in FirstNumber and a value of 20 in SecondNumber. The IF Statement is trying to test what's in these numbers:
If FirstNumber = 10 Or SecondNumber = 20 Then
The valid numbers are 10 and 20. We don't really care if FirstNumber
AND SecondNumber hold 10 and 20. Just as long as at least one of them
holds the correct number, then that's OK.
Run your code and you should see the first message box display, "Valid
Number". Now change your code so that FirstNumber holds a value of 30.
Run the programme again and you'll find the first message box still displays.
However, change the value of SecondNumber to 40 and now neither number
holds the correct value. In which case, the second message box will display
when the programme is run.
Nested If
You can nest one (or even more than one) If Statement inside another. Examine the following code:
An Integer variable has been set up called score. This has been set to a value of 27. The first If Statement, the outer one, is this:
If score > 20 And score < 30 Then
So we want to test score to see if it's greater than 20 AND less than 30. If it's not, then we have an Else part:
Else
MsgBox "Not between 20 and 30"
However, If score is indeed between 20 and 30 then the outer If Statement evaluates to TRUE. In which case, the code that gets executed is another If Statement. This one:
If score < 25 Then
MsgBox "A-"
Else
MsgBox "A+"
End If
This new If Statement checks to see if score is less than 25. If it is, then we know that the variable called score is between 20 and 25. A score of between 20 and 25 gets the message ""A-". Any other result and we know that score is between 25 and 30. In which case, the message is "A+".
Nested If Statements are a great way to narrow down your search for a particular value. They can be tricky to use, though. But stick with them and your programming skills will come on a treat!
In the next part below, we'll take a look at some more practical ways to use the lessons you have learned in this and previous sections.
Next Lesson: 3.5 Excel VBA Practice 1 >