IF statements allow you to make decisions in your Macros and VBA in Excel.
An IF statement gives you the power to do different things in a macro depending on the outcome of a test or a condition, as it's called.
This is probably the most important thing you will learn when it comes to creating a macro and programming in general.
Below, I'll go over the syntax of an IF statement, show you how to build it step-by-step, and then give you some examples.
If..Then..ElseIf..Else Examples
This is the most basic form of the If statement.
If condition Then
Do something
End If
This is the template for the If statement and the main part of it is the condition. That part is where the "test" is made to determine if the code contained within the If statement, the part where it says Do something, will run.
Next, I'll give you a thorough step-by-step with more detail.
Steps to create the If statement:
If you turn the less than sign into a greater than sign in the condition for this If statement, the message box will not appear because 1 is not greater than 2. Download the sample file for this tutorial and try that out; playing around with this on your own is the fastest way to learn it.
This is a slightly more complex but immensely more useful version of the If statement.
This allows us to do something if the condition evaluates to True, and also to do something if it evaluates to False.
If condition Then
Do something.
Else
Do something else.
End If
This is exactly like the regular If syntax above except that we now have Else added in there. The Else is what allows us to do something ELSE if the condition evaluates to false.
We build this exactly like the If syntax above, look to that example for an in-depth explanation, except now it will look like this:
(note: I changed the less than sign in the condition to a greater than sign from the last example.)
Here, a condition is checked; if it evaluates to True, then the first section of code is run; if it evaluates to False, then the section of code between Else and End If will run.
This specific If statement code runs like this:
You can use whatever conditions you want and put whatever code you want within the If statement; this is just a simple example to help convey the key concepts.
If you run this macro, called If_Statement_2, you will see a message box that says Bye!.
Once again, download the sample file for this tutorial and play around with this code; that is the best way to learn.
This form of the If statement allows you to check the original condition and, if that evaluates to False, you can then check another condition to see if that one evaluates to True or not.
If condition Then
Do something.
ElseIf condition Then
Do something.
End If
This is like the last example except that now we have ElseIf instead of Else and we also have another condition to check.
If you don't understand how conditions work, please read the first example above, that covers it in more depth.
Here, we have the ability to check two conditions. If the first condition evaluates to False, the code moves to check the second condition.
Here is how this If statement runs:
This was a simple example and we of course knew the answers beforehand but, in a real situation, the conditions will include variables and we won't know the outcome of the test just by looking at the If statement.
You can have, basically, as many ElseIf parts to an If statement as you need. In fact, you can have so many that it becomes difficult to follow. However, you can only have one Else part to an If statement and that should always come at the end of it, after all ElseIf sections.
Here, I want to show you an example that combines all of the previous examples and then walk you through the logic.
If condition Then
Do something.
ElseIf condition Then
Do something.
Else
Do something else.
End If
Here is a real-world example:
I created a variable called A and I set it equal to some text.
The If statement checks if the variable A is equal to specific text.
Here is the logic of the If statement:
The benefit of this kind of If statement is that you can check for multiple conditions and multiple outcomes and, in the end, if no condition evaluates to True, we have a piece of code to run by default.
This is a great example to play around with in the attached file and the macro for it is called If_Statement_4.
You can also make a small version of an If statement that fills just a single line.
If condition Then code to run Else other code to run
Here is an example:
This says that if 1 is greater than 2, output a message box that says "oh no" but, if 1 is not greater than 2, output a message box that says "Ok".
In a one line If statement you can only use If and Else. However, you do not have to have an Else section; you can just check if a condition is True and only do something if that is the case; you do not need to have a section for the case where the condition evaluates to False.
That may sound confusing, but I'm just saying that the last example didn't have to include the Else section and it could have, instead, looked like this:
If 1 > 2 Then MsgBox "oh no"
The one line If statement is used when you need to check something simple and do something simple in response. It saves space but, other than that, it doesn't have much purpose; use it if you want to.
The If statement in VBA and Macros for Excel is one of the most important things to learn, if not the most important thing.
Using this allows you to add logic to your code; it allows your code to make decisions and to do different things based on the criteria that you set.
Download the sample file attached to this tutorial and play around with the examples in there. Compare them to this tutorial, make changes, and see how everything works.