Home and Learn - Free Excel VBA Course
3.2 Excel VBA - Else ... ElseIf
In the last lesson, you saw how to set up an If Statement. In this lesson, we expand the If Statement with Else and ElseIf.
Else
Between If and End If you can also add an Else part. The structure of an If … Else Statement looks like this:
If Condition_To_Test Then
Else
End If
In our code from the previous lesson, nothing happened because we set MyNumber to a value of 11. Our IF Statement only executes when MyNumber has a value of 10, making the condition TRUE.
However, instead of VBA skipping past the entire IF Statement, we can add an Else part. The Else part is where you tell VBA what should happen if the opening line is FALSE. Change your code from the last lesson to this (the new lines are in bold):
Dim MyNumber As Integer
MyNumber = 11
If MyNumber = 10 Then
MsgBox "Number = 10"
Else
MsgBox "Number is not 10"
End If
What we're saying is, "If MyNumber has a value of 10 Then display one message box, Else display another message box".
Run your code by pressing F5 on your keyboard. You should see the message from the Else part appear:
Click OK on the message box to return to your coding window. Change the number from the second line of your code from 11 back to 10. When you run your code again, you'll see the first message box.
Using an Else part for your If Statements is handy programming technique to learn.
ElseIf
Not every condition can be reduced to a simple either/or. Quite often, you'll have more than two options you want to check for. In our code, we may want to check for a value of 10, 11, and any other number. This is where ElseIf comes in. Here's the structure of an ElseIf Statement:
If Condition_To_Test Then
ElseIf Condition_To_Test Then
End If
You can have more than one ElseIf parts, as many as you need, in fact:
If Condition_To_Test Then
ElseIf Condition_To_Test Then
ElseIf Condition_To_Test Then
End If
Notice that the only difference between If and ElseIf is the word Else tacked on to the word If. You still have a condition to test and the keyword Then at the end.
You can also add an Else part, to catch anything you may have missed:
If Condition_To_Test Then
ElseIf Condition_To_Test Then
Else
End If
To test all this out, add a new Sub. Call it If_Test_2. Then add the following code:
Dim MyNumber As Integer
MyNumber = 11
If MyNumber = 10 Then
MsgBox "Number = 10"
ElseIf MyNumber = 11 Then
MsgBox "Number = 11"
End If
Your coding window should look like this:
This is almost the same as before. The difference is the ElseIf part:
ElseIf MyNumber = 11 Then
MsgBox "Number = 11"
First of all, VBA checks the variable MyNumber for a value of 10. If this evaluates to TRUE then the code for the IF statement gets executed. If MyNumber doesn't have a value of 10 then VBA drops down to the ElseIf part. It then checks MyNumber for a value of 11. If this is TRUE then the second message box will display. If neither is TRUE then VBA will skip to the End If and continue on its way.
Test it out. Run your code and you'll see Number = 11 appear in a message box. Now return to your coding window and change the second line from MyNumber = 11 to MyNumber = 10. Run your code again and the first message box displays.
Return to your coding window again and change MyNumber to 12. If you try to run your code now, nothing will happen. Nothing happens because both the IF and the ElseIf line evaluate to FALSE.
If MyNumber = 10 Then
MsgBox "Number = 10"
ElseIf MyNumber = 11 Then
MsgBox "Number = 11"
Else
MsgBox "Not 10 or 11"
End If
Run your code again and you'll find that the third message box display. By adding Else you've guaranteed that at least one part of your If statement will be TRUE.
In the next lesson, you'll learn about the conditional operators that are used with If Statements.
Next Lesson: 3.3 Conditional Operators >