In VBA, you can use one IF statement inside another IF statement to create nested IFs. In simple words, you can execute one test statement using IF based on the result of another IF statement. In this kind of conditional statement, you need to test complex conditions. Let’s see an example:
In the above example, we are testing two conditions, condition1 (1 + 1 = 2) and condition2 (2 + 2 = 4).
But as we are the IF statement inside the IF statement, VBA will first test the first condition and then move to the next IF.
In the second IF, we have a message box to show if the condition is TRUE.
Sub myMacro1() 'first if statement If 1 + 1 = 2 Then 'second if statement If 2 + 2 = 4 Then MsgBox True End If Else MsgBox False End If End Sub
Here’s an example of using multiple IFs in one IF.
There is one difference between the example that we have used in the earlier and in the above example: In this code, we have used a nested condition for “Else”.
If the first condition is not true, VBA will move to the “Else” and test the condition, and if that condition is not true then to the next.
In this way, we have a total of four conditions to test.
Sub myMacro2() Dim myScore As Single: myScore = Range("A1").Value If myScore >= 80 Then MsgBox "Excellent" Else If myScore >= 60 And myScore < 80 Then MsgBox "Good" Else If myScore >= 40 And myScore < 60 Then MsgBox "Average" Else If myScore < 40 Then MsgBox "Poor" End If End If End If End If End Sub
This nested IF statement will test for the score from cell A1 and show a message box accordingly.
VBA Nested IF with Multiple Lines (IFs)
You can also use separate two different IFs to execute for both results (True and False).
In the above example, as you can see we first have a condition to test and if that condition is TRUE and then you have another condition to test and if that second condition is TRUE, you’ll get a message box.
And if the main condition is false, you have another condition to test and you’ll get a message box if that condition is true.
Sub myMacro3() If 1 + 1 = 2 Then If 2 - 1 = 1 Then MsgBox "Condition1 is TRUE." End If Else If 3 - 2 = 1 Then MsgBox "Condition2 is TRUE." End If End If End Sub
Below are some of the links which could be useful for you and if you want to learn more about VBA, check out this Excel VBA Tutorial – The Best Way to Learn Programming in Excel.
If you are preparing for an interview, make sure to check out these VBA interview questions.