VBA IF Else Statement

Article byJeevan A Y
Reviewed byDheeraj Vaidya, CFA, FRM

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 ExcelIF Function Of ExcelIF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is “true” or “false”. It is a conditional function of Excel, which returns the result based on the fulfillment or non-fulfillment of the given criteria. read more.

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.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

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 testLogical TestA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical test.read more.

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
VBA If Else Example 1.1

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 1.2

VBA IF Else Example

You can download this VBA If Else Excel Template here – VBA If Else Excel Template

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.”
VBA If Else Example 2.1

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
VBA If Else Example 2.2

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

Dim k As Integer
VBA If Else Example 2.3

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
Example 2.4

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
Example 2.5

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"
Example 2.6

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
Example 2.7

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.

GIF

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:

Frequently Asked Questions

1. Define the If Else statement of VBA.

The If Else statement of VBA executes a group of statements depending on the fulfillment or non-fulfillment of a condition. This condition or logical test is entered between the If and the Then keywords of the code.

The condition is an expression that evaluates to either true or false. If the condition is met (true), the code following the If Then statements is executed. If the condition is not met (false), the code following the Else statement is executed.

The If Else statements are used to perform a set of actions subject to the condition specified by the user. It is possible to test more than one condition at a given time with the help of the ElseIf statements.

Note: The If and Else blocks cannot be executed simultaneously in a single run.

2. State the syntax of the If Else statements of VBA.

There are two syntaxes of the If Else statements of VBA. These are stated as follows:

a. First syntax of the If Else statement

If condition [ Then ]
[ statements ]
Else
[ else statements ]

b. Second syntax of the If Else statement

If condition [ Then ]
[ statements ]
Else
[ else statements ]
End If

The condition in both the preceding syntaxes is required, while the Else statements are optional.

The difference between the two syntaxes is in the usage of the End If statement. The End If statement is used when the code extends into multiple lines.

The End If statement terminates the If Then Else block. In other words, the End If tells VBA where the execution of the If Then statements is to be ended.

3. How to use the If Else to test multiple conditions in VBA?

To test multiple conditions, the ElseIf statements are used. The syntax for the same is stated as follows:

If condition [ Then ]
[ statements ]
ElseIf elseifcondition [ Then ]
[ elseif statements ]
Else
[ else statements ]
End If

The ElseIf condition is required if ElseIf is being used. The ElseIf statements are optional. The preceding syntax works as follows:

a. First, the If condition is checked. If this condition is true, the code following the Then keyword is run. If this condition is false, the ElseIf condition is tested.
b. If the ElseIf condition is true, the ElseIf statements are executed. If the ElseIf condition is false, the statements following Else are run.
c. The execution of statements ends with the End If keyword.

Note: The user can add any number of ElseIf clauses to the If Then Else statement. However, an ElseIf clause must appear before an Else clause.

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-