Excel VBA Select Case Examples: Syntax and Common Uses

VBA & macros · Updated November 19, 2026

In Excel VBA, Select Case chooses which block of code to run based on the value of an expression. Use it when one variable or expression needs to be checked against several values, ranges, or conditions; the examples below show exact matches, ranges, text comparisons, and a worksheet task.

What Select Case Does

Select Case is VBA’s multi-branch alternative to writing a long chain of If...Then...ElseIf statements. VBA evaluates the expression after Select Case, compares it with each Case clause in order, and runs the first matching block. It then continues after End Select; it does not fall through and run later cases.

The basic structure looks like this:

Option Explicit

Sub BasicSelectCase()
    Dim statusCode As Long

    statusCode = 2

    Select Case statusCode
        Case 1
            MsgBox "The item is new."
        Case 2
            MsgBox "The item is in progress."
        Case 3
            MsgBox "The item is complete."
        Case Else
            MsgBox "The status code is not recognized."
    End Select
End Sub

Put this procedure in a standard VBA module. With the assigned value of 2, it displays “The item is in progress.” The Case Else branch is optional, but it is useful when values outside the expected set need to be handled rather than silently ignored.

Match One Value or Several Values

A Case can match a single value or a comma-separated list. The following procedure maps several numeric codes to labels. Here, codes 1, 3, and 5 share one result, while 2 and 4 share another.

Option Explicit

Sub DescribeCode()
    Dim codeValue As Long
    Dim description As String

    codeValue = 3

    Select Case codeValue
        Case 1, 3, 5
            description = "Odd code"
        Case 2, 4
            description = "Even code"
        Case Else
            description = "Other code"
    End Select

    MsgBox description
End Sub

For the example value 3, the message is “Odd code.” Keep each case group clear: if a value appears in more than one branch, only the first matching branch runs. This is especially helpful when the cases are being revised later and overlap is easy to miss.

Use Ranges for Scores and Thresholds

Use To to specify an inclusive range of values. For example, Case 70 To 79 matches 70 through 79, including both endpoints. A related form, Case Is >= 90, tests a comparison operator and is useful when a boundary has no fixed upper limit.

Option Explicit

Sub AssignGrade()
    Dim score As Double
    Dim grade As String

    score = 86

    Select Case score
        Case 90 To 100
            grade = "A"
        Case 80 To 89.99
            grade = "B"
        Case 70 To 79.99
            grade = "C"
        Case 60 To 69.99
            grade = "D"
        Case 0 To 59.99
            grade = "F"
        Case Else
            grade = "Check score"
    End Select

    MsgBox grade
End Sub

With a score of 86, this displays “B.” These decimal endpoints make the intended ranges explicit for decimal scores; if scores are guaranteed to be whole numbers, integer ranges such as 80 To 89 are simpler. The fallback also catches values below zero or above 100. Decide deliberately how your own data should treat those values.

Test Conditions with Select Case True

When branches involve different logical tests rather than values from one simple list, VBA can use Select Case True. Each case expression is evaluated as a condition, and the first condition that is true wins. This can make a set of ordered thresholds easier to scan, although a conventional If...ElseIf chain may be more familiar for complicated logic.

Option Explicit

Sub CheckStockLevel()
    Dim units As Long
    Dim message As String

    units = 4

    Select Case True
        Case units < 0
            message = "Check the entered quantity"
        Case units = 0
            message = "Out of stock"
        Case units < 5
            message = "Low stock"
        Case Else
            message = "Stock level is adequate"
    End Select

    MsgBox message
End Sub

For 4 units, the result is “Low stock.” Order matters: a broad condition placed before a narrower one can capture values that were meant for a later branch. In this example, the negative and zero checks come before the less-than-five test so their distinct messages remain reachable.

Compare Text and Handle Capitalization

String matching is a common source of surprises. By default, VBA uses binary comparison unless the module specifies a different comparison setting, so capitalization can affect whether two strings match. To make comparisons in a module case-insensitive, place Option Compare Text at the top of that module, before its procedures. It changes text comparisons throughout that module, not just one Select Case.

Option Compare Text
Option Explicit

Sub CategorizeDepartment()
    Dim department As String
    Dim category As String

    department = "Sales"

    Select Case department
        Case "sales"
            category = "Commercial"
        Case "support"
            category = "Service"
        Case Else
            category = "Other"
    End Select

    MsgBox category
End Sub

With Option Compare Text, “Sales” matches the “sales” case and the message is “Commercial.” If you keep the default binary comparison, the capitalization must match. Also consider leading or trailing spaces from worksheet data: a cell containing Sales may not match Sales. Clean the value before comparing if that is appropriate; for example, assign Trim$(department) to a string variable, then use that variable in the selection.

Read Worksheet Values and Write Results

This complete macro reads scores from column A on the active worksheet, classifies them, and writes a label in column B. It starts at row 2, assuming row 1 contains headers, and finds the last used row in column A. Non-numeric or blank entries receive a prompt to check the score rather than being treated as valid numbers.

Option Explicit

Sub LabelScoresOnActiveSheet()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNumber As Long
    Dim scoreValue As Variant
    Dim result As String

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For rowNumber = 2 To lastRow
        scoreValue = ws.Cells(rowNumber, "A").Value

        If IsNumeric(scoreValue) And Len(CStr(scoreValue)) > 0 Then
            Select Case CDbl(scoreValue)
                Case 90 To 100
                    result = "A"
                Case 80 To 89.99
                    result = "B"
                Case 70 To 79.99
                    result = "C"
                Case 60 To 69.99
                    result = "D"
                Case 0 To 59.99
                    result = "F"
                Case Else
                    result = "Check score"
            End Select
        Else
            result = "Check score"
        End If

        ws.Cells(rowNumber, "B").Value = result
    Next rowNumber
End Sub

If A2 contains 86, the macro writes B in B2. If A3 is blank, it writes “Check score” in B3. The macro changes cells, so save a copy of the workbook before running it; macro actions cannot be undone with Ctrl+Z. It operates on whichever sheet is active when started, so confirm the correct sheet is selected first.

Choose Select Case or If…Then

Choose the structure that makes the decision easiest to understand and maintain. Select Case is usually a good fit when one expression—such as a status, menu choice, or score—is compared against a number of distinct values or ranges. It puts the tested expression in one place and gives each outcome a visible branch.

An If...Then chain is often clearer when conditions combine different variables, use several operators, or depend on one another. For example, checking that a date is within a period and that a status is also approved may be easier to express with If. Neither structure is inherently faster for ordinary workbook decision logic in a way that should drive the choice; prefer readable, correct conditions.

  • Use Select Case value for grouped exact matches and ranges.
  • Use Select Case True for a short, ordered list of related conditions.
  • Use If...ElseIf when each branch has substantially different logic or combines multiple tests.

Common Errors and Debugging Tips

Most problems come from data types, boundaries, or assumptions about matching. A variable declared as String will not behave like a numeric variable in every comparison, and a worksheet cell may contain an error value, blank, or text even when the column is intended to hold numbers. Validate worksheet input before converting it with functions such as CDbl.

  • Missing fallback: Without Case Else, unexpected values simply pass through without a matching action. Add a fallback when unrecognized input needs attention.
  • Overlapping cases: VBA runs the first matching branch, not every branch. Arrange conditions carefully, especially with Select Case True.
  • Uncovered endpoints: Check whether the values at each boundary are included. A range written as 80 To 89 does not include 89.5.
  • Text mismatch: Confirm capitalization settings and remove unwanted spaces if the source data may contain them.
  • Wrong active sheet: A macro using ActiveSheet writes to the selected sheet. Qualify the target worksheet explicitly if the macro must always use a particular sheet.

In the VBA editor, use Debug > Compile VBAProject to catch many syntax and compile-time problems. To inspect runtime behavior, set a breakpoint by clicking beside a code line, then run the macro and step through it. The Locals window and Watch window can help show variable values as each case is evaluated.

Run a VBA Example Safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste a procedure into the module. If the Developer tab is not visible, turn it on through File > Options > Customize Ribbon and select Developer. Run the cursor’s procedure with F5 in the editor, or use Developer > Macros in Excel to select and run a macro.

On Excel for Mac, Option+F11 opens the Visual Basic Editor on many keyboards; a keyboard’s Fn key or its settings may affect the function-key combination. The Developer tab can be enabled in Excel’s ribbon settings. Excel for the web does not create, edit, or run VBA macros, so use the desktop application. Macros may also be restricted by security settings or organizational policy. Only enable macros from workbooks and sources you trust, and keep a backup before a macro edits workbook data.

Frequently Asked Questions

Does VBA Select Case stop after the first match?
Yes. VBA runs the first matching Case block and then moves to the code after End Select. It does not continue through later cases.

Can one Case contain multiple values?
Yes. Separate values with commas, as in Case "Open", "Pending". Both values will select that same block, subject to the module’s text comparison setting.

Can Select Case check whether a number is greater than a value?
Yes. Use a comparison such as Case Is > 10, or use Select Case True with Boolean conditions. Ensure the branches are ordered so a broader test does not hide a more specific one.

Is Select Case case-sensitive in VBA?
By default, string comparison is binary, which distinguishes capitalization. Add Option Compare Text at the top of the module for text comparisons that ignore case in that module.

Can I use VBA macros in Excel for the web?
No. Excel for the web can preserve a workbook containing VBA, but it cannot create, edit, or run those macros. Open the workbook in desktop Excel to use the VBA editor and run VBA code.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.