How to use Logical Operators in Excel VBA Easily (3 Type Operators)

Welcome to Excel Avon 

Logical Operators?

DOWNLOAD USED EXCEL FILE FROM HERE>>

In this post, you will understand how to use Logical Operators in Excel VBA to perform various functions in your Excel workbook. AND function in excel VBA checks the given statement whether it is true or false. The AND function also evaluates conditions with each other. Checks both for the given condition and returns True. 

Today in this post we will explain 3 types of Logical Operators (NOT, AND and OR).

First, we will go to Developer tab and then go to Visual Basic.

First go to the INSERT tab and then I will insert module.

‘NOT’ Logical Operator

First of all, we will explain that ‘NOT’ operators NOT operator is considered as the simplest logical operator in Excel. You use the ‘NOT’ operator in Excel to reverse the value of its argument. Single expression is required in ‘NOT’ operator.

Experession 1	Result
True	False
False	True

We will insert the module and write the subroutine in it.

Sub UseNotOperator()

End Sub

Then we will use displaying gridline function for active sheet use of this function to make grid line visible and invisible in excel sheet.

Sub UseNotOperator()
Activewindow.DisplayingGridlines

End Sub

Then we will use the NOT operator and displaying again. Using the gridline function.

Sub UseNotOperator()
Activewindow.DisplayingGridlines = Not Activewindow.DisplayingGridlines 

End Sub

Before running the code if you see you get gridlines in excel sheet.

Now I will click Run button

After clicking run button we get reverse result if your active excel gridline is visible then it becomes invisible.

If we click the run button again then we have gridline invisible then it becomes visible.

‘AND’ Logical Operator

The logical operator AND in Excel VBA are used to compare two or more conditions. They return true or false if the conditions are fully met. For example, if both the conditions are true then the result is true, if any one condition is false then the return will be false and if both the conditions are false then the result will also be false.

Expression 1	Expression 2	Result
TRUE	TRUE	TRUE
TRUE	FALSE	FALSE
FALSE	TRUE	FALSE
FALSE	FALSE	FALSE

 

write the subroutine in it.

Sub UseNotOperator() 

End Sub

Then we will Define Variable exp1 as Boolean

Sub UseNotOperator() 
Dim Exp1 As Boolean
End Sub

Then we will Define Variable exp2 as Boolean

Sub UseNotOperator() 
Dim Exp1 As Boolean
Dim Exp2 As Boolean
End Sub

Then we will Define Variable Result as Boolean

Sub UseNotOperator() 
Dim Exp1 As Boolean
Dim Exp2 As Boolean
Dim Result As Boolean
End Sub

Now I will use Exp1 As displaying gridline function

Sub UseNotOperator() 
Dim Exp1 As Boolean
Dim Exp2 As Boolean
Dim Result As Boolean

Exp1 = Activewindow.DisplayGridlines
End Sub

Now I will use Exp2 as displaying heading function

Sub UseNotOperator() 
Dim Exp1 As Boolean
Dim Exp2 As Boolean
Dim Result As Boolean

Exp1 = Activewindow.DisplayGridlines
Exp2 = Activewindow.Displayheadlines
End Sub

Now we will write Exp1 And Exp2 for the result.

Sub UseNotOperator() 
Dim Exp1 As Boolean
Dim Exp2 As Boolean
Dim Result As Boolean

Exp1 = Activewindow.DisplayGridlines
Exp2 = Activewindow.Displayheadlines
Result = exp1 And exp2
End Sub

Now I will write Debug. Print For print the result.

Sub UseNotOperator() 
Dim Exp1 As Boolean
Dim Exp2 As Boolean
Dim Result As Boolean

Exp1 = Activewindow.DisplayGridlines
Exp2 = Activewindow.Displayheadlines
Result = exp1 And exp2
Debug.Print Result
End Sub

Both Statement is true

If you see in active sheet, both gridline and heading are visible that means both condition is true.

Now I will Run Code

If both the conditions are true, then the Immediate window will have a true print.

One Statement True

Again, I will Run Code.

If one the conditions are false, then the Immediate window will have a false print.

Both Statement False

Now we will make both the condition false and make both heading and gridlines invisible then will print false to us.

Now I will Run Code

Now if both the condition is false then it will print False.

‘OR’ Logical Operator

Similar to the AND operator, the OR logical operator compares two values. It returns true when either condition is met, false if not.
For example, if both the conditions are true then the result is TRUE, if one of the conditions is FALSE then the return will be TRUE and if both the conditions are false then the result will also be FALSE.

Expression 1	Expression 2	Result
TRUE	TRUE	TRUE
TRUE	FALSE	TRUE
FALSE	TRUE	TRUE
FALSE	FALSE	FALSE

write the subroutine for OR Operator

Sub UseNotOperator() 

End Sub

Then we will Define Variable exp1 as Boolean for OR Operator

Sub UseNotOperator() 
Dim Exp1 As Boolean
End Sub

Then we will Define Variable exp2 as Boolean for OR Operator

Sub UseNotOperator() 
Dim Exp1 As Boolean
Dim Exp2 As Boolean
End Sub

Then we will Define Variable Result as Boolean for OR Operator

Sub UseNotOperator() 
Dim Exp1 As Boolean
Dim Exp2 As Boolean
Dim Result As Boolean
End Sub

Now I will use Exp1 As displaying gridline function

Sub UseNotOperator() 
Dim Exp1 As Boolean
Dim Exp2 As Boolean
Dim Result As Boolean

Exp1 = Activewindow.DisplayGridlines
End Sub

Now I will use Exp2 as displaying heading function

Sub UseNotOperator() 
Dim Exp1 As Boolean
Dim Exp2 As Boolean
Dim Result As Boolean

Exp1 = Activewindow.DisplayGridlines
Exp2 = Activewindow.Formulas
End Sub

Now we will write Exp1 And Exp2 for the result.

Sub UseNotOperator() 
Dim Exp1 As Boolean
Dim Exp2 As Boolean
Dim Result As Boolean

Exp1 = Activewindow.DisplayGridlines
Exp2 = Activewindow.Formulas
Result = exp1 And exp2
End Sub

Now I will write Debug. Print For print the result.

Sub UseNotOperator() 
Dim Exp1 As Boolean
Dim Exp2 As Boolean
Dim Result As Boolean

Exp1 = Activewindow.DisplayGridlines
Exp2 = Activewindow.Formulas
Result = exp1 And exp2
Debug.Print Result
End Sub

Both Statement is true

If you see in active sheet, both gridline and Formulas are visible that means both condition is true.

Now I will Run code

If both the conditions are true, then the Immediate window will have a true print.

One Statement True

To make a statement false, we will make the formula bar invisible, making one of our conditions true and the other false.

Now I will Run Code

If One conditions are true, then the Immediate window will have a true print.

Both Statement False

To make both the statements false, we will make both the excel sheet formula bar and gridlines invisible so that both our condition will be false and what will be the print let’s see.

Now I will Run Code

If both statements are false, then the Immediate window will have a false printed.

So, I hope you have understood How to use Logical Operator in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.

DOWNLOAD USED EXCEL FILE FROM HERE>> (This is Logical Operator VBA Related file)

Learn More Topic in VBA here

You can also see well-explained video here about Logical Operator