VBA IF Statement in Excel Macros

Add to Favorites
Author:

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.

Sections:

If Syntax

If..Then..Else Syntax

If..Then..ElseIf Syntax

If..Then..ElseIf..Else Examples

1 Line IF Statement

Notes

If Syntax

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:

  1. Type If
    8b1d7a2de67cb5800cdcec4970973586.jpg
  2. Input a condition. A condition is something that must evaluate to True or False.
    Conditions are how the If statement knows what to do; if the condition evaluates to True, the If statement does something; if it doesn't, nothing happens, at least for now - the next example will show you how to do something when the condition evaluates to False.
    Here is a simple condition:
    0a358fadab2706c6dedd632339cc7622.jpg
    I highlighted the condition so you can see it clearly.
    This checks if 1 is less than 2; it's a very simply check and we know for sure that it will evaluate to True.
    You can put variables in the condition, numbers, text, and also functions; the only thing that matters is that you have something that will evaluate to either True or False.
    You can learn more about making conditions by reading this tutorial on the comparison operators for VBA, which are things like the > and < sign and you can learn slightly more complex concepts in this tutorial for logical operators in VBA.
  3. Now that we have a condition, hit the space bar and then type Then and hit the Enter key.
    6fbc2e88044500b4b29eccc638058b70.jpg
    We now have the first part of the If statement.
  4. Hit Enter a couple more times and then type End If.
    caf8efc5a3a20d9abda6f5365650b76a.jpg
    The End If is what closes the If statement.
  5. In-between the first line that we made and End If, we input the code that we want to run if the condition we created in Step 2 evaluates to True.
    690c68849c0fe59983b9daf4886b2ed2.jpg
    Here, I set a message box to appear that says "HI!"
    Since the condition for this macro will evaluate to True, because 1 is less than 2, our If statement will execute the code contained within it and the message box will appear.
  6. Run the macro in Excel and you will see this:
    ea8faf007d173666eb7130401394a526.jpg

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.

If..Then..Else Syntax

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:

7a37d90c8313b1e56ac5d01f06c013b0.jpg

(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:

  1. If 1 is greater than 2 then run the first section of code, which outputs a message box that says "HI!"
  2. If 1 is NOT greater than 2 then run the section of code that comes after Else, which, in this case, outputs a message box that says "Bye!"

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.

If..Then..ElseIf Syntax

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.

ec4c6bae76fe8eea36da10de0d3aa044.jpg

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:

  1. If 1 is greater than 2, output a message box that says "HI!"
  2. If 1 is not greater than 2, go to the next check (the ElseIf) and check if 1 is greater than 0; if it is, output a message box that says "Bye!"
  3. If 1 is not greater than 0, do nothing, because we have reached the end of the If statement.

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.

If..Then..ElseIf..Else Examples

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:

8b646efcc6252f2ed2e9fcb23d9d7a1d.jpg

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:

  1. Check if A equals the text "tree" and if it does, output a message box that says "A equals tree." If A does not equal "tree" then move on.
  2. The ElseIf line then checks if A is equal to the text "bee" and if it is, output a message box that says "A equals bee." If A does not equal "bee" then move on to the next part of the If statement.
  3. The Else line is the last section of the If statement. If no condition in the If statement evaluated to True, then the code contained within the Else section will run. In this case, that means that a message box saying "A equals something else." will appear.

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.

1 Line IF Statement

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:

1bc2aa1c8cc63af1d06b8605fa8e2e8c.jpg

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.

Notes

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.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Nest IF Statements in Excel
Tutorial: How to nest IF statements inside of each other in Excel so that you can make more complex...
Automatically Lock Certain Cells in Excel using a Macro
Tutorial: This macro allows you to have a cell automatically locked after a user enters something in...
IFS Function - Multiple IF Statements in One
Tutorial: Easily create nested IF statements using the new IFS() function for Excel! The IFS() func...
SWITCH() Function in Excel and 3 Alternatives
Tutorial: How to use the SWITCH() function for Excel. SWITCH() allows you to match a series of valu...
Make Better IF Statements in Excel - Learn to Convert Complex Formulas into True False Values
Tutorial: How to change any formula to make it return a TRUE or FALSE value - you need this for usi...
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
Macro: This free Excel macro illustrates all of the possible parameters and arguments that yo...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course