How to Use IF Statements in VBA

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

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.

What are IF statements?

A note on nomenclature

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, like we have above. If we’re talking about a concept or idea (like in this sentence), “if,” “then,” and other words will be in lower case.

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.

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).

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

IF-THEN statements in VBA

Creating an IF-THEN clause 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

This tells Excel 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.

vba-even-msgbox

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.

Try creating an IF-THEN statement like the one above, and change some of the parameters to see how it works.

You’ll also probably get a few errors. Try to fix them on your own to learn even more about the syntax!

Kasper Langmann, Co-founder of Spreadsheeto

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 ease 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.

vba-odd-msgbox

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

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

Run the above VBA code in your spreadsheet and try a few different values in A1. What happens?

Kasper Langmann, Co-founder of Spreadsheeto

ELSEIF 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 ELSEIF clause.

Let’s expand on our previous script:

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

Now, when we set A1 equal to zero, we’ll get a message telling us so.

vba-zero-msgbox

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 had written our script this way:

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.

This example is a bit contrived, but you get the idea.

Remember to check to see if the order in which your conditional clauses are listed will get you the effect you’re looking for.

Kasper Langmann, Co-founder of Spreadsheeto

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:

if-without-end-if

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.

Using conditionals to your advantage

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 that conditionals can help you accomplish just about anything.

Play around with conditional statements to get a feel for how they work. When you need to write one, you’ll be glad that you have a bit of experience.

2019-03-29T09:10:53+00:00