Select Case Statement

Description

Executes one of several groups of statements, depending on the value of an expression.

Syntax

Select Case testexpression
[Case expressionlist-n
[statements-n]] . . .
[Case Else
[elsestatements]]
End Select

The Select Case statement syntax has these parts:

Part	Description

testexpression	Any numeric or string expression.
expressionlist-n	Comma-delimited list of one or more of the following forms: expression, expression To expression, Is comparisonoperator expression. The To keyword specifies a range of values. If you use the To keyword, the smaller value must appear before To. Use the Is keyword with comparison operators (except Is and Like) to specify a range of values. If not supplied, the Is keyword is automatically inserted.
statements-n	One or more statements executed if testexpression matches any part of expressionlist-n.
elsestatements	One or more statements executed if testexpression doesn't match any of the Case clause.

Remarks

If testexpression matches any expressionlist expression associated with a Case clause, the statements following that Case clause are executed up to the next Case clause, or, for the last clause, up to the End Select. Control then passes to the statement following End Select. If testexpression matches an expressionlist expression in more than one Case clause, only the statements following the first match are executed.

The Case Else clause is used to indicate the statements to be executed if no match is found between the testexpression and an expressionlist in any of the other Case selections. When there is no Case Else statement and no expression listed in the Case clauses matches testexpression, execution continues at the statement following End Select.

Although not required, it is a good idea to have a Case Else statement in your Select Case block to handle unforeseen testexpression values.

You can use multiple expressions or ranges in each Case clause. For example, the following line is valid:

Case 1 To 4, 7 To 9, 11, 13, Is > MaxNumber

Note

The Is comparison operator is not the same as the Is keyword used in the Select Case statement.

You also can specify ranges and multiple expressions for character strings. In the following example, Case matches strings that are exactly equal to everything,strings that fall between nuts and soup in alphabetical order, and the current value of TestItem:

Case "everything", "nuts" To "soup", TestItem

Select Case statements can be nested. Each Select Case statement must have a matching End Select statement.

See Also

If...Then...Else Statement; On...GoSub, On...GoTo Statements; Option Compare Statement.

Example

This example uses the Select Case statement to evaluate the value of a variable. The second Case clause contains the value of the variable being evaluated and therefore only the statement associated with it is executed.

Number = 8    ' Initialize variable.
Select Case Number    ' Evaluate Number.
Case 1 To 5    ' Number between 1 and 5.
    MyString = "Between 1 and 5"
Case 6, 7, 8, 9, 10    ' Number between 6 and 10.
    ' This is the only Case clause that evaluates to True.
    MyString = "Between 6 and 10"
Case Else    ' Other values.
    MyString = "Not between 1 and 10"
End Select

This example displays the name of the mail system installed on the computer.

Sub foo()
Select Case Application.MailSystem
    Case Is = xlMAPI
        MsgBox "Mail system is Microsoft Mail"
    Case Is = xlPowerTalk
        MsgBox "Mail system is PowerTalk"
    Case Is = xlNoMailSystem
        MsgBox "No mail system installed"
End Select
End Sub

This example displays a message box that describes the location of the active cell in the PivotTable.

Worksheets("Sheet1").Activate
Select Case ActiveCell.LocationInTable
Case Is = xlRowHeader
    MsgBox "Active cell is part of a row header"
Case Is = xlColumnHeader
    MsgBox "Active cell is part of a column header"
Case Is = xlPageHeader
    MsgBox "Active cell is part of a page header"
Case Is = xlDataHeader
    MsgBox "Active cell is part of a data header"
Case Is = xlRowItem
    MsgBox "Active cell is part of a row item"
Case Is = xlColumnItem
    MsgBox "Active cell is part of a column item"
Case Is = xlPageItem
    MsgBox "Active cell is part of a page item"
Case Is = xlDataItem
    MsgBox "Active cell is part of a data item"
Case Is = xlTableBody
    MsgBox "Active cell is part of the table body"
End Select

This example displays a message if the active cell on Sheet1 contains a cell error value. You can use this example as a framework for a cell-error-value error handler.

Worksheets("Sheet1").Activate
If IsError(ActiveCell.Value) Then
    errval = ActiveCell.Value
    Select Case errval
        Case CVErr(xlErrDiv0)
            MsgBox "#DIV/0! error"
        Case CVErr(xlErrNA)
            MsgBox "#N/A error"
        Case CVErr(xlErrName)
            MsgBox "#NAME? error"
        Case CVErr(xlErrNull)
            MsgBox "#NULL! error"
        Case CVErr(xlErrNum)
            MsgBox "#NUM! error"
        Case CVErr(xlErrRef)
            MsgBox "#REF! error"
        Case CVErr(xlErrValue)
            MsgBox "#VALUE! error"
        Case Else
            MsgBox "This should never happen!!"
    End Select
End If