Excel VBA: Select … Case Statement

Select … Case Statement

When a group of statements is executed, depending upon the value of an Expression, then Switch Case is used. 

If you have several conditions to check, then the If condition will go through each of the conditions separately. Hence, the job becomes a bit tedious. The alternative of IF is jumping off to the statement that applies to the state of a condition is Select Case. Each value is called a Case. Using the Select Case condition, you can choose from two or more options.

The Select CASE statement executes the code for the first condition that is found to be TRUE. If none of the conditions are met, then the Else clause in the CASE statement will be implemented. 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.

Syntax

Select Case <Expression>
    Case <Expression1>
                 <Statement1>
    Case <Expression2>
                 <Statement2>
 …..
    Case <ExpressionN>
              <StatementN>
 [ Case Else 
       result_else ]
 End Select  

Example 1: Let’s suppose we have a country name in cell A2, which can be either UK, US, or India, and depending on which country we have, we will place the value of its capital in cell B2. The program will look like following

Sub Select_Case_Example1()
     Select Case Worksheets("Sheet1").Cells(2, 1).Value
         'Will check if the cell A2 value is Uk
         'If returns true, it will return London in B2
         Case Is = "UK"
         Worksheets("Sheet1").Cells(2, 2).Value = "London"
         'Will check if the cell A2 value is US
         'If returns true, it will return Washington D.C in B2
         Case Is = "US" 
         Worksheets("Sheet1").Cells(2, 2).Value = "Washington D.C"
         'Will check if the cell A2 value is India
         'If returns true, it will return New Delhi in B2
         Case Is = "India"
         Worksheets("Sheet1").Cells(2, 2).Value = "New Delhi"
     End Select
 End Sub 

Output

Example 2: By using the Select Case condition, write a macro, and implement the following condition

1. If the cell value is less than 100 return North.
2. If it’s less than 200, it should return South,
3. For value less than 300, return East
4. Else it should return West

Sub Select_Case_Example2()
     Select Case Worksheets("Sheet1").Cells(2, 1).Value
         Case Is < 100
             Worksheets("Sheet1").Cells(2, 2).Value = "North"
         Case Is < 200
             Worksheets("Sheet1").Cells(2, 2).Value = "South"
         Case Is < 300
             Worksheets("Sheet1").Cells(2, 2).Value = "East"
         Case Else 
             Worksheets("Sheet1").Cells(2, 2).Value = "West"
    End Select
 End Sub 

Output

Example 3: Write a macro to check the divisibility of numbers using Select Case conditions.

Sub Select_Case_Example3()
    Dim Var As Integer
    MyVar = 5
    Select Case MyVar
       Case 2
          MsgBox "The Number is a multiple of 2."
       Case 3
          MsgBox "The Number is a multiple of 3."
       Case 5 
          MsgBox "The Number is a multiple of 5."
       Case Else
          MsgBox "Unknown Number"
    End Select
 End Sub 

Output