How to Use the Select Case Structure in Excel 2016 VBA

By John Walkenbach

The Select Case structure is a useful VBA structure for decisions involving three or more options in Excel 2016 (although it also works with two options, providing an alternative to the If-Then-Else structure).

A Select Case example

The following example shows how to use the Select Case structure:

Sub ShowDiscount3()
  Dim Quantity As Long
  Dim Discount As Double
  Quantity = InputBox(“Enter Quantity: “)
  Select Case Quantity
    Case 0 To 24
      Discount = 0.1
    Case 25 To 49
      Discount = 0.15
    Case 50 To 74
      Discount = 0.2
    Case Is >= 75
      Discount = 0.25
  End Select
  MsgBox “Discount: “ & Discount
End Sub

In this example, the Quantity variable is being evaluated. The routine checks for four different cases (0–24, 25–49, 50–74, and 75 or greater).

Any number of statements can follow each Case statement, and they all are executed if the case is true. If you use only one statement, as in this example, you can put the statement on the same line as the Case keyword, preceded by a colon — the VBA statement separator character. This makes the code more compact and a bit clearer. Here’s how the routine looks in this format:

Sub ShowDiscount4 ()
  Dim Quantity As Long
  Dim Discount As Double
  Quantity = InputBox(“Enter Quantity: “)
  Select Case Quantity
    Case 0 To 24: Discount = 0.1
    Case 25 To 49: Discount = 0.15
    Case 50 To 74: Discount = 0.2
    Case Is >= 75: Discount = 0.25
  End Select
  MsgBox “Discount: “ & Discount
End Sub

When VBA executes a Select Case structure, the structure is exited as soon as VBA finds a true case and executes the statements for that case.

A nested Select Case example

As demonstrated in the following example, you can nest Select Case structures. This routine examines the active cell and displays a message describing the cell’s contents. Notice that the procedure has three Select Case structures, and each has its own End Select statement:

Sub CheckCell()
  Dim Msg As String
  Select Case IsEmpty(ActiveCell)
    Case True
     Msg = “is blank.”
    Case Else
     Select Case ActiveCell.HasFormula
       Case True
        Msg = “has a formula”
       Case Else
        Select Case IsNumeric(ActiveCell)
          Case True
           Msg = “has a number”
          Case Else
           Msg = “has text”
        End Select
    End Select
  End Select
  MsgBox “Cell “ & ActiveCell.Address & “ “ & Msg
End Sub

The logic goes something like this:

  1. Find out whether the cell is empty.

  2. If it’s not empty, see whether it contains a formula.

  3. If there’s no formula, find out whether it contains a numeric value or text.

When the routine ends, the Msg variable contains a string that describes the cell’s contents. The MsgBox function displays that message.

A message displayed by the CheckCell procedure.

A message displayed by the CheckCell procedure.

You can nest Select Case structures as deeply as you need to, but make sure that each Select Case statement has a corresponding End Select statement.

If you’re still not convinced that indenting code is worth the effort, the previous listing serves as a good example. The indentations really to make the nesting levels clear. Take a look at the same procedure without any indentation:

Sub CheckCell()
Dim Msg As String
Select Case IsEmpty(ActiveCell)
Case True
Msg = “is blank.”
Case Else
Select Case ActiveCell.HasFormula
Case True
Msg = “has a formula”
Case Else
Select Case IsNumeric(ActiveCell)
Case True
Msg = “has a number”
Case Else
Msg = “has text”
End Select
End Select
End Select
MsgBox “Cell “ & ActiveCell.Address & “ “ & Msg
End Sub

Fairly incomprehensible, eh?