VBA IF Else Statement

What is IF Else Statement in VBA?

The If Else or If Then Else statement of VBA executes a set of instructions depending on whether the specified condition is met or not. If the condition (logical test) is true, one set of actions is performed. However, if the condition is false, an alternative set of actions is performed.

For example, in a call center, an employee is given a target of making 50 outbound calls in a day. If he/she achieves the target, a sum of money is paid as an incentive. However, if the target is not achieved, no incentive is paid.

The condition here is x>=50. So, if x is greater than or equal to 50, an incentive is paid. Otherwise, it is not paid.

Hence, if the condition is true, the output should be the message “incentive paid.” Otherwise, the output should be the message “incentive not paid.” Rather than checking the number of calls made by each employee manually, one can use the If Else statement of VBA.

A condition is an expression that evaluates to true or false. It is placed between the If and Then keywords. To create a condition, the conditional operators like “equal to” (=), “not equal to” (<>), “greater than” (>), “less than” (<), “less than or equal to” (<=), and “greater than or equal to” (>=) are used.

The purpose of using the If Else statement is to evaluate a condition and perform an action accordingly. If the condition is true, the code following the If Then keywords is run. However, if the condition is false, the code following the Else keyword is run.

The If Else statement works in VBA and not in Excel. However, the logical test of the If Else statement works similar to that of the IF function of Excel.

The Syntax of the VBA If Else Statement

The syntax of the VBA If Else statement is stated as follows:

IF <Logical Test> Then
    If the Logical Test is TRUE 
Else
    If the Logical Test is FALSE
End IF

The condition (logical test) is required, while the Else statement is optional.

The Working of the VBA If Else Statement

First, the VBA If Else statement checks the supplied logical test. If the supplied logical test is true, an action (task) is performed. However, if the test is false, an alternative action is performed. An action is performed by the execution of the code.

Let us understand the working of the If Else statement with the help of an example. We want to supply the condition 10>11 as the logical test.

If the given condition is true, the output should be the message “10 is greater.” However, if the condition is false, the output should be the message “10 is lesser.”

To write the code, the details are specified as follows:

1. Enter the task to be performed if the condition evaluates to true.
2. Enter the keyword Else followed by an alternative task to be performed. This alternative task will be performed if the condition evaluates to false.

The code for the given example is written as follows:

Sub IF_Else_Example1()

    If 10 > 11 Then
       MsgBox "10 is greater"
    Else
       MsgBox "10 is lesser"
    End If

End Sub

Execute the code and the result appears as shown in the succeeding image. Since the condition (10>11) evaluates to false, the message following the Else statement is returned. Hence, the output is “10 is lesser.”

It must be noted that the preceding code returns at least one of the specified messages. This implies that either the If block or the Else block is executed at a given time. Both these blocks cannot be executed together in a single run.

VBA IF Else Example

The following table shows the cost price (in $ in column B) of seven products (column A). We want to find the status (column C) of each product based on its price. For this, the criteria are mentioned as follows:

• If the cost price>$50, the status should be “expensive.”
• If the cost price<$50, the status should be “not expensive.”

The logical test is cost price>50. If this condition is true, the output in column C should be “expensive.” However, if the given condition is false, the alternative result should be “not expensive.”

Prior to writing the code, copy and paste the preceding table to an Excel worksheet. The steps for writing the code are listed as follows:

Step 1: Start the sub procedure.

Sub IF_ELSE_Example2()

End Sub

Step 2: Declare the variable “k” as an integer. So, the data type is integers.

Dim k As Integer

Step 3: Use the For Next loop to loop through the given cells and perform tasks on all of them.

The For Next loop is used because the code needs to be executed a specific number of times. Moreover, multiple cell values need to be tested.

Since the test needs to be conducted on rows 2 to 8, the For Next loop begins from 2 and runs till 8. The same is shown in the following code and image.

For k = 2 To 8

Next k

Step 4: Enter the logical test within the For Next loop. For this, open the If statement and select the first cell with the help of the cells property. The cells(k, 2) implies row “k” and column 2.

The same is shown in the following code and image.

If Cells(k, 2).Value > 50 Then

Step 5: Enter the action to be performed following the If Then statement.

If the value in cell (k, 2) is greater than 50 (the condition evaluates to true), the output should be “expensive” in the adjacent cell of column C.

The same is shown in the following code and image.

Cells(k, 3).Value = "Expensive"

Step 6: Enter the Else statement. This will run if the logical test (cost price>50) evaluates to false. Going by the Else statement, the cells (k, 3) must display the message “not expensive” if the cost price is less than $50.

The same is shown in the following code and image.

Sub IF_ELSE_Example2()

    Dim k As Integer

    For k = 2 To 8
        If Cells(k, 2).Value > 50 Then
            Cells(k, 3).Value = "Expensive"
        Else
            Cells(k, 3).Value = "Not Expensive"
        End If
    Next k

End Sub

Step 7: Run the code and the output appears, as shown in the following image. The code loops through rows 2 to 8, tests every value of column B, and arrives at a result accordingly.

Hence, the logical test for cells B2, B4, B6, and B8 evaluates to true. So, the status in the corresponding cells of column C appears as “expensive.”

For the remaining cells of column C, the status appears as “not expensive.” These are the ones whose related cost price is less than $50.

Likewise, the If Else statement can be used to obtain different results.

Key Points

The important points associated with the If Else statements of VBA are listed as follows:

• The Else statement runs if the logical test evaluates to false.
• For testing multiple conditions, use the ElseIf or nested If Then Else statements.
• For looping through multiple objects (like cells) and performing tasks on each one of them, use the VBA loops.
• If the code is long and split into multiple lines, use the End If statement to terminate the If Then Else block.

Frequently Asked Questions

Recommended Articles

This has been a guide to VBA If Else. Here we discuss how to use the If Then Else Statement along with a practical example and downloadable Excel template. You may learn more about VBA from the following articles-

• How to Use Counter in VBA Excel?
• VBA Asc
• Boolean in Excel VBA
• VBA IIF
• VBA ByRef