Logical Operators in Excel VBA Macros

Add to Favorites

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

Operator Meaning
AND

A < B AND B < 10 - Checks if both conditions are true.

OR

A < B OR B < 10 - Checks if one of the conditions is true.

NOT

Not A < B - This reverses the check, which means this will evaluate to true if A is greater than B and NOT if it is less than B.

Xor

Rarely used. Click here for more info on it.

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:

bf8497d54c2335213fbb0b86501a99c7.jpg

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:

7ccb6ee1eb773f1d70609017f6f375bc.jpg

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:

e54df08a6250374941f10b99f13bc248.jpg

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

ad025a978b09689b6176430e8824d9c9.jpg

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

64e7071268de8671b7a7fae19bbd207a.jpg

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.


Downloadable Files: Excel File

Similar Content on TeachExcel
How to Add a New Line to a Message Box (MsgBox) in Excel VBA Macros
Tutorial: I'll show you how to create a message box popup window in Excel that contains text on mult...
Logical Comparison Operators in Excel - How to Compare Things
Tutorial: Logical comparison operators allow you to compare values in Excel to see if they are equal...
Loop Through an Array in Excel VBA Macros
Tutorial: I'll show you how to loop through an array in VBA and macros in Excel.  This is a fairly ...
Select Cells in Excel using Macros and VBA
Tutorial: This is actually a very easy thing to do and only requires a couple lines of code. Below I...
Loop through a Range of Cells in Excel VBA/Macros
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
VBA IF Statement in Excel Macros
Tutorial: IF statements allow you to make decisions in your Macros and VBA in Excel. An IF statement...