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