Chapter 7 – Conditional Statements

In this chapter, we will learn Conditional Statements commonly used in VBA. We will also see practical example of each statement.

• If Statement
• If Else Statement
• If ElseIf Else Statement
• Nested If Statement
• Select Statement

Single line If Statement

If --Condition-- = True Then --Do something--

Example 1:

Code:

Sub If_Example_1()

    If Sheet1.Range("B5").Value > 33 Then Sheet1.Range("C5").Value = "Pass"

End Sub

Result:

Explanation: Since the value in cell B5 is greater than 33 hence code changed the value of cell C5 to Pass.

Example 2:

Code:

Sub If_Example_2()

    If Sheet1.Range("B5").Value = "Saturday" Or Sheet1.Range("B5").Value = "Sunday" Then Sheet1.Range("C5").Value = "Yes"

End Sub

Result:

Explanation: Since the value of cell B5 is Sunday hence code changed the value of cell C5 to ‘Yes’

If Block Statement

If --Condition-- = True Then
--Do something--
End If

Example 1:

Code:

Sub If_Block_Example_1()

    If Sheet1.Range("B5").Value > 33 Then
        MsgBox "You are Pass"
    End If

End Sub

Result:

Explanation: Since the value in cell B5 is greater than 33 hence message box is displayed to user.

Example 2:

Code:

Sub If_Block_Example_2()

    If Sheet1.Range("B5").Value = "Saturday" Or Sheet1.Range("B5").Value = "Sunday" Then
        MsgBox "Today is holiday"
    End If

End Sub

Result:

Explanation: Since the value of cell B5 is Sunday hence message box is displayed to user.

Single Line If Else Statement

If --Condition-- = True Then --Do something-- Else --Do something--

Example 1:

Code:

Sub If_Else_Example_1()

    If Sheet1.Range("B5").Value > 33 Then Sheet1.Range("C5").Value = "Pass" Else Sheet1.Range("C5").Value = "Fail"

End Sub

Result:

Explanation: Since the value in cell B5 is less than 33 hence code changed the value of cell C5 to Fail.

Example 2:

Code:

Sub If_Else_Example_2()

    If Sheet1.Range("B5").Value = "Saturday" Or Sheet1.Range("B5").Value = "Sunday" Then Sheet1.Range("C5").Value = "Yes" Else Sheet1.Range("C5").Value = "No"

End Sub

Result:

Explanation: Since the value of cell B5 is Monday hence the code changed the value of cell C5 to No.

If Else Block Statement

If --Condition-- = True Then
	--Do something--
Else
	--Do something--
End If

Example 1:

Code:

Sub If_Else_Block_Example_1()

    If Sheet1.Range("B5").Value > 33 Then
        MsgBox "You are Pass"
    Else
        MsgBox "You are Fail"
    End If

End Sub

Result:

Explanation: Since the value in cell B5 is less than 33 hence Fail message box is displayed to user.

Example 2:

Code:

Sub If_Else_Block_Example_2()

    If Sheet1.Range("B5").Value = "Saturday" Or Sheet1.Range("B5").Value = "Sunday" Then
        MsgBox "Today is holiday"
    Else
        MsgBox "Today is working day"
    End If

End Sub

Result:

Explanation: Since the value of cell B5 is Monday hence working day message box is displayed to the user.

If --Condition-- = True Then

--Do something--

ElseIf --Condition-- = True Then

--Do something--

Else

--Do something--

End If

Example 1:

Code:

Sub If_ElseIf_Else_Example_1()

    If Sheet1.Range("B5").Value = "Saturday" Then
        MsgBox "Today is holiday"
    ElseIf Sheet1.Range("B5").Value = "Sunday" Then
        MsgBox "Today is holiday"
    Else
        MsgBox "Today is working day"
    End If

End Sub

Result:

Explanation: Since the value of cell B5 is Sunday hence code executed second condition and displayed holiday message box.

Example 2:

Code:

Sub If_ElseIf_Else_Example_2()

    If Sheet1.Range("B5").Value < 33 Then
        MsgBox "You are fail"
    ElseIf Sheet1.Range("B5").Value < 75 Then
        MsgBox "You are pass"
    Else
        MsgBox "You got distinction"
    End If

End Sub

Result:

Explanation: Since the value of cell B5 is 77 hence code executed third condition and displayed distinction message box to the user.

If --Condition-- = True Then
        If --Condition-- = True Then
                If --Condition-- = True Then
                        --Do something--
                ElseIf --Condition-- = True Then
                        --Do something--
                End If
        End If
End If

Example 1:

Code:

Sub Nested_If_Example_1()

    If Sheet1.Range("C5").Value > 33 Then
        If Sheet1.Range("C6").Value > 33 Then
            If Sheet1.Range("C7").Value > 33 Then
                Sheet1.Range("C8").Value = "Pass"
            Else
                Sheet1.Range("C8").Value = "Fail"
            End If
        Else
            Sheet1.Range("C8").Value = "Fail"
        End If
    Else
        Sheet1.Range("C8").Value = "Fail"
    End If

End Sub

Result:

Explanation: Since all three conditions are true hence code changed cell C8 to Pass.

Example 2:

Code:

Sub Nested_If_Example_2()

    If Sheet1.Range("B5").Value < 18 Then
        If Sheet1.Range("C5").Value = "Male" Then
            MsgBox "You are a boy"
        Else
            MsgBox "You are a girl"
        End If
    ElseIf Sheet1.Range("B5").Value < 60 Then
        If Sheet1.Range("C5").Value = "Male" Then
            MsgBox "You are a man"
        Else
            MsgBox "You are a woman"
        End If
    Else
        MsgBox "You are senior citizen"
    End If

End Sub

Result:

Explanation: Since the value of cell B5 is 30 hence the code entered into ElseIf condition. The value of cell C5 is Male hence “You are a man” message box is displayed to the user.

Select Case expression
        Case condition1
                --Do something--
        Case condition2
                --Do something--
        Case conditionN
                --Do something--
        Case Else
                --Do something--
End Select

Example 1:

Code:

Sub Select_Example_1()

    Select Case Sheet1.Range("B5").Value
        Case 0 To 33
            MsgBox "You are fail"
        Case 34 To 75
            MsgBox "You are pass"
        Case Else
            MsgBox "You got distinction"
    End Select

End Sub

Result:

Explanation: Since the value of cell B5 is 60 hence “You are pass” message box is displayed to user.

Example 2:

Code:

Sub Select_Example_2()

    Select Case Sheet1.Range("B5").Value
        Case Is < 18
            If Sheet1.Range("C5").Value = "Male" Then
                MsgBox "You are a boy"
            Else
                MsgBox "You are a girl"
            End If
        Case Is < 60
            If Sheet1.Range("C5").Value = "Male" Then
                MsgBox "You are a man"
            Else
                MsgBox "You are a woman"
            End If
        Case Else
            MsgBox "You are senior citizen"
    End Select

End Sub

Result:

Explanation: Since the value of cell B5 is 15 hence code entered into first case block. Cell C5 is Female hence "You are a girl" message box is displayed to the user.