excel tips

Chapter 7 – Conditional Statements

Chapter 7 – Conditional Statements

excel tips

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

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

VBA for Beginners

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:

VBA for Beginners

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:

VBA for Beginners

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:

VBA for Beginners

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

If Else Statement

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:

VBA for Beginners

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:

VBA for Beginners

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:

VBA for Beginners

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:

VBA for Beginners

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

If ElseIf Else Statement

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:

VBA for Beginners

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:

VBA for Beginners

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

Nested If Statement

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:

VBA for Beginners

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:
VBA for Beginners

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 Statement

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:

VBA for Beginners

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:

VBA for Beginners

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.

Author:
Excelsirji.com was started with the thought of making people experience easy while working with Excel, data analytics, vba, automations etc.

Leave a Reply

Your email address will not be published. Required fields are marked *

Are you a Data Enthusiast?

Join us for a ride on your data and automate your stuffs

Business Analytics
Data Analyst
Programming for Data Science
Predictive Analytics for Business