If Statement in Excel VBA

Get FREE Advanced Excel Exercises with Solutions!

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.

Code to Use the If Statement in VBA in Excel

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 If

If 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.]

IF Statement in Excel VBA

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

Code with the If Statement in VBA in Excel

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.

Output of the If Statement in VBA in Excel

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

Code with the If Statement in VBA in Excel

Run the code, and it’ll display “Integer1 is Greater than 10.

Output of the If Statement in VBA in Excel

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

Code with the If Statement in VBA in Excel

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.

Tags:

Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo