Nested If Statement. VBA Programming is a major area of relying on the If-Else statements. In this lesson, we will explore various ways to implement conditional statements in VBA (If..Else nested IF). In VBA, there are several powerful data manipulation tools; in the later part of the tutorial, we will see how IF ELSE statements are commonly used in popular programs.
Syntax
If (Boolean_expression) Then Statement 1 ... Statement n If (Boolean_expression) Then Statement 1 ... Statement n ElseIf (Boolean_expression) Then Statement 1 ... Statement n Else Statement 1 ... Statement n End If Else Statement 1 ... Statement n End If
The compound statement in an if-branch may contain one or more of any type of if-statement discussed above.
Example
Sub NestedIfStatement() Dim Marks As Integer Marks = 80 If Marks >= 80 Then MsgBox "First" ElseIf Marks >= 60 And sngMarks < 80 Then MsgBox "Second" ElseIf Marks >= 40 And sngMarks < 60 Then MsgBox "Third" Else MsgBox "Fail" End If End Sub
Note: In a nested if statement, the last else is associated with the closest unpaired if, unless braces are used to alter the default pairing.
Flow Diagram
After evaluating a True condition (and running its associated statements), VBA still runs through each of the If…Then conditions in a nested statement, while in an ElseIf structure, all of the following conditions are skipped after evaluating a True condition.
In this situation, the ElseIf structure is faster. Thus, nested statements might not include a very efficient method if the same can be done with the ElseIf structure. Although, Nested If statements are hard to read and very hard to debug.
See More: If Else If Statement
For Example
suppose you want to write a code that returns the message greater than, less than, and betwen if a x value is greater 50 and less the 100. And returns “X is between 50 and 100”, “X is > 100”, and “X is between 10 and 50” or “X is < 10” according to the decision criteria as shown in the below example, such as:
Sub VBANestedIf_Example1() Dim x As Integer If x > 50 Then If x < 100 Then MsgBox "X is between 50 and 100" Else MsgBox "X is > 100" End If Else If x > 10 Then MsgBox "X is between 10 and 50" Else MsgBox "X is < 10" End If End If End Sub
You can make this possible by running the above code in VBA, and you get the output as shown in the below screenshot.
Example 2
Let’s find the positive numbers of Excel with the help of a function. Check the code below:
Sub VBANestedIf_Example2() Dim x As Integer x = 30 If x > 0 Then MsgBox "a number is a positive number" If x = 1 Then MsgBox "A number is neither prime nor composite" ElseIf x = 2 Then MsgBox "A number is the only prime even prime number" ElseIf x = 3 Then MsgBox "A number is the least odd prime number" Else MsgBox "The number is not 0, 1, 2, or 3" End If ElseIf x < 0 Then MsgBox "A number is a negative number" Else MsgBox "the number is zero" End If End Sub
After that execution of the above code, you will get the output such as:
A number is a positive number
The number is not 0, 1, 2, or 3
