How to use the VBA IF Statement: Step-by-Step (2024)

IF statements are some of the most foundational concepts in programming.

They serve as the basis for many more complicated functions—if you understand them, you’re well on your way to becoming a VBA expert 🧑‍🎓

But if you don’t have programming experience, understanding how IF statements work can be difficult. Let’s start at the very beginning.

A quick note on IF statement terminology in this guide

In this post, we’ll be using a few different ways of talking about IF statements. Here’s a quick breakdown:

A conditional statement contains conditions and statements and can consist of multiple IF, THEN, ELSEIF, and ELSE clauses. In some cases, I’ll refer to them by the clauses that they contain.

For example, an IF THEN statement contains both an IF and a THEN clause.

A clause is the equivalent of a sentence fragment (it can be one IF, one THEN, one ELSE, and so on). Because it’s a clause, it can’t stand on its own.

When talking about a statement or a clause, we’ll use capital letters, as we have above. If we’re talking about a concept or idea (like in this sentence), “if,” “then,” and other words will be in lowercase.

These conventions are fairly standard, but there might be some variation. Fortunately, you can usually pick up on what someone’s talking about with context clues.

You’ll see what I mean as we go along.

What is the VBA IF statement?

The IF statement is a way to make your VBA code decide what course of action to take. If the criteria of the IF statement are met, something happens (then…). If not, something else happens (else…).

A conditional statement is actually a three-part statement. There’s an IF clause, a THEN clause, and an ELSE clause.

Outside of programming, you use this kind of statement all the time. You say “if it’s nice, then I’ll go to the beach.” The ELSE part is implied: “else, I will not.” You can think of ELSE as “if not” (but we’ll be talking about ELSE because you could make an if-not-type statement in a different way).

Programming uses similar conventions.

In a conditional statement, you’ll specify a condition (that’s the IF), what happens if the condition is met (THEN), and what happens if it’s not (ELSE).

So the normal VBA IF statement is actually an IF THEN ELSE statement.

Let’s take a look at how you put these clauses together in the VBA code.

IF THEN statements in VBA

Creating an IF THEN statement in VBA is simple. Here’s the syntax:

If [condition] Then
  [statements]

Replace [condition] with the condition you want to assess, and [statements] with whatever you want Excel to do if the condition is true.

It’s important to note that the condition has to produce a TRUE (1) or FALSE (0) result.

Let’s take a look at a simple example.

We’ll use VBA to look at a cell and tell us if the number is even. Here’s the VBA we’ll use:

Sub evenOdd ()
 If Range("A1") Mod 2 = 0 Then
   Msgbox "A1 is even."
 End If
End Sub

The above code tells Excel VBA to look at cell A1. If the number contained in the cell has a remainder of zero when divided by two, Excel displays a message box telling us the number is even.

Of course, you can use IF THEN clauses for much more complicated operations than displaying short messages. But this is a good way to see it in action.

It’s important to note that the above example of the IF statement shows it has a single-line syntax. That means the IF and the THEN words must always be placed on the same line.

IF-THEN-ELSE statements in VBA

In our discussion of IF statements, we talked about three clauses: if, then, and else. In VBA, it’s easy to include an ELSE statement. Just write “ELSE” and the statement.

Let’s use that to make our previous script a little more useful. In the previous version, we’d only get a message if the value in A1 was even.

Here’s how we’d change that using an ELSE clause:

Sub evenOdd ()
  If Range("A1") Mod 2 = 0 Then
    Msgbox "A1 is even."
    Else
      Msgbox "A1 is odd."
 End If
End Sub

Now when you run the script, you’ll get a message no matter what the numerical value in A1 is.

Again, remember that you can use a more complicated IF THEN ELSE statement than simply displaying a message box. Here’s how we’d add a new message to a cell using the VBA code:

Sub evenOddWrite ()
  If Range("A1") Mod 2 = 0 Then
    Range("B1").Value = "A1 + 2 ="
    Range("C1").Value = A1+2
    Else
      Range("B1").Value = "A1 x 2 ="
      Range("C1").Value = A1*2
    End If
End Sub

ELSE IF statements in VBA

In many cases, you’ll want more than one condition to apply.

Our example of going to the beach serves well: we might say that if it’s over 80 degrees outside, we’ll go to the beach. If it’s over 70 degrees, we’ll go to the park. If it’s over 60 degrees, we’ll go for a hike. If it’s not any of those, we’ll go to a movie.

In VBA, you can create this sort of decision-making process with the ELSE IF statement.

Let’s expand on our previous Excel VBA code:

Sub evenOddZero ()
  If Range("A1") = 0 Then
    Msgbox "A1 is 0."
    Elseif Range("A1") Mod 2 = 0 Then
      Msgbox "A1 is even."
    Else
      Msgbox "A1 is odd."
  End If
End Sub

In contrast to the VBA IF statement from earlier, this syntax must be split into more than one line.

Now, expanding on the above example, when we set A1 equal to zero, we’ll get a message telling us so.

An important thing to note about multiple conditionals: VBA goes through them one at a time, so the order in which you write them matters.

Let’s say we use the same code but write it differently:

Sub evenOdd ()
  If Range("A1") Mod 2 = 0 Then
    Msgbox "A1 is even."
    Elseif Range ("A1") Mod 2 = 1 Then
      Msgbox "A1 is odd."
    Else
      Msgbox "A1 is 0."
  End If
End Sub

In this case, we’ll never get a message box saying that A1 is equal to zero. Because zero is an even number, Excel will stop looking at conditionals after it sees that the number in A1 is even.

A common error

When you program in VBA, you’ll get error messages. You may get a lot of them. And you’ll need to learn to deal with them.

One of the most common errors you’ll get is this:

Excel is telling us that there’s a compile error and “Block If without End If.”

It’s important to include “End If,” because Excel doesn’t automatically know when your conditional statement has ended.

Just write “End If” at the end of the conditional statement.

You may run into other errors, but this is a common one and very easy to fix if you know what to look for.

That’s it – Now what?

The examples in this post are useful, but if you’re going to create useful VBA scripts, you’ll need to learn to think about your Excel spreadsheet in a new way.

And that way includes a lot of IF, THEN, and ELSE clauses. It takes practice, but if you stick with it, you’ll see those conditionals can help you accomplish just about anything.

If you want to learn more about the IF statement (and other VBA concepts), you can enroll in my free Excel VBA training here.

Other resources

The VBA IF statement and IF THEN ELSE statement are extremely important when building anything complex. Also, you’ll find that you can combine them with the AND and OR VBA functions (and other operators) to dive even deeper into Excel VBA logic.

Also, when dealing with multiple conditions, you might want to check out ‘Select case’.