VBA Logical Operators: AND, OR, NOT, IF NOT in Excel VBA

VBA Logical Operators: AND, OR, NOT

Excel VBA Logical Operators

Let's say you want to process a customer order. For that, you want to first check to see if the ordered product exists or not. If it does, you also want to check if the quantity on hand is enough. Logical operators come in handy in such cases. Logical operators are used to evaluate more than one condition.

The main Excel VBA logical operators AND, OR, NOT are listed in the table below:

S/NOperatorDescriptionExampleOutput1ANDAND: This is used to combine more than one condition. If all the conditions are true, AND evaluates to true. If any of the condition is false, AND evaluates to falseIf true = true AND false = true THENfalse2OROR: This is used to combine more than one condition. If any of the conditions evaluate to true, OR returns true. If all of them are false, OR returns falseIf true = true OR true = false THENtrue3NOTNOT: This one works like an inverse function. If the condition is true, it returns false, and if a condition is false, it returns true.If NOT (true) Thenfalse

VBA Logical Operators Example Source Code

For the sake of simplicity, we will be comparing hard coded numbers.

How to write a TEST CASE Software Testing Tutorial

Add ActiveX buttons to the sheet from the "Insert option."

Set the properties as shown in the image below

VBA Logical Operators

The following table shows the properties that you need to change and the values that you need to update too.

S/NControlPropertyValue1CommandButton1NamebtnAND

CaptionAND Operator (0 = 0)2CommandButton2NamebtnOR

CaptionOR Operator (1 = 1) Or (5 = 0)3CommandButton3NamebtnNOT

CaptionNOT Operator Not (0 = )

Add the following code to btnAND_Click

Private Sub btnAND_Click()If (1 = 1) And (0 = 0) ThenMsgBox "AND evaluated to TRUE", vbOKOnly, "AND operator"ElseMsgBox "AND evaluated to FALSE", vbOKOnly, "AND operator"End IfEnd Sub

VBA If AND Operator

• "If (1 = 1) And (0 = 0) Then" the if statement uses the AND logical operator to combine two conditions (1 = 1) And (0 = 0). If both conditions are true, the code above 'Else' keyword is executed. If both conditions are not true, the code below 'Else' keyword is executed.

Add the following code to btnOR_Click

Private Sub btnOR_Click()If (1 = 1) Or (5 = 0) ThenMsgBox "OR evaluated to TRUE", vbOKOnly, "OR operator"ElseMsgBox "OR evaluated to FALSE", vbOKOnly, "OR operator"End IfEnd Sub

VBA If OR Operator

• "If (1 = 1) Or (5 = 0) Then" the if statement uses the OR logical operator to combine two conditions (1 = 1) And (5 = 0). If any of the conditions is true, the code above Else keyword is executed. If both conditions are false, the code below Else keyword is executed.

Add the following code to btnNOT_Click

Private Sub btnNOT_Click()If Not (0 = 0) ThenMsgBox "NOT evaluated to TRUE", vbOKOnly, "NOT operator"ElseMsgBox "NOT evaluated to FALSE", vbOKOnly, "NOT operator"End IfEnd Sub

VBA If NOT Operator

• "If Not (0 = 0) Then" the VBA If Not function uses the NOT logical operator to negate the result of the if statement condition. If the conditions is true, the code below 'Else' keyword is executed. If the condition is true, the code above Else keyword is executed.

Download Excel containing above code