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.

Sub SingleIf()
    Dim age As Integer
    age = 18
    
    If age >= 18 Then
        MsgBox "This person is an adult"
    End If
End Sub

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.

Sub SingleLineIf()
    Dim age As Integer
    age = 18
    
    If age >= 18 Then MsgBox "This person is an adult"
End Sub

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.

Sub MultipleIfs()
    Dim age As Integer
    age = 77
    
    If age >= 18 Then
        MsgBox "This person is an adult"
    End If
    If age >= 65 Then
        MsgBox "This person is a retiree"
    End If
End Sub

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.

Sub IfElse()
    Dim age As Integer
    age = 16
    
    If age >= 18 Then
        MsgBox "This person is an adult"
    Else
        MsgBox "This person is not an adult"
    End If
End Sub

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.

Sub IfElseIfElse()
    Dim age As Integer
    age = 11
    
    If age < 18 Then
        MsgBox "This person is underage"
    ElseIf age >= 65 Then
        MsgBox "This person is a retiree"
    Else
        MsgBox "This person is an employee"
    End If
End Sub

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.

Sub SingleIfNot()
    Dim age As Integer
    age = 18
    
    If Not age >= 18 Then MsgBox "This person is not an adult"
End Sub

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

Sub SingleIfNot2()
    Dim age As Integer
    age = 16
    
    If Not age >= 18 Then MsgBox "This person is not an adult"
End Sub

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.

Sub IfAnd()
    Dim age As Integer
    age = 56
    
    If age >= 18 And age <= 65 Then
        MsgBox "This person is an adult and employee"
    End If
End Sub

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.

Sub Fruits()
    Dim discount As Single
    Dim amount As Integer
    Dim fruit As String
    
    fruit = "apple"
    amount = 120
    
    If fruit = "banana" Then
        If amount > 100 Then
            discount = 0.1
        ElseIf amount > 10 Then
            discount = 0.05
        End If
        MsgBox "You bought " + CStr(amount) + " banana(s) with " + CStr(discount * 100) + "% discount"
    ElseIf fruit = "orange" Then
        If amount > 100 Then
            discount = 0.2
        ElseIf amount > 10 Then
            discount = 0.1
        End If
        MsgBox "You bought " + CStr(amount) + " orange(s) with " + CStr(discount * 100) + "% discount"
    ElseIf fruit = "apple" Then
        If amount > 100 Then
            discount = 0.4
        ElseIf amount > 10 Then
            discount = 0.2
        End If
        MsgBox "You bought " + CStr(amount) + " apple(s) with " + CStr(discount * 100) + "% discount"
    Else
        MsgBox "You didn't buy anything"
    End If
End Sub

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.

Sub IfElse()
    Dim age As Integer
    age = 16
    
    If age >= 18 Then
        MsgBox "This person is an adult"
    Else
        MsgBox "This person is a youngster"
    End If
End Sub

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

Sub IfElse()
    Dim age As Integer
    age = 16
    
    MsgBox IIf(age >= 18, "This person is an adult", "This person is a youngster")
End Sub

It’s going to return this message.

Posted in vba