Logical Operators in Excel VBA Macros

Logical operators in VBA allow you to make decisions when certain conditions are met.

They allow you to check if something equals this OR that; this AND that; NOT this; and combinations of all three. When used in conjunction with IF statements, they allows you to make more complex choices in your macros and VBA.

Logical Operators

These are easier to understand when you see them in the examples below.

Logical Operator Examples

These examples assume that you are familiar with the IF statement in VBA/Macros.

AND

Check if all of the conditions evaluate to true.

Check if A is less than B AND B is less than 10:

If A < B And B < 10 Then
    MsgBox "Hi"
End If

The important part of the code is: A < B And B < 10

You do not need parentheses around this here and you can have as many conditions as you need.

Here is the full example:

This says that if A is less than B AND B is less than 10, a message box that says "Hi" will appear.

This evaluates to True because A is less than B (A is set to 1 and B to 5) and B is less than 10.

Or

Check if ANY of the conditions evaluate to true.

Check if A is less than B OR B is less than 10.

If A < B Or B < 10 Then
    MsgBox "Hi"
End If

The important part of the code is: A < B Or B < 10

You do not need parentheses around this here and you can have as many conditions as you need.

Here is the full example:

This time I set B equal to 50, but the IF statement still evaluates to True because A, which is 1, is less than B. As such, the message box will appear and say "Hi".

When using Or only 1 condition has to be true for the statement to evaluate to true.

Not

Not literally reverses whatever condition it precedes.

Here, let's check if A is less than B, BUT let's put a Not in front of it.

If Not A < B Then
    MsgBox "Hi"
End If

The important part of the code is: Not A < B

Here is the full example:

Normally, this would evaluate to True because A is less than B, but, since there is a Not in front of the check, this evaluates to False.

If this is confusing, just mentally remove the Not and perform the comparison in your head, in this case you will get a True, then just return the opposite when a Not is present.

In this example, the message box will not appear.

More Complex Examples

Let's kick things up a notch.

When you have more complex checks in your IF statement, you will need to use parenthesis.

Example 1

Check if A is less than B AND B is greater than 10 OR it is less than 40.

Sub logical_examples_1()

A = 100
B = 30

If A < B And (B > 10 Or B < 40) Then

    MsgBox "Hi"

End If

End Sub

This evaluates to False; however, without the parentheses after the And, this would evaluate to True.

Parentheses force a part of the check to be evaluated first or simply just together. In this example, the part before the And is evaluated and then everything within the parentheses is evaluated together; after that the part on the left of the And is compare to the part on the right.

This is kind of confusing; you really need to download the sample file for this tutorial and play around with this until you understand it; that is the best way to learn this concept.

Example 2 (Not)

Let's use the same example, except A now equals 10 and we will put a Not after the And.

Sub logical_examples_2()

A = 10
B = 30

If A < B And Not (B > 10 Or B < 40) Then

    MsgBox "Hi"

End If

End Sub

If there was no Not after the And in this example, it would evaluate to True and the message box would appear.

However, the Not reverses the result. In this case, the Not reverses the result of the checks within the parenthesis.

Look to each comparison, evaluate it, and then compare them using the logical operators. This is the best way to understand how all of this works. This may sound confusing, but it will help if you download the sample file attached to this tutorial and play around with this example.

Notes

The And, Or, and Not logical operators are extremely useful in VBA and Macros. You must learn how to use them if you want to build more useful macros in Excel.

When it comes to parentheses, people tend to over-use them; however, I say that you should use them to the point that everything becomes clear and easy-to-understand for you! If you need a lot of parentheses, add them; if you are really good with the order of precedence, which controls what parts of the condition will be evaluated first, use fewer parentheses.

To help learn how to use these operators and parentheses, download the sample file attached to this tutorial and play around with the examples in there; compare them with this tutorial and edit them and see what happens. If you do this, you will have these concepts memorized in no time.