VBA multiple ifs

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.

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.

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.

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.

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.

Posted in vba