MS Excel: CASE Statement (VBA)

In Microsoft Excel, the CASE statement has the functionality of an IF-THEN-ELSE statement.

Syntax

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 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

• VBA function (VBA)

VBA Function Example

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