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.

<< Excel VBA Course Menu

Lots more free online courses here on our main Home and Learn site

© All course material copyright Ken Carney