VBA If..ElseIf..Else statement

Structure of VBA If statements

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

Using a single line:

• In single-line syntax, you have two separate blocks of code. 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:

If condition [ Then ] 

    [ statements_to_Execute ] 

[ ElseIf elseifcondition [ Then ] 

    [ elseif_statements_to_execute ] ] 

[ Else 

    [ else_statements_to_Execute] ] 

End If

• 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:

Private Sub if_exmamples()

'Single line if statement

   numx = 15


   If numx = 15 Then MsgBox "The value of numx=15: True"

End Sub

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 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:

Private Sub if_exmamples()

'Single line if with Else Statement

   numx = 20

   If numx = 15 Then MsgBox "The condition is True" Else MsgBox "The value is not = 15, so False!"

End Sub

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 that we have a three-button 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 used as follows:

Function ElseIf_exmample()

   btnVal = MsgBox("Press a button and program will tell which button was pressed?", 3, "Demo of If..ElseIf..Else")

   If btnVal = 6 Then

      MsgBox "User pressed Yes!"

   ElseIf btnVal = 7 Then

      MsgBox "User Pressed No!"

   Else

      MsgBox "User Pressed Cancel!"

   End If

End Function

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:

Private Sub dayName_Click()

Dim readValue As Integer

    readValue = Range("A1").Value

    If readValue = 0 Then

        Range("B1").Value = "Sunday"

    ElseIf readValue = 1 Then

        Range("B1").Value = "Monday"

    ElseIf readValue = 2 Then

        Range("B1").Value = "Tuesday"

    ElseIf readValue = 3 Then

        Range("B1").Value = "Wednesday"

    ElseIf readValue = 4 Then

        Range("B1").Value = "Thursday"

    ElseIf readValue = 5 Then

        Range("B1").Value = "Friday"

    Else

        Range("B1").Value = "Saturday"

    End If

End Sub

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:

Private Sub if_and_Click()

Dim color1 As String, color2 As String

    color1 = Range("A1").Value

    color2 = Range("B1").Value

    If color1 = "Green" And color2 = "White" Then

       Range("C1").Value = "Yellow"

    Else

        Range("C1").Value = "Black"

    End If



End Sub

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 the ‘And’ with ‘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:

Private Sub if_or_Click()

Dim color1 As String, color2 As String



    color1 = Range("A1").Value

    color2 = Range("B1").Value

    If color1 = "Green" Or color2 = "White" Then

       Range("C1").Value = "Yellow"

    Else

        Range("C1").Value = "Black"

    End If

End Sub

An example of If with Not operator

See the use of the Not operator in the example below:

Private Sub if_not_Click()

Dim color1 As String



    color1 = Range("A1").Value

    If Not color1 = "Red" Then

       Range("B1").Value = "Brown"

    Else

        Range("B1").Value = "Green"

    End If

End Sub

The result as I entered Yellow:

As I entered the Red, the outcome:

This is because the if condition became false.