VBA Select Case Statement

In VBA, the Select Case Statement is an alternative to the If-Then statement, allowing you to test if conditions are met, running specific code for each condition. The Select Statement is preferable to the If Statement when there are multiple conditions to process.

Select Case Example

This example prompts the user with a YesNoCancel MessageBox and tests which option the user selected:

Sub Select_Case_Yes_No_Cancel()
    Dim nResult As VbMsgBoxResult
    
    nResult = MsgBox("...", vbYesNoCancel)
    
    Select Case nResult
        Case vbYes
            MsgBox "Yes"
        Case vbNo
            MsgBox "No"
        Case vbCancel
            MsgBox "Cancel"
    End Select
End Sub

Below we’ve written out the equivalent using an If Statement instead. You’ll notice that the Case Select Statement involves slightly less typing – this benefit is amplified when testing multiple criteria.

Sub If_Yes_No_Cancel()
    Dim nResult As VbMsgBoxResult
    
    nResult = MsgBox("...", vbYesNoCancel)
    
    If nResult = vbYes Then
        MsgBox "Yes"
    ElseIf nResult = vbNo Then
        MsgBox "No"
    ElseIf nResult = vbCancel Then
        MsgBox "Cancel"
    End If
End Sub

Case Statement Syntax

The Select Case Statement syntax is as follows:

Select Case [Test Expression]
    Case [Condition 1]
        [Action if condition 1 is true]
    Case [Condition 2]
        [Action if condition 2 is true]
    Case [Condition n]
        [Action if condition n is true]
    Case Else
        [Action if none are true]
End Select

Where:

[Test Expression] – Is the value to evaluate. Usually this is a variable.

[Action if condition n is true] – Is just the code to run if the condition is met (just like with an If Statement)

[Condition n] – Is the condition to test. There are a lot of different ways to test conditions. We will discuss them below.

The Case Statement will execute the code for the FIRST condition that is found to be TRUE.  If no condition is met then no code will be executed, unless the Else clause is added.

Select Case Criteria

Select Cases can be used to evaluate both numerical values and text.  First we will discuss how to use Select Cases to evaluate numerical expressions.

Exact Match – Numbers

You can easily test for an exact match with a Case Statement:

Case 10

or add commas to test for exact matches with multiple numbers:

Case 20, 30, 40

Sub ExactMatch_Numbers()
    Dim n As Integer
    n = CInt(InputBox("..."))
    
    Select Case n
        Case 10
            ' If n is 10 Then
        Case 20, 30, 40
            ' If n is 20/30/40 Then
        Case Else
            ' If n is not 10/20/30/40 Then
    End Select
    
End Sub

Ranges

You can test if a number falls within a range like so:

Case 55 To 74

This procedure will generate a letter score for a student based on their numerical score:

Sub Calc_Grade()
Dim Score       As Integer
Dim LetterGrade As String

    Score = InputBox("Enter Student Score")
    
    Select Case Score
        Case 90 To 100
            LetterGrade = "A"            
        Case 80 To 90
            LetterGrade = "B"           
        Case 70 To 80
            LetterGrade = "C"            
        Case 60 To 70
            LetterGrade = "D"            
        Case Else
            LetterGrade = "F"
    End Select
    
    MsgBox "The Student's Grade is: " & LetterGrade
    
End Sub

You can also test ranges with the Case Is

Select Case Is

Case is < 55
  'Do Nothing
Case <= 74
 MsgBox "In Range"

Remember that the Case Statement will only execute code for ONLY the first match.

This procedure will calculate a student’s grade using the Case Is instead of Case To.

Sub Select_Case_Is_Grade()
    Dim Score      As Integer
    Dim LetterGrade As String
    
    Score = InputBox("Enter Student Score")
    
    Select Case Score
        Case Is >= 90
            LetterGrade = "A"            
        Case Is >= 80
            LetterGrade = "B"            
        Case Is >= 70
            LetterGrade = "C"            
        Case Is >= 60
            LetterGrade = "D"            
        Case Else
            LetterGrade = "F"
    End Select
    
    MsgBox "The Student's Grade is: " & LetterGrade
    
End Sub

Case Else

You can add “Case Else” to the end of your Case Statement to do something if no conditions are met:

Case Else

See the end of the previous code example to see how Case Else can be used.

Select Case – Text & the Like Operator

So far our Select Case examples have worked only with numbers.  You can also use Select Case statements with text.

Exact Match – Text

You can test if the expression matches an exact phrase like this:

Case "Beets"

Or use commas to test if the expression exactly matches more than one phrase:

Case "Apple", "Banana", "Orange"

Putting it together looks like:

Sub ExactMatch_Food()

Select Case Range("a1").Value
    Case "Beets"
        MsgBox "Vegetable"
    Case "Apple", "Banana", "Orange"
        MsgBox "Fruit"
End Select

End Sub

VBA Programming | Code Generator does work for you!

Upper and Lower Case

By default, VBA is Case Sensitive. This means that VBA considers “Text” different than “text”. To turn case-sensitivity off add Option Compare Text to the top of your module:

Option Compare Text

This example will make the Case Select case-insensitive when working with text:

Option Compare Text

Sub ExactMatch_Food()

Select Case Range("a1").Value
    Case "Beets"
        MsgBox "Vegetable"
    Case "Apple", "Banana", "Orange"
        MsgBox "Fruit"
End Select

End Sub

Case Like

The Like Operator allows you to make inexact comparisons.  If the text matches, Like returns TRUE, if it doesn’t match it returns FALSE.  This makes the Like operator easy to use with If Statements, however it won’t work as easily with Case Statements.

Case Like – A Failed Test

The following code demonstrates that the Like Operator doesn’t work with Select Case:

Sub Select_Case_Like_DoesnotWork()
    Dim word As String
    word = "COCOA"
    
    Select Case word
        Case word Like "*C*C*"
            MsgBox "Good"
        Case Else
            MsgBox "Not Good"
    End Select
End Sub

Case Like – The Correct Way

However, we can add in the TRUE expression to make the Select Statement work with the Like Operator:

Sub Select_Case_Like_CorrectWay()
    Dim word As String
    word = "COCOA"
    
    Select Case True
        Case word Like "*C*C*"
            MsgBox "Good"
        Case Else
            MsgBox "Not Good"
    End Select
End Sub

Case – Colon

When using a Case Statement you can add as many lines of code as you’d like to run with each condition.  However, if you only need to run one line of code. You can use a Colon ( : ) to write everything on the same line.

Here is the same student grade example as before, except using a colon to shorten the code:

Sub Calc_Grade_colon()
Dim Score      As Integer
Dim LetterGrade As String

    Score = InputBox("Enter Student Score")
    
    Select Case Score
        Case 90 To 100: LetterGrade = "A"
        Case 80 To 90: LetterGrade = "B"
        Case 70 To 80: LetterGrade = "C"
        Case 60 To 70: LetterGrade = "D"
        Case Else: LetterGrade = "F"
    End Select
    
    MsgBox "The Student's Grade is: " & LetterGrade
    
End Sub

 

Case Select – And / Or – Multiple Conditions

You can use the And / Or Operators to test additional criteria along with the Select Case.

In this example we are using a Select Case on the variable ‘age’, but we also want to test sex. So we use the And Operator to perform the more complex test:

Sub NestedSelectCase()
    Dim sex As String
    Dim age As Integer
    
    sex = "male" ' or female
    age = 15
    
    Select Case age
        Case Is < 20 And sex = "male"
            Msgbox "Male under 20"
        Case Is < 20 And sex = "female"
            Msgbox "Female under 20"        
        Case Is >= 20 And sex = "male"
            Msgbox "Male over 20" 
        Case Is >= 20 And sex = "female"
            Msgbox "Female over 20" 
    End Select
End Sub

Nested Case Statements

Just like If Statements, you can nest Case Statements inside each other:

Sub NestedSelectCase()
    Dim sex As String
    Dim age As Integer
    
    sex = "male" ' or female
    age = 15
    
    Select Case age
        Case Is < 20
            Select Case sex
                Case "male"
                    MsgBox "Male under 20"
                Case "female"
                    MsgBox "Female under 20"
            End Select
        Case Is >= 20 And sex = "female"
            Select Case sex
                Case "male"
                    MsgBox "Male over 20"
                Case "female"
                    MsgBox "Female over 20"
            End Select
    End Select
End Sub

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

Case Statement vs. If Statement

The more conditions to test, the more useful the Case Statement is compared to an If Statement. Let’s look at an example.

Here is the code required to test if a worksheet name equals a set of values using an If Statement:

If Name = "Budget" Or Name = "Forecast" Or Name = "Trailing12" Or _
   Name = "Flex" Or Name = "OtherRatios" Or Name = "Comparison" Or _
   Name = "BudReview" Or Name = "P&L_Review" Or Name = "Other" Then
   'Do something
End If

Here is the same code using a Select Statement instead:

Select Case Name
Case "Budget", "Forecast", "Trailing12", "Flex", "OtherRatios", _
     "Comparison", "BudReview", "P&L_Review", "Other"
    'Do Something
End Select

You can see it’s much easier to use a Select Statement in this scenario. It’s significantly less typing, and it’s much easier to read.

VBA Select Case Examples

Ex 1. Case Statement User Defined Function (UDF)

Let’s replicate our grade calculation example above and create a UDF to calculate a student’s score:

Function GetGrade(Score As Integer) As String
    
    Select Case Score
        Case 90 To 100
            GetGrade = "A"            
        Case 80 To 90
            GetGrade = "B"            
        Case 70 To 80
            GetGrade = "C"            
        Case 60 To 70
            GetGrade = "D"           
        Case Else
            GetGrade = "F"
    End Select
    
End Function

Now we can use Function GetGrade in our Excel worksheet to quickly calculate student grades:

 

Ex 2. Test Sheet Name / Loop Case Statement

This code will loop through all worksheets in a workbook, UnProtecting sheets that meet certain criteria:

Sub Case_UnProtectSheet()
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        Select Case ws.Name    'List of all sheets with Ratios
        Case "Budget", "Forecast", "Trailing12", "Flex", "OtherRatios", _
             "Comparison", "BudReview", "P&L_Review", "Other"
            ws.Unprotect
        End Select
    Next ws
    
End Sub

Ex 3. Select Case – Cell Value

This example will test a student’s score in a cell, outputting the letter grade directly to the cell to the right.

Sub TestCellValue()
    Dim cell As Range
    Set cell = Range("C1")

    Select Case cell.Value
    Case 90 To 100
        cell.Offset(0, 1) = "A"
    Case 80 To 90
        cell.Offset(0, 1) = "B"
    Case 70 To 80
        cell.Offset(0, 1) = "C"
    Case 60 To 80
        cell.Offset(0, 1) = "D"
    End Select

End Sub

Ex 4. Select Case – Dates

This Case Select example is a Function that tests which quarter a date fall into.

Sub TestDate ()
 MsgBox GetQuarter(CDate("7/20/2019"))
End Sub

Function GetQuarter(dt As Date) As Integer
    Dim sht As Worksheet

    Select Case dt
        Case CDate("01/01/2019") To CDate("03/31/2019")
            GetQuarter = 1
        Case CDate("04/01/2019") To CDate("06/30/2019")
            GetQuarter = 2
        Case CDate("07/01/2019") To CDate("09/30/2019")
            GetQuarter = 3
        Case CDate("10/01/2019") To CDate("12/31/2019")
            GetQuarter = 4
    End Select
End Function

Because it’s a function, you can use it as a function inside Excel:

Ex. 5 Check if Number is Odd or Even

This example tests if a number is odd or even.

Sub CheckOddEven()
    Dim n As Integer
    n = InputBox("Enter a number")
    
    Select Case n Mod 2
        Case 0
            MsgBox "The number is even."
        Case 1
            MsgBox "The number is odd."
    End Select
    
End Sub

Ex. 6 Test if Date is on Weekday or Weekend

These examples will test if a date falls on a weekday or a weekend.

Sub CheckWeekDay()
    Dim dt As Date
    dt = CDate("1/1/2020")
    
    Select Case Weekday(dt)
        Case vbMonday
            MsgBox "It's Monday"
        Case vbTuesday
            MsgBox "It's Tuesday"
        Case vbWednesday
            MsgBox "It's Wednesday"
        Case vbThursday
            MsgBox "It's Thursday"
        Case vbFriday
            MsgBox "It's Friday"
        Case vbSaturday
            MsgBox "It's Saturday"
        Case vbSunday
            MsgBox "It's Sunday"
    End Select
End Sub

Sub CheckWeekend()
    Dim dt As Date
    dt = CDate("1/1/2020")
    
    Select Case Weekday(dt)
        Case vbSaturday, vbSunday
            MsgBox "It's a weekend"
        Case Else
            MsgBox "It's not a weekend"
    End Select

End Sub

VBA Select Case in Access

All of the above examples work exactly the same in Access VBA as in Excel VBA.

Sub TestCellValue()
    Dim dbs as Database
    Dim rst as RecordSet
    Set dbs = CurrentDB
    Set rst = dbs.OpenRecordset("tblClients", dbOpenDynaset)
    With rst
       .MoveFirst
       .Edit
       Select Case rst.Fields("City")
       Case "Austin"
           .rst.Fields("TelCode") = "512" 
       Case "Chicago"
           .rst.Fields("TelCode") = "312" 
       Case "New YorK"
           .rst.Fields("TelCode") = "1212" 
       Case "San Fransisco"
           .rst.Fields("TelCode") = "415" 
    End Select
    .Update
   End With
End Sus