totn Excel Functions

MS Excel: How to use the CASE Statement (VBA)

This Excel tutorial explains how to use the Excel CASE statement with syntax and examples.

Description

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

The CASE statement is a built-in function in Excel that is categorized as a Logical Function. It can be used as a VBA function (VBA) in Excel. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

Syntax

The syntax for the CASE statement in Microsoft Excel 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

Parameters or Arguments

test_expression
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, ... condition_n
Conditions that are evaluated in the order listed. Once a condition is found to be true, it will execute the corresponding code and not evaluate the conditions any further.
result_1, ... result_n
The code that is executed once a condition is found to be true.

Returns

The CASE statement executes the corresponding code for the first condition that is found to be TRUE.
If no condition is met, then the Else clause in the CASE statement will be executed. The Else clause is optional.
If the Else clause is omitted and no condition is found to be true, then the CASE statement will do nothing.

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

  • VBA function (VBA)

Example (as VBA Function)

The CASE statement can only be used in VBA code in Microsoft Excel.

Let's look at some Excel CASE statement function examples and explore how to use the CASE statement in Excel VBA code:

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

With the Excel CASE statement, 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

With the Excel CASE statement, 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, with the Excel CASE statement, 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