How to use If-Else-If Statement in Excel VBA?

In this article, we are going to look into how to use the If Else If statement in Excel VBA using a suitable example.

Implementation :

In the Microsoft Excel tabs, select the Developer Tab. Initially, the Developer Tab may not be available.

The Developer Tab can be enabled easily by a two-step process :

Right-click on any of the existing tabs at the top of the Excel window.
Now select Customize the Ribbon from the pop-down menu.

In the Excel Options Box, check the box Developer to enable it and click on OK.

Now, the Developer Tab is visible.

Now click on the Visual Basic option in the Developer tab and make a new module to write the program using the Select Case statement.

Developer  -> Visual Basic -> Tools -> Macros

Now create a Macro and give any suitable name.

This will open the Editor window where can write the code.

IF ELSE IF statement

The syntax is :

If condition1/expression1 Then
Code Block 1
Else If condition2/expression2 Then
   Code Block 2
Else 
    Code Block 3
End If

In this initially, the If condition is executed and if it is TRUE then the code block 1 will execute and the program terminates. Now, if condition 1 becomes FALSE then condition 2 inside Else IF will work, and if it is TRUE code block 2 will execute and the program terminates. If both the previous conditions are false then Else will work and Code Block 3 will be executed.

Flow Diagram :

Example : Suppose in a company we need to find the maximum salary of three departments : HR, Finance and IT.

Code :

Sub Find_Max()
'Declaring the variables
Dim HR_Sal As Integer
Dim Fin_Sal As Integer
Dim IT_Sal As Integer
'Asking the users to enter the salary
HR_Sal = InputBox("Enter HR department salary:")
Fin_Sal = InputBox("Enter Finance department salary:")
IT_Sal = InputBox("Enter IT department salary:")
'Conditions to find the maximum salary of three departments
If HR_Sal > Fin_Sal And HR_Sal > IT_Sal Then
    MsgBox "HR department has maximum salary"
'The previous condition fails then the below statement executes
ElseIf Fin_Sal > IT_Sal Then
    MsgBox "Finance department has maximum salary"
'The previous two conditions fail then the below statement executes
Else
    MsgBox "IT department has maximum salary"
End If
End Sub

Here, first we check if HR Salary is more than that of the other two departments. If it is TRUE then definitely the HR department has maximum salary and the code inside IF executes. If this is not TRUE, it means HR department salary is not maximum and next in Else If condition we need to compare between IT and Finance and see which is maximum. In this way If Else If executes. If none of the previous conditions are TRUE, then the code inside Else will be executed.

AND is a logical operator which gives the result as TRUE value if and only if both the expressions associated with this operator are TRUE.

Case 1 :

Inputs by the user are :

The output is :

Case 2 :

Inputs by the user are :

The output is :

Case 3 :

Inputs by the user are :

The output is :

Article Tags :

Excel