• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

VBA SELECT CASE

You are here: Home / Excel VBA / VBA SELECT CASE
VBA Select Case
August 10, 2017 by Philip Treacy

VBA SELECT CASE allows you to test an expression against multiple conditions, and then carry out a series of commands when a matching condition is found.

It's like using lots of IF statements, but easier and clearer to understand.

Here's the basic structure of SELECT CASE

Select Case Expression

   Case Condition_1
      Code Block

   Case Condition_2
      Code Block

   ..
   ..

   Case Condition_n
      Code Block

   Case Else
      Code Block

End Select

Each Case Condition is a test to match against the Expression

At the bottom is a Case Else which is a catch-all if none of the other Case tests have been met. The Case Else is optional.

Each Code Block consist of one or more lines of VBA that are run if the Condition matches the Expression.

SELECT CASE in Action

VBA Select Case Example

In this example I'm testing the value of a variable called MyNum. I've initially set this to 5.

As I step through the code (by pressing F8) you can see that each of the Case statements is tested before reaching the 5 towards the bottom. When this match is found, the code under that is run which just writes some text to the Immediate Window in the VB editor.

The value of MyNum is then changed to 6 and I step through the code again. This time I don't have any Case matches so the code uses the Case Else and prints the message that the number is "Greater than Five".

If I left out the Case Else, then no tests match the Expression and nothing is printed.

In this example I'm testing a hard coded value, but this Expression can be the result of a function, input from a MsgBox, a value in a cell, some mathematical expression or just a variable you've assigned a value to.

SELECT CASE With Strings

You can test strings like this:

Sub select_case_string()

    Dim MyStr As String
    
    MyStr = "Three"
    
    Select Case MyStr

        Case "One"
            Debug.Print 1

        Case "Two"
            Debug.Print 2

        Case "Three"
            Debug.Print 3
        
        Case "Four"
            Debug.Print 4
        
        Case "Five"
            Debug.Print 5
    
    End Select

End Sub

SELECT CASE With Multiple Tests

You're not limited to testing a single value against your expression. You can test multiple values by separating them with commas:

Sub select_case_numbers()

    Dim MyNum As Long
    
    MyNum = 2
    
    Select Case MyNum

        Case 1, 3, 5
            Debug.Print "Odd"

        Case 2, 4
            Debug.Print "Even"

    End Select

End Sub
Sub select_case_strings()

    Dim MyStr As String
    
    MyStr = "Three"
    
    Select Case MyStr

        Case "One", "Three", "Five"
            Debug.Print "Odd"

        Case "Two", "Four"
            Debug.Print "Even"
    
    End Select

End Sub

Test a Range of Numbers

You can test if a number is in a range using To:

Sub select_case_to_num()

    Dim MyNum As Long
    
    MyNum = 50
    
    Select Case MyNum

        Case 51 To 100 ' Must be low number to high number
            Debug.Print "> 50 and <= 100"

        Case 0 To 50
            Debug.Print ">= 0 and <= 50"
            
    End Select

End Sub

Be careful with the Case statements as the ranges are inclusive of the starting and ending numbers, and make sure your ranges don't overlap.

Also, you must specify the lowest number first. Writing Case 100 To 51 won't work properly.


Test a Range of Characters

As with numbers, you can test if a character (or string) is in a given 'range' of characters using To:

Sub select_case_to_char()

    Dim MyStr As String
    
    MyStr = "f"
    
    Select Case MyStr

        Case "a" To "m"
            Debug.Print "a to m"

        Case "n" To "z"
            Debug.Print "n to z"
            
    End Select

End Sub

Comparison Tests : IS

You can use the comparison operators =, <>, <, <=, >, >= to test values:

You need to use the Is keyword before the comparison operator. If you don't type it, it's entered in for you automatically.

Sub select_case_is()

    Dim MyNum As Long
    
    MyNum = 75
    
    Select Case MyNum

        Case Is > 100
            Debug.Print "Greater than 100"

        Case Is > 75
            Debug.Print "Greater than 75"

        Case Is > 50
            Debug.Print "Greater than 50"
            
        Case Is > 25
            Debug.Print "Greater than 25"
        
        Case Is > 0
            Debug.Print "Greater than 0"
            
        Case Else
            Debug.Print "Less than or equal to 0"
    
    End Select

End Sub

Mixing Case Tests

It is possible to mix the different types of Case tests, like so:

Sub select_case_mixed_test()

    Dim MyNum As Long
    
    MyNum = -9
    
    Select Case MyNum

        Case Is > 100
            Debug.Print "Greater than 100"

        Case 50 To 100
            Debug.Print ">= 50 and <= 100"

        Case 1 To 49
            Debug.Print ">= 1 and <= 49"
                  
        Case 0
            Debug.Print "Zero"
                        
        Case Else
            Debug.Print "Less than 0"
    
    End Select

End Sub

As long as the data type for each Case test matches the data type being tested, you are ok with this.

Using Variant Data Type

If you aren't sure what data type you are going to be testing then you need to use a Variant variable.

This can happen for example, if you read the contents of a cell, and aren't sure if you are getting text or a number.

Sub select_case_variant()

    Dim MyVar As Variant
    
    MyVar = "One"
    
    Select Case MyVar

        Case "One"
            Debug.Print "One"
        
        Case 1
            Debug.Print "1"

    End Select

End Sub

If I declared MyVar as Long, I'd get an error when the code gets to the first test Case "One". It will only generate an error when the code runs, not before, so bugs can sneak into your code this way if you're not careful.

Download Sample Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

You can download a sample workbook with lots of examples of using SELECT CASE.

VBA Select Case

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Excel VBA
Previous Post:if then elseVBA IF THEN ELSE
Next Post:Excel Find and Remove Duplicates

Reader Interactions

Comments

  1. Wyn Hopkins

    August 11, 2017 at 9:00 am

    Nice illustration

    Reply
    • Philip Treacy

      August 11, 2017 at 10:53 am

      Thanks

      Reply
  2. Harold

    August 11, 2017 at 8:50 am

    An excellent tutorial Phil !

    For those doing serious VBA programming, I would recommend SELECT CASE as generally a much better structure to use than IF . . . THEN, especially for more complex decision trees.

    It makes the code far easier to read, understand and troubleshoot.

    Reply
    • Philip Treacy

      August 11, 2017 at 10:53 am

      Thanks Harold.

      Reply
  3. RyanOKeefe

    August 10, 2017 at 10:47 pm

    I saw this newsletter header and thought “I already know this, but I’ll skim it anyway.” And then you illuminate how little I knew about select case statements. Thank you for the post!

    Reply
    • Philip Treacy

      August 11, 2017 at 10:53 am

      No worries Ryan, glad you found it useful.

      Reply
  4. SunnyKow

    August 10, 2017 at 2:28 pm

    One other test that I often use in a userform when I need to find out what option button was selected.

    Private Sub cbOK_Click()
        Select Case True
            Case obDeleteRows
                DeleteEmptyRows
            Case obDeleteColumns
               DeleteEmptyColumns
        End Select
    End Sub
    
    Reply
    • Philip Treacy

      August 10, 2017 at 2:44 pm

      Thanks Sunny

      Reply
  5. KingTamo

    August 10, 2017 at 1:59 pm

    Thank you very much Phil for this awesome tutorial.

    Reply
    • Philip Treacy

      August 10, 2017 at 2:20 pm

      You’re welcome 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.