Different languages have different syntaxes for nested if statements. In this lesson, I’m going to show you, how you can use multiple if statements in VBA in a few different examples.
Nested If statements
The first example I’m going to show you two ways you can use multiple ifs. The first way will work, but it’s not the elegant one.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Sub MultipleIfsWrongWay() vehicle = "scooter" If vehicle = "car" Then MsgBox "The vehicle is a car" Else If vehicle = "motorcycle" Then MsgBox "The vehicle is a motorcycle" Else If vehicle = "bicycle" Then MsgBox "The vehicle is a motorcycle" Else MsgBox "I do not know what kind of vehicle it is" End If End If End If End Sub |
Here, you have a lot of code and when you have to close the IF condition this is not going to be very readable.
If and ElseIf
That’s why there is an ElseIf condition. It is executed only if the previous one returned FALSE. Take a look at the modified example.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub MultipleIfs() vehicle = "scooter" If vehicle = "car" Then MsgBox "The vehicle is a car" ElseIf vehicle = "motorcycle" Then MsgBox "The vehicle is a motorcycle" ElseIf vehicle = "bicycle" Then MsgBox "The vehicle is a motorcycle" Else MsgBox "I do not know what kind of vehicle it is" End If End Sub |
This time the code is much more readable than the last one. In this example, the first three conditions are not met, so Excel displays the last message.
Select … Case statement
If you have multiple cases to choose from, you can also use the Select … Case statement. It works in a similar way to If … Else.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Sub CaseStatement() vehicle = "scooter" Select Case vehicle Case Is = "car" MsgBox "The vehicle is a car" Case Is = "motorcycle" MsgBox "The vehicle is a motorcycle" Case Is = "bicycle" MsgBox "The vehicle is a bicycle" Case Else MsgBox "I do not know what kind of vehicle it is" End Select End Sub |
Nesting If and Select … Case statements
You can also combine If and Select … Case statements. This example shows a practical example of how you can use it in real life.
Let’s say you buy bicycles, motorcycles, and cars. The discount depends on the type and the number of vehicles you buy. We want to buy 7 motorcycles. Run the code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
Sub IfElseCaseStatement() vehicle = "motorcycle" amount = 7 discount = 0 If vehicle = "car" Then Select Case discount Case Is < 5 discount = 0.05 Case 5 To 10 discount = 0.1 Case Is > 10 discount = 0.15 End Select ElseIf vehicle = "motorcycle" Then Select Case discount Case Is < 5 discount = 0.06 Case 5 To 10 discount = 0.12 Case Is > 10 discount = 0.18 End Select ElseIf vehicle = "bicycle" Then Select Case discount Case Is < 5 discount = 0.07 Case 5 To 10 discount = 0.14 Case Is > 10 discount = 0.21 End Select Else MsgBox "I do not know what kind of vehicle it is" End If MsgBox "You bought " & amount & " " & vehicle & "s with " & discount * 100 & "% of dicount." End Sub |
After you run the code, Excel will display this message.
