Excel: Case Statement (VBA only)

In Excel, the Case statement can only be used in VBA code. It has the functionality of an IF-THEN-ELSE statement.

The syntax for the Case statement is:

Select Case test_expression

   Case condition_1
     result_1
   Case condition_2
     result_2
   ...
   Case condition_n
     result_n

  Case Else
     result_else

End Select

test_expression is a string or numeric value. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)

condition_1 to condition_n are evaluated in the order listed. Once a condition is found to be true, the Case statement will execute the corresponding code and not evaluate the conditions any further.

result_1 to result_n is the code that is executed once a condition is found to be true.

Note:

If no condition is met, then the Else portion of the Case statement will be executed. It is important to note that the Else portion is optional.

Applies To:

Excel 2007, Excel 2003, Excel XP, Excel 2000

VBA Code

The Case statement can only be used in VBA code.
Let's take a look at a simple example:

Select Case LRegion
   Case "N"
      LRegionName = "North"
   Case "S"
      LRegionName = "South"
   Case "E"
      LRegionName = "East"
   Case "W"
      LRegionName = "West"
End Select

You can also use the To keyword to specify a range of values. For example:

Select Case LNumber
   Case 1 To 10
     LRegionName = "North"
   Case 11 To 20
      LRegionName = "South"
   Case 21 To 30
     LRegionName = "East"
   Case Else
      LRegionName = "West"
End Select

You can also comma delimit values. For example:

Select Case LNumber
   Case 1, 2
      LRegionName = "North"
   Case 3, 4, 5
      LRegionName = "South"
   Case 6
      LRegionName = "East"
   Case 7, 11
      LRegionName = "West"
End Select

And finally, you can also use the Is keyword to compare values. For example:

Select Case LNumber
   Case Is < 100
      LRegionName = "North"
   Case Is < 200
      LRegionName = "South"
   Case Is < 300
      LRegionName = "East"
   Case Else
      LRegionName = "West"
End Select