VBA If..ElseIf..Else statement

In VBA, the if is a decision-making statement that is used to execute a block of code if a certain condition is true. For example, if you have a message box in the application and the user is presented with the “Yes, No, and Cancel” options.

You may execute different actions based on the user’s selection upon selecting Yes, No, or Cancel. By using If statement, you may capture the user’s option and evaluate in the If..ElseIf..Then..Else statements and execute different code for each case.

See the next section for learning how to use the If, ElseIf..Else statements, followed by examples including using if statement with Microsoft Excel.

Structure of VBA If statements

Following is the general syntax of using If, Else If, and Else VBA statements.

Using a single line:

If condition Then [ statements_to_be_executed] [ Else [ else_statements_to_Execute ] ]

• In single-line syntax, you have two separate blocks of codes. One, if the expression is evaluated as true. In that case, the statements inside the if statement execute.
• If the condition is false, the statements in the Else part will execute.

Multi-line syntax:

• First of all, the expression is tested in the first If condition.
• If the condition was false at first if statement, the ElseIf part is tested. You may use multiple ElseIf statements if your application has more options.
• If all conditions are False, the statement(s) in the Else part will execute.

Let us now look at how to use the If..ElseIf..Else statements in VBA and excel.

First, a simple if statement example

Let me start with the basic example where just the If VBA statement is used without Else in a single line. A simple message box is displayed if the condition is True:

As you run this code, a message box should appear because of the value of variable numx = 15.

Using If with Else statement in a single line

Let me now add the Else VBA statement in the above example in single line If statement. The variable value is now set as 20, so the condition becomes false and Else part should execute. Have a look at the code and output:

You can see, the Else part message box displayed as the condition was false.

Using ElseIf statement with If..Else example

Let us move ahead by using the ElseIf statement. The scenario is, we have a three buttons dialog box with Yes/No and Cancel options. As a button is pressed by the user, we will get the button value and display a respective message to the user that tells which button was pressed.

For that, the VBA Else If statement is as used as follows:

You see, how btnVal variable is evaluated in the If and ElseIf statements and then we displayed the respective message to the user by another dialog.

Using If..ElseIf..Else with excel

In this example, the If..ElseIf..Else statements are used with Microsoft Excel. For that, an ActiveX button is placed in the worksheet. In the click event of the button, the code with If..ElseIf..Else is written to check the value in cell A1.

Upon clicking the button, If the value in A1 is 0, the B1 cell will be updated by “Sunday” text. Similarly, 1 for Monday, 2 for Tuesday, and so on.

The example code:

The result:

An example of Excel VBA If..Else with And operator

If you have multiple expressions to check in the If statement and all have to be True in order to execute the code, you may do this by using the ‘And’ operator. By that, you may use two or more expressions. See the following example:

You noticed I entered Green in A1 and White in B1 and the output is written as Yellow in C1. If any of the values did not match (A1 or B1) then the if part would have been evaluated as False and Else was updated the C1 with Black. See the graphic below:

An example of If..Else with Or operator

If you require to evaluate multiple expressions and execute the If block if any of the expressions is True, then you may use the ‘Or’ operator. This is unlike the ‘And’ operator where all expressions have to be True.

To make things clearer, I am amending the above example just a little bit – replacing ‘And’ by ‘Or’ operator in the If statement. In the excel cells, the same text is given as in the second graphic i.e. A1=Green and B1=Red and see the output:

An example of If with Not operator

See the use of Not operator in the example below:

The result as I entered Yellow:

As I entered the Red, the outcome:

This is because the if condition became false.