In this article, I’ll show you how you can use the If statement in VBA in Excel. You’ll learn to use the If statement, along with to use the ElseIf and Else statements.
How to Use the If Statement in VBA in Excel
Here we’ve got a Macro with a VBA code in which we’ve declared two integers called Integer1 and Integer2.
Integer1 is set to have the value 12.
And Integer2 is set to have the value 17.
Today I’ll show you how you can use the If statement of VBA in this code.
1. If Statement in Excel VBA: Only If Statement
First, let’s see how we can use only the If statement.
The syntax of the If statement in VBA is:
If Condition Then
Statement
End IfIf the condition is True, then the code will execute the statement.
Otherwise, it won’t be executed.
For example, let’s show a message box displaying “Integer1 is Greater than 10” if Integer1 is actually greater than 10.
The lines of code will be:
If Integer1 > 10 Then
MsgBox "Integer1 is Greater than 10."
End If[The If Statement must end with an End If statement.]
As Integer1 is greater than 10, this code will show the message “Integer1 is Greater than 10” if you run this code.
If you wish, you can join two or more conditions with AND or OR statements within the If statement.
For example, let’s display the message “Integer 1 is Greater than 15 or Integer2 is Less than 20” if either Integer1 is greater than 15 or Integer2 is less than 20.
The lines of code will be:
If Integer1 > 15 Or Integer2 < 20 Then
MsgBox "Integer1 is Greater than 15 or Integer2 is Less than 20."
End If
If you run this code, it’ll show the message “Integer 1 is Greater than 15 or Integer2 is Less than 20” because at least one of the conditions is True.
Integer1 (12) isn’t greater than 15, but Integer2 (17) is less than 20.
But if you run the same statement with AND condition, that is:
If Integer1 > 15 Or Integer2 < 20 Then
MsgBox "Integer1 is Greater than 15 or Integer2 is Less than 20."
End If
It’ll not show the message, because both the conditions aren’t True.
Note: An OR condition is True if at least one of the conditions is True.
On the other hand, an AND condition is True if all the conditions are True.
Read More: VBA IF Statement with Multiple Conditions in Excel
2. If Statement in Excel VBA: If and ElseIf Statements
You can include an ElseIf statement with the If statement, which attaches a condition that the code will check if it doesn’t fulfill the condition with the If statement.
For example, let’s check Integer1 is greater than 20 or not. If it is, then a message box will display it.
But if it’s not, then we’ll check whether it’s greater than 10 or not. If it’s, a message box will display it.
The lines of the VBA code will be:
If Integer1 > 20 Then
MsgBox "Integer1 is Greater than 20."
ElseIf Integer1 > 10 Then
MsgBox "Integer1 is Greater than 10."
End If
Run the code, and it’ll display “Integer1 is Greater than 10.”
Note: You can use as many ElseIf conditions as you wish, there’s no limit.
See below. Here I’ve used 3 ElseIf conditions.
Read More: Excel VBA: If Statement Based on Cell Value
3. If Statement in Excel VBA: If, ElseIf, and Else Statements
Finally, you can use an Else statement to end your if statement.
The code will first check the If condition.
If it doesn’t fulfill the condition, then it will check the ElseIf conditions.
If it doesn’t fulfill the ElseIf conditions too, then it’ll execute the instructions given in the Else condition.
Let’s first check whether Integer1 is greater than 30 or not. If it is, then we’ll display it.
But if it’s not then we’ll use an ElseIf condition to check whether it’s greater than 20 or not. If it’s, then we’ll display it.
Finally, if it’s not greater than even 20, then we’ll use an Else statement to display “No Condition is Fulfilled.”
The lines of the VBA code will be:
If Integer1 > 30 Then
MsgBox "Integer1 is Greater than 40."
ElseIf Integer1 > 20 Then
MsgBox "Integer1 is Greater than 30."
Else
MsgBox "No Condition is Fulfilled."
End If
Run the code, and it’ll display “No Condition is Fulfilled.”.
Note: You can use only one Else statement within an If block. Also, you can use an Else statement directly after the If statement, using no ElseIf statement.
Read More: Excel VBA Nested If Then Else in a For Next Loop
Things To Remember
If you have a bunch of conditions other than just one or two, you better use the Select Case of VBA rather than using a lot of If, ElseIf, and Else statements.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
Conclusion
Using these methods, you can use the If statement of VBA, along with the ElseIf and Else statements. Do you have any questions? Feel free to ask us.
Related Article
- Excel VBA: If Cell Contains Value Then Return a Specified Output
- Excel VBA: Combining If with And for Multiple Conditions
- Excel VBA: Combined If and Or
- Excel VBA to Check If String Contains Letters
- Else Without If Error VBA in Excel
- Excel VBA: Check If a File Exists or Not
- Excel VBA: Check If a Sheet Exists
