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

NestedIf

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.

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.

NestedifMsgbox

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


Notice: Trying to access array offset on value of type int in /home2/bestrev2/public_html/xlautomation/wp-includes/class-wp-list-util.php on line 170

Notice: Trying to access array offset on value of type int in /home2/bestrev2/public_html/xlautomation/wp-includes/class-wp-list-util.php on line 170

Notice: Trying to access array offset on value of type int in /home2/bestrev2/public_html/xlautomation/wp-includes/class-wp-list-util.php on line 170

Leave a Comment

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