VBA Select Case

---

In some situations it is necessary to repeat several conditions to create an If statement with ElseIf.

Sub Grade()

    Dim Score As Integer
    Dim Grade As String

    Score = Inputbox("What was your test score?")

    If Score < 2 Then
        Grade = "E"
    ElseIf Score < 4 Then
        Grade = "D"
    ElseIf Score < 6 Then
        Grade = "C"
    ElseIf Score < 8 Then
        Grade = "B"
    ElseIf Score <= 10 Then
        Grade = "A"
    Else
        Msgbox "Please insert a value between 0 and 10"
    End if

    Msgbox "Your grade was " & Grade & "."

End Sub

The variable Score was repeated several times in the code to create the conditions. One way to simplify repetitions is by using Select Case (notice that the variable you want to compare will be omitted and implicitly replicated for all statements).

Sub GradeSCase()

    Dim Score As Integer
    Dim Grade As String

    Score = Inputbox("What was your test score?")

    Select Case Score
        Case Is < 2: Grade = "E" 'Equivalent to ElseIf Score < 2 Then: Grade = "E"
        Case Is < 4: Grade = "D"
        Case Is < 6: Grade = "C"
        Case Is < 8: Grade = "B"
        Case Is <= 10: Grade = "A"

    Case Else
        Msgbox "Please insert a value between 0 and 10"
    End Select

    Msgbox "Your grade was " & Grade & "."

End Sub

Note that it is possible that multiple Case conditions result in True (E.g. in the case of the example above, if Score is equal to 7, shall result True in <8 and <=10). In this case the behavior of Case will be the same as in True in ElseIf.

The Case Else statement will only be executed if there is no True value.

You can specify multiple cases for the same Case by separating them with a comma, or by using To to create a range.

Sub Bank_Line_Time()

    Dim LineN As Integer

    LineN = Inputbox("How many people are in front of you in the queue (1 a 10)?")

    Select Case LineN

        Case 1 'Equivalent to ElseIf LineN = 1  Then
        MsgBox "You are next!"

        Case 2 To 5 'Equivalent to ElseIf LineN >= 2 And ElseIf LineN <= 5 Then
        MsgBox "You will be attended soon"

        Case 6, 7, 8 'Equivalent to ElseIf LineN = 6 Or LineN = 7 Or LineN = 8 Then
        MsgBox "Moderate wait time"

        Case Else
        MsgBox "You will not be attended today"

    End Select
End Sub

Note that when Case is used, an comparison operators is required. If the operator is omitted Case (alone), Excel will assume an equality comparison.

---

• Previous page
• Next page

---

• report error/feedback