VBA If Then Else (complete guide)

Sometimes you don’t want every part of your code to be executed as you run your macro. In almost every computer language. In VBA, you can find IF .. ELSEIF .. ELSE statement or SELECT .. CASE.

In this lesson, I’ll show you how you can use the IF statement in VBA.

If .. Then

You can use the If statement if you want to check a condition and execute that part of the code only if the condition is met. You can close the If statement with an End If line.

The condition (age >= 18) is met therefore the code between If and End If is executed and the message is displayed.

If you change the age variable to a number smaller than 18, Excel will check the condition, and because the condition is not met it won’t do anything.

A single line IF

In the previous example, I showed you that you have to end your if statement with the End If line. That’s true unless you write your statement inside a single line.

Let’s modify our example a bit.

Single line statement can be more readable if you use multiple Ifs with a short single line code to execute.

Multiple Ifs

You can use multiple Ifs if you want to execute multiple parts of code, and each If has to be closed with End If. Here’s what I mean.

In this case, both conditions are met, therefore Excel will display both messages. If you have multiple Ifs, it’s a good idea to use the SELECT .. CASE statement.

If .. Then .. Else

The If statement allows us to check a single condition. If the condition is met (returns TRUE), the particular code is executed.

But what if the statement returns FALSE and, in this case, you want to execute a different part of a code?

In this case, you can use ELSE statement. This part of code will execute if the condition is not met. Let’s take a look.

Excel checks whether the condition is met. In our case, 16 >= 18 returns FALSE, therefore program will execute code that is inside the Else statement.

If .. Then .. ElseIf

So far you’ve learned about If and Else. In this part, you are going to learn about ElseIf, which allows creating more complicated conditions.

It works in the following way. First Excel checks the If condition. If the condition is not met (and only then) it checks the ElseIf condition. If the condition is still not met, it returns the Else statement. You can use multiple ElseIf statements, but only a single If or Else in a single if statement.

Let’s find out using the following example.

This code checks whether a person is underage. If the condition is met it returns the message. If the condition is not met if checks the next condition – if a person is over 65. If the condition returns TRUE it returns the next message. If both conditions are not met, it returns the last message inside Else.

IF with logical operators

NOT

In our example, we used the following condition:

age >= 18

If you want to select people who are younger than 18, you can use this statement: age < 18, or you can use negation.

With negation, our example looks like this.

In the above example, no code will be executed because it makes our condition negative.

After you changed our variable to be smaller than 18, the message is going to be displayed.

OR and AND

Besides NOT, you can also use AND and OR.

Let’s modify our example.

First, we are going to use AND.

In this example, both conditions have to return TRUE in order to display the message. In other words, the condition is met if the age variable is between 18 and 65.

Nesting IF and ELSEIF (intermediate)

You can find that one If the statement is nested inside another If statement. It’s not only possible, but you can find that this is a common practice.

I’ll illustrate it in the following example.

In the first If statement this code checks what kind of fruit you are buying. If there are other If statements inside, it checks how many fruits you are buying and gives you a discount.

The IIf function

There is no typo. There is a function in VBA, called IIf. This function is not as popular as the If statement, but it exists, so I decided that it should be mentioned.

It’s similar to ternary operators from other programming languages.

With this function, you can make your code even shorter than with single-line If statements.

This is our If .. Else example.

Let’s modify it in order to use the IIf function.

It’s going to return this message.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba