How to Use IF OR Statements in VBA

In VBA, IF OR is the combined utilization of the IF statement and the OR keyword. Particularly, the OR keyword is used to construct the logic of the condition statement. The syntax for the combined IF OR statement is as follows:

IF [condition statement 1] OR  [condition statement 2] Then

[Execute if either condition 1 or condition 2 are true]

Else

[Execute if condition 1 and condition 2 are both false]

End If

Contents

Example 1

In this example, we have two values and a comparison value. The purpose of our code is to compare the two values with the comparison value and check if either of them exceed the comparison value or not. Note that Range(“B1”) is Value 1, Range(“B2”) is Value 2, and Range(“B3”) is Comparison Value.

Excel spreadsheet with two values and comparison value
Sub IF_OR()
IF Range("B1") > Range("B3") OR Range("B2") > Range("B3") Then
    MsgBox "One of the values is greater than the comparison value"
Else
    MsgBox "Both values are within limit"
End If
End Sub 

When we run the above code, we get a resulting messaging below stating that both values are within limit, because the first value is 5 and the second value is 6, and neither of them are greater than 8.

Message: Both values are within limit

Because we’re using OR, only one of the two statements need to be correct (either Range(“B1”) > Range(“B3”) is correct OR Range(“B2”) > Range(“B3”) is correct) in order for the condition/test statement to be correct and the line that follows THEN will be executed, otherwise, the line after ELSE would be executed (if it exists).

Example 2

Let us change one of the values to make it greater than the comparison value. We will change the first value and make it greater than the comparison value. This will cause our condition statement within the If statement to become true. The whole statement is true because one of the two values is indeed greater than the comparison value.

Value 1 is now greater than Value 2
Sub IF_OR()
If Range("B1") > Range("B3") Or Range("B2") > Range("B3") Then
    MsgBox "One of the values is greater than the comparison value"
Else
    MsgBox "Both values are within limit"
End If
End Sub

Running the code will result in displaying the first message (“One of the values is greater than the comparison value”). Note: if we had used AND instead of OR then the condition statement of IF would have been false because not both values are greater than the comparison value. Only the first value is greater than the comparison value.

One of the values is greater than the comparison value

Bonus: IF And in VBA

Using the AND keyword instead of OR will change our logic from “if either condition 1 or condition 2 are met, then the statement is TRUE” to “both condition 1 and condition 2 must be met for the statement to be TRUE.” Hence, the syntax will be as follows:

IF [condition statement 1] AND  [condition statement 2] then

[Execute if both condition 1 and condition 2 are true]

Else

[Execute if either condition 1 or condition 2 are false]

End If

In terms of logic diagrams, we can show the different between OR and AND in the following way:

XYANDOR
TRUETRUETRUETRUE
TRUEFALSEFALSETRUE
FALSETRUEFALSETRUE
FALSEFALSEFALSEFALSE

As shown above, the only time that we get a TRUE when we use AND is when both X and Y are TRUE. On the other hand, the only time that OR is False is when both X and Y are FALSE.

Example 3

In this example, we use AND instead of OR. In this case, instead of checking if one of the two values is greater than the comparison value, we check If both value 1 and value 2 are greater than the comparison values. If the condition, then this indicates that one of the values is not greater than the comparison value.

Sub IF_AND()
If Range("B1") > Range("B3") And Range("B2") > Range("B3") Then
    MsgBox "Both values are greater than the comparison value"
Else
    MsgBox "At least one of the values is within limit"
End If
End Sub
At least one of the values is within limt

Combining AND OR

We could combine OR and AND in the same statement. In the following example, note the utilization of brackets to specify which conditions we are using AND with and which conditions we’re using OR with. Now that’s a tongue twister.

Example 4

In this example, we are saying that if either cells B1 AND B2 are greater than B3 OR cells C1 AND C2 are greater than B3 then the statement is TRUE. Note the use of the brackets as highlighted below.

At least one pair of values is greater than the comparison value

Conclusion

Embedding the AND OR keywords allows you to fine tune the IF statement logic. Careful examination of the logic is critical in order to wisely choose which keyword to use in each situation. 

Related:

Else without If errors in VBA

Leave a Reply

Your email address will not be published. Required fields are marked *