Open In App

VBA Logical Operators in Excel

Improve
Improve
Like Article
Like
Save
Share
Report

Logical operators are used for performing logical and arithmetic operations on a set of values or variables. VBA allows you to use the Logical operators AND, OR, NOT, and XOR to compare values. The operators are considered “Boolean” which means they return True or False as a result. In Excel VBA, logical operators are used to perform logical comparisons and combine multiple conditions. 

VBA Logical Operators- AND, OR, NOT,XOR

AND Logical Operator

This is used to combine more than one condition. If all the condition is true AND evaluates to true. If any of the conditions is false AND evaluates to false. 

For Example: Assume variable A holds 10 and Variable B holds 0, then

a<>0 AND b<>0 is False.

OR Logical Operator

This function 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 return false.

For Example: Assume variable A holds 10 and Variable B holds 0 then

 a<>0 OR B<>0 is true.

NOT Logical Operator

This works like an inverse function. If the condition is true, it returns false, and if a condition is false it returns true.

For Example: Assume variable A holds 10 and variable B holds 0, then 

NOT(a<>0 OR b<>0) is false.

XOR Logical Operator:

It is the combination of NOT and OR Operator. If one, and only one, of the expressions, evaluate to be True, the result is True.

For Example: Assume variable A holds 10 and variable B holds 0, then 

(a<>0 XOR b<>0) is true.

VBA If AND Operator(LOGICAL AND Operator)

If both the conditions are True, then the Expression is true.

If Condition1 and Condition2 are true Then

“Code to execute if both Condition1 and Condition2  are TRUE

End if 

Example: Assume variable A holds 20 and variable B holds 0, then a<>0 AND b<>0 is False

Program:

Private Sub Demo_Loop()

Dim a As Integer //Declaring variable

a = 20

Dim b As Integer// Declaring variable

b = 0

If a<> 0 and b <>0 Then

MsgBox (“AND LOGICAL Operator Result is: True”)

Else

MsgBox (” AND LOGICAL operator Result is: False”)

End If 

End Sub

VBA code of AND logical operator.

Output:

AND LOGICAL Operator Result: False

output of AND logical operator.

VBA If OR Operator( Logical OR Operator)

If any of the two conditions are True, then the condition is true.

If Condition1 OR Condition2 then 

‘Code to execute if either Condition1 or Condition2 are True

End if

Example: Assume variable A holds 20 and variable B holds 0, then a<>0 OR b<>0 is true.

Program:

Private Sub Demo_Loop()

Dim a As Integer //Declaring variable

a = 20

Dim b As Integer //Declaring variable

b = 0

If a<> 0 OR b <>0 Then

MsgBox (“OR LOGICAL Operator Result is: True”)

Else

MsgBox (” OR LOGICAL operator Result is: False”)

End If 

End Sub

VBA code of OR logical operator.

Output:

OR Logical Operator Result is: True

output of OR logical operator.

VBA If NOT Operator Logical NOT Operator)

Reverse the result. If a condition is true, then the Logical NOT operator will make false.

If Not Condition then 

‘Code to Execute if the condition is False.

End if

Example: Assume variable A holds 20 and variable B holds 0, then NOT(a<>0 OR b<>0) is false.

Program:

Private Sub Demo_Loop()

Dim a As Integer //Declaring variable

a = 20

Dim b As Integer //Declaring variable

b = 0

If a<> 0 NOT b <>0 Then

MsgBox (“NOT LOGICAL Operator Result is: True”)

Else

MsgBox (” NOT LOGICAL operator Result is: False”)

End If 

End Sub

VBA code of NOT logical operator..

Output:

NOT LOGICAL operator Result is : False

output of Not Logical operator.

VBA If XOR Operator Logical XOR Operator)

It is the combination of NOT and OR Operator. If one, and only one, of the expressions, evaluate to be True, the result is True.

If Condition1 XOR Condition 2 then 

‘code to Execute if either of the conditions is true but not both

End if

Example: Assume variable A holds 20 and variable B holds 0, then (a<>0 XOR b<>0) is true.

Program:

Private Sub Demo_Loop()

Dim a As Integer //Declaring variable

a = 20

Dim b As Integer //Declaring variable

b = 0

If a<> 0 XOR b <>0 Then

MsgBox (“XOR LOGICAL Operator Result is: True”)

Else

MsgBox (” XOR LOGICAL operator Result is: False”)

End If 

End Sub

VBA code of XOR logical operator.

Output:

XOR LOGICAL operator Result is : True

Output of XOR logical operator.

A Sample Program showing all the Operators is included below along with the outputs:

VBA code of all Logical operator.

Program:

Private Sub Demo_Loop()

Dim a As Integer //Declaring Variable

a = 20

Dim b As Integer // Declaring variable

b = 0

If a<> 0 and b<>0  Then

   MsgBox(“AND LOGICAL operator Result is: True”)

Else 

MsgBox(” AND LOGICAL Operator Result is False”)

End If 

If a<> 0 Or b<> 0 then 

  MsgBox (“OR LOGICAL Operator Result is: True “)

Else 

  MsgBox (“OR LOGICAL operator result is: False”)

End if 

If NOT( a<>0  or b<>0) Then

   MsgBox (” NOT LOGICAL Operator Result is: True”)

Else

MsgBox (” NOT LOGICAL Operator Result is: False”)

End If

If (a<>0 XOR b<>0) Then

 MsgBox (“XOR LOGICAL Operator Result is: True”)

Else  

 MsgBox (“XOR LOGICAL Operator Result is: False”)

End if

End sub

Output:

AND LOGICAL Operator Result is: False

OR LOGICAL Operator Result is: True

NOT LOGICAL Operator Result is: False

XOR LOGICAL Operator Result is: True

output of AND logical operator.output of OR logical operator.output of NOT logical operator.Output of XOR logical operator.

FAQs on Logical Operators in Excel

Can I use Logical operators in Excel Formulas?

Yes, Logical operators can be used as Excel formulas to create conditional expressions. Using logical operators, Functions, and cell References together, can solve Complex logic operations in Excel.

Can I combine multiple conditions using Logical operators in VBA?

Yes, you can combine multiple conditions in VBA code using logical operators such as ‘AND ‘  and ‘OR’ by specifying the conditions and using these operators to join the, You can create complex logical expressions to evaluate in Excel. 

How many Logical operators are present in Excel?

There are mainly four logical operators in Excel that are ‘AND’, ‘OR’,’ NOT’, and ‘XOR’. These operator helps you to combine conditions based on logical relationships. 

What does the ‘XOR’ operator do in Excel?

‘XOR’ (combination of NOT and OR) operator in Excel checks for the conditions. It returns TRUE if one and only one condition out of two is TRUE and if none or more condition is TRUE then it will return FALSE.



Last Updated : 21 Sep, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads