Conditional Statements: If, Else-If, If-Then And Select Case

This tutorial explains various conditional statements in VBA such as If, Else-If, If-Then, Nested If, And Select Case with examples:

Often while designing a code we are bound to verify functionalities based on certain conditions and make decisions according to the output of the conditional statement.

In this tutorial, we will understand the various conditional statements, If. Then, If…Then…Else, ElseIf, Nested If and Select Case provided by VBA to perform comparisons.

=> Explore The Simple VBA Training Series Here

Conditional Statements In VBA

Before we proceed, let’s first understand what conditional statements are? and see why we use them?

Conditional Statements are used in programming languages to perform a set of actions depending on the condition specified by the programmer that evaluates to true or false.

These are mainly used to decide the execution flow. If the condition evaluates to true, execute a certain set of actions and if the condition evaluates to false then perform another set of actions.

Types Of Conditional Statements

IF Statements

If statements execute a set of actions depending on the condition. If the condition evaluates to true then the code mentioned in the If block will be executed.

Syntax:

If condition Then 
[statements]
End If

Condition: This is the required field. Based on the Boolean result of this condition the action will be performed. If the result is true then the statements in the If block will be executed.

If the condition is Null then it is treated as False.

Statements: This set of actions will be performed if the condition is true.

Flow Diagram

Once the code enters the conditional statement, the expression is verified. If the condition returns true then a set of activities defined under the if block is executed, but if the condition returns false then the program will not enter the if block.

Hence the if block statements are skipped and are never executed. The program directly goes to the line after the End If statement.

Note: To write VB Code Open Microsoft Excel (supported version Excel 2007,2010, 2013, 2016, 2019), navigate to Developer Tab -> Visual Basic (Alternatively use shortcut Alt+F11). In the VB editor, click on Insert -> Module.

Example:

Option Explicit
Sub ifExample()
Dim Obtained_Marks, Total_Marks As Integer
Obtained_Marks = 100
Total_Marks = 100
If (Obtained_Marks = Total_Marks) Then
MsgBox "Student obtained a perfect score"
End If
Debug.Print "Results Published"
End Sub

Note: To execute the code, click on F5 or hit the run button on the toolbar.

The output from the above code will be a msgbox as shown below and whether the condition is true or false “Result Published” will be printed in the immediate window.

IF… Then… Else Statements

If the condition returns a boolean true, then the set of actions defined under the if block will be executed but if the conditional expression returns a boolean false then the statements under the else block will be executed.

Syntax:

If (condition) Then
[ Statement (s) ]

Else
[Statement(s)]
End If

Flow Diagram

Once the code reaches the conditional statement, it evaluates the value of the expression. The If-block is executed if the condition is true and the Else block is executed if the condition is false. It is not possible to execute both the If and Else blocks in a single run.

Example:

Sub ifElseExample()
Dim Obtained_Marks, Passing_Marks As Integer
Obtained_Marks = 35
Passing_Marks = 35
If (Obtained_Marks >= Passing_Marks) Then
               MsgBox "Student has passed the exam"
Else
               MsgBox "Student did not clear the exam"
End If
End Sub

The output from the above code is given below

ElseIF Statements

To test a second condition we can add ElseIf statements to a simple If..Then..Else. An If statement is allowed to be followed by multiple ElseIf statements each consisting of a conditional statement.

Syntax:

If(condition) Then
   [Statement(s)]
ElseIf (condition)Then
   [Statement (s)]
End If
End If

Flow Diagram

Once the code reaches the conditional expression, it evaluates either to True or False. If the condition is true then the statements under the 1^st IF block will be executed and the control exists in the conditional block, but if the expression returns false then the control will enter the 2^nd conditional expressions and repeats the process.

Example:

Sub ifElseifExample()
Dim Obtained_Marks, Passing_Marks As Integer
Obtained_Marks = 60
Passing_Marks = 35
If (Obtained_Marks < Passing_Marks) Then
             MsgBox "Student did not clear the exam"
ElseIf (Obtained_Marks >= 60) Then
             MsgBox "Student has cleared the exam with firstclass"
Else 
            Msgbox “Student passed with second class”
End If
End Sub

Output from the above code is given below:

Nested IF Statements

VBA allows us to place control statements inside another control statement.

Example: Placing an If statement inside another if statement. This procedure of placing one control statement within another is called to be nested.

Control structures in VBA can be nested to as many levels as you wish. By intending the body of each control statement, it will be better readable.

Syntax:

If (condition) Then
Statement(s)
If(condition) Then
Statement(s)
ElseIf (condition) Then
Statement(s)
Else
           Statement(s)
End If
Else
Statement(s)
End If

Example:

Sub NestedIFExample()
Dim Obtained_Marks
Obtained_Marks = 67
If (Obtained_Marks > 0) Then
    If (Obtained_Marks = 100) Then
             MsgBox "Student has got a perfect score"
ElseIf (Obtained_Marks >= 60) Then
             MsgBox "Student has cleared the exam with first class"
ElseIf (Obtained_Marks >= 50) Then
MsgBox "Student cleared the exam with second class"
ElseIf (Obtained_Marks >= 35) Then
MsgBox "Student has cleared"
Else
MsgBox " Student did not clear the exam"
End If
ElseIf (Obtained_Marks = 0) Then
MsgBox "Student scrored a zero)"
Else
MsgBox "student did not attend the exam"
End If
End Sub

Output:

Select Case

From the above nested if statement we have seen how cumbersome it is to deal with multiple if..else statements. If you misplace a single If or Else then it is difficult to debug and hence it is more error-prone. To deal with such a problem we can use Select Case.

In Select Case, you can enter the block of code to be executed under a particular case statement. Each case statement will have a variable value to identify. Before we begin the execution, we have to specify which case is to be executed by entering the variable value in the Select Case Statement.

Syntax:

Select Case testexpression
[ Case expressionlist-n ]
             [ statements-n ]]
[ Case Else ]
            [ elsestatements ]
End Select

Select Case has a 3 part syntax:

1. Testexpression: Mandatory field and takes any numeric or string expression as input.
2. expressionlist-n: List of expressions using which the appropriate case will be selected.
3. statements-n: Set of actions performed if the test expression matches the case expression list.
4. elsestatements: Set of actions to be executed if the test expression does not match any of the case statements.

Let’s rewrite the above example in a Select Case instead of using a nested If.

Sub selectExample()
Dim marks As Integer
marks = InputBox("Enter Total Marks")
Select Case marks
Case 100
            MsgBox "Perfect score"
Case 60 To 99
            MsgBox "First Class"
Case 50 To 59
            MsgBox "Second class"
Case 35 To 49
            MsgBox "Pass"
Case 1 To 34
            MsgBox "Not Cleared"
Case 0
            MsgBox "Scored zero"
Case Else
            MsgBox "Did not attend the exam"
End Select
End Sub

As you can see, this is so easy to read and can include any number of case statements without the need to worry about matching the IF and Else statements

Now let’s build a simple calculator to perform Add, Sub, Product & Divide and understand Select case which works even for operators.

Private Sub Compute_Click()
Dim no1, no2 As Integer
Dim op As String
no1 = InputBox("Enter 1st numbers")
no2 = InputBox("Enter 2nd number")
op = InputBox("Enter Operator")
Select Case op
Case "+"
MsgBox " Sum of " & no1 & " and " & no2 & " is " & no1 + no2
Case "-"
MsgBox " Difference of " & no1 & " and " & no2 & " is " & no1 - no2
Case "*"
MsgBox " Product of " & no1 & " and " & no2 & " is " & no1 * no2
Case "/"
MsgBox " Division of " & no1 & " and " & no2 & " is " & no1 / no2
Case Else
MsgBox " Operator is not valid"
End Select
End Sub

In this example, we have taken 2 integer values and one Operator. Depending on which operator the user enters, the appropriate case will be executed. If the user enters an operator which is not a part of any of the case statement, then the Case Else will be executed.

Else case is used to execute when there is no match found. Though it is not mandatory to include an else clause, it is recommended to have an else case to handle any unforeseen expression values.

The output from all the operators and an invalid operator is shown below.

FAQs

Q #1) How do I write an If statement in VBA?

Answer: If the statement executes a set of statements under an If block only if the conditional expression returns true, otherwise the control will go to the line after the End If.

Syntax:

If(condition) Then
 [ Statement]
End If

Q #2) How do I use multiple IF statements in VBA?

Answer: Multiple IF statements can be created using the below syntax.

If(condition) Then
[Statement(s)]
ElseIf (condition)Then
[Statement (s)]
End If
End If

While using multiple if statements it is recommended to intend the code properly to avoid confusion with multiple if statements.

Q #3) How do I end an If statement in VBA?

Answer: If the statement has to be ended with an End If statement otherwise you end get a compiler error saying “Block If without End If”.

If(condition) Then
[ Statement]
End If

Q #4) How do you end Sub inside an if statement?

Answer: You can exit a Sub, by using the Exit Sub command.

If we insert an Exit Sub inside an if statement, the control comes out of the Sub procedure. Similarly, we can use the Exit Function to come out of the Function.

Example:

Sub f()
Dim i As Integer
i = 5
If i = 5 Then
   Exit Sub
End If 
End Sub

Conclusion

In this tutorial, we have learned the different conditional statements like If..Then statements, If..Then..Else, ElseIf, Nested If, and Select Case statements,

These conditional statements that VBA provides will help us in making a decision and perform an appropriate set of actions.

=> Visit Here To Learn VBA From Scratch