Logical Operator in Excel VBA
Contant
- Introduction about VBA Logical Operation Excel VBA
- And VBA Code under VBA Logical Operation Excel VBA
- Or VBA Code under VBA Logical Operation Excel VBA
- Not VBA Code under VBA Logical Operation Excel VBA
- Xor VBA Code under VBA Logical Operation Excel VBA
- Eqv VBA Code under VBA Logical Operation Excel VBA
- Question and Answer about VBA Logical Operation in Excel VBA
Introduction
In VBA (Visual Basic for Applications), logical operators are used to perform logical operations, such as combining or comparing multiple conditions. Logical operators return True or False and are often used in control structures like If…Then…Else statements. Below are the main logical operators in VBA:
Following logical operators are supported by VBA
| Operator | Description |
| And | Rertun True if both Condition are true. Otherwise, will return to False. |
| Or | Return True if a least condition is true. Returns false only if both condition are false. |
| Not | Reverses the logical value of the condition. Returns True if the condition is False, and vice versa. |
| Xor | Return True if one and only one of the condition is true |
| Eqv | Return True if both conditions are the same (True/True or False/False) |
| Imp | Returns False Only if the first conditions is true and the second condition is false. |
- And: Returns True if both conditions are true.
Syntax:
If condition1 and Condition2 then ‘ Code to execute if both conditions are true End if
Example:
Sub example1() Dim A As Long, B As Long A = 6 B = 9 If A > 5 And B < 10 Then MsgBox "Both conditions are True" End If End Sub
2. Or: Returns True if at least one of the conditions is true.
Syntax:
If condition1 or condition2 Then ‘Code to execute if either condition is true End IF Example
Sub example2() Dim A As Long, B As Long A = 4 B = 9 If A > 5 Or B < 10 Then MsgBox "At least one condition is True" Else MsgBox "No Condition Met" End If End Sub
3. Not: Reverses the logical state of a condition. If a condition is True, Not makes it False, and vice versa.
Syntax:
If not condition then
‘Code to execute if condition is false
End If
Example:
Sub Example3() Dim A As Long A = 4 If Not A > 5 Then MsgBox "A is not greater than 5" End If End Sub
4. Xor: Returns True if exactly one of the conditions is true. If both are true or both are false, it returns False.
Syntax:
IF condition1 Xor condition2 Then ‘Code to execute if only one of the conditions is true End If
Example:
Sub example4() Dim A As Long, B As Long A = 6 B = 11 If A > 5 Xor B < 10 Then MsgBox "One condition is True, but not both" Else MsgBox "Both Condition not true" End If End Sub
5. Eqv: Returns True if both conditions are either true or false (logical equivalence).
Syntax:
If condition1 Eqv condition2 Then ‘Code to execute if both conditions have the same logical value End If
Example:
Sub example5() Dim A As Long, B As Long A = 6 B = 9 If A > 5 Eqv B < 10 Then MsgBox "Both conditions are either True or False" Else MsgBox "Both Condition not true" End If End Sub
6. Imp: Returns False only if the first condition is True and the second condition is False; otherwise, it returns True (logical implication).
Syntax:
If condition1 Imp condition2 Then ‘Code to execute if the logical implication is true End If
Exmaple:
Sub example6() Dim A As Long, B As Long A = 6 B = 9 If A > 5 Imp B < 10 Then MsgBox "Condition A implies Condition B" Else MsgBox "Both condition net met" End If End Sub
For Practice
Example1:
Dim a As Integer, b As Integer a = 10 b = 20 If a > 5 And b < 30 Then MsgBox "Both conditions are true." End If If a > 15 Or b < 25 Then MsgBox "At least one condition is true." End If If Not (a > b) Then MsgBox "a is not greater than b." End If
Example2:
Private Sub Demo_3 ()
Dim a As Integer
a = 10
Dim b As Integer
b = 0
If a <> 0 And b <> 0 Then
MsgBox ("AND Operator Result is : True")
Else
MsgBox ("AND Operator Result is : False")
End If
If a <> 0 Or b <> 0 Then
MsgBox ("OR Operator Result is : True")
Else
MsgBox ("OR Operator Result is : False")
End If
If Not (a <> 0 Or b <> 0) Then
MsgBox ("NOT Operator Result is : True")
Else
MsgBox ("NOT Operator Result is : False")
End If
If (a <> 0 Xor b <> 0) Then
MsgBox ("XOR Operator Result is : True")
Else
MsgBox ("XOR Operator Result is : False")
End If
End Sub
Common Uses:
- Conditional Statements: Logical operators are often used in If…Then…Else or Select Case statements to control the flow of a VBA program.
- Loop Control: They are also useful for controlling loops where multiple conditions need to be evaluated before proceeding.
These logical operators are essential for making decisions in your VBA code, allowing you to create complex conditional logic and efficiently control the program flow.
Q & A
- What does the And operator do in VBA?
Answer: The And operator is used to combine two conditions and returns True only if both conditions are true. For example, If a > 5 And b < 10 Then will execute the block if both a > 5 and b < 10 are true.
2. How is the Or operator different from the And operator in VBA?
Answer: The Or operator returns True if at least one of the conditions is true, whereas the And operator requires both conditions to be true to return True.
3. When would you use the Not operator in VBA?
Answer: The Not operator is used to reverse the logical state of a condition. It turns True to False and vice versa. For example, If Not condition executes the code block if the condition is false.
4. What is the purpose of the Xor operator in VBA?
Answer: The Xor operator returns True if only one of the conditions is true. If both are true or both are false, it returns False. This is useful when you need a condition where only one of two possible states can be true.
5. Can you explain the difference between Eqv and Imp logical operators in VBA?
Answer: Eqv returns True if both conditions have the same logical value (both true or both false). Imp (implication) returns False only if the first condition is True and the second condition is False; otherwise, it returns True.
- VBA Select Case: The Select Case statement in VBA is used for evaluating an expression against multiple possible values, making it a more readable and efficient alternative to using multiple If…ElseIf statements.
- VBA If Then Else : The If…Then…Else statement in VBA is used to execute different blocks of code based on whether a condition is True or False
- VBA Nested If Statement: In VBA (Visual Basic for Applications), nested If statements allow you to make more complex decision structures by including one If statement inside another.
- VBA Switch Statement : In VBA (Visual Basic for Applications), the Switch function is used to evaluate a list of expressions and return a value corresponding to the first expression that evaluates to True.
