Excel VBA Case Statement: How Select Case Works

Excel VBA does not have a separate Case statement; it uses Select Case ... End Select to choose and run one block of code based on a value. Use it when one expression needs to be compared with several possible values, ranges, or conditions; for a simple two-way decision, an If...Then...Else statement may be clearer.

What Select Case Means in VBA

Select Case is VBA’s multi-branch decision structure. VBA evaluates the expression after Select Case, checks the listed Case clauses in order, and runs the statements under the first matching clause. It then continues at the first statement after End Select. It does not run every matching branch, and it does not fall through from one branch into the next.

This makes it useful when a macro must react to a status, category, score, menu choice, or other value with several possible outcomes. It can make such logic easier to scan than a long chain of repeated comparisons. The expression can be a variable, a property, or another VBA expression that produces a value.

Basic Syntax and a First Example

The general structure is shown below. The Case Else branch is optional, but it is often a good way to handle values that do not match any listed case.

Sub ShowScoreBand()
    Dim score As Long
    Dim result As String

    score = 84

    Select Case score
        Case 90 To 100
            result = "Excellent"
        Case 80 To 89
            result = "Good"
        Case 70 To 79
            result = "Pass"
        Case Else
            result = "Needs review"
    End Select

    MsgBox result
End Sub

With score set to 84, the macro displays Good. The ranges include both endpoints, so 80 To 89 matches 80 and 89 as well as the values between them. In this example, a score below 70 or above 100 falls into Case Else; if those values should be handled differently, add explicit cases or validate the score before choosing a result.

Values, Lists, Ranges, and Comparisons

A Case clause can match a single value, a list of values, an inclusive range, or a comparison. These forms cover many everyday decisions without repeating the expression being tested:

One exact value: Case "Paid" matches the string Paid.
Several exact values: Case 1, 3, 5 matches any one of those numbers.
A range: Case 10 To 20 matches values from 10 through 20, including both ends.
A comparison: Case Is >= 90 matches a value greater than or equal to 90.

The keyword Is in a case clause introduces a comparison operator; it is not the same as using VBA’s Is operator to compare object references. For example, Case Is < 0 is a numeric condition. A case clause can also contain a comma-separated set of choices, such as Case "Queued", "In progress". Choose the form that makes the rule easiest to understand, and keep the tested expression and the case values compatible in type.

Practical Example: Categorize Worksheet Statuses

A useful Excel macro can read a status in column A and write a standardized category in column B. This example assumes the workbook contains a worksheet named Orders and that statuses are in A2:A100. It trims extra spaces and converts the input to uppercase so that values such as paid, Paid, and PAID are handled alike.

Sub CategorizeOrderStatuses()
    Dim ws As Worksheet
    Dim cell As Range
    Dim status As String

    Set ws = ThisWorkbook.Worksheets("Orders")

    For Each cell In ws.Range("A2:A100")
        status = UCase$(Trim$(CStr(cell.Value)))

        Select Case status
            Case "PAID"
                cell.Offset(0, 1).Value = "Complete"
            Case "PENDING", "PROCESSING"
                cell.Offset(0, 1).Value = "In progress"
            Case "CANCELLED"
                cell.Offset(0, 1).Value = "Closed"
            Case Else
                cell.Offset(0, 1).Value = "Check status"
        End Select
    Next cell
End Sub

CStr converts the cell value to text before the string functions are applied, while Trim$ removes leading and trailing spaces. UCase$ standardizes the case for matching. The Case Else output makes unexpected or blank entries visible for review rather than silently treating them as a known status. Adjust the range and worksheet name to fit the workbook before running the macro.

Case Order, Overlaps, and the Default Branch

VBA uses the first matching Case clause, so order matters whenever the conditions overlap. For example, if a score is 95, both Case Is >= 90 and Case 80 To 100 could match. Put the more specific condition first if it should take precedence. Better still, arrange ranges so they do not overlap when that reflects the intended rules.

Case Else is not required, but it makes the behavior explicit when none of the listed cases match. Without it, VBA simply moves on after End Select. A default branch can provide a useful fallback, report an unexpected value, or leave a clear place to add handling later. It should not conceal invalid data: a generic result such as Check status is often more informative than assigning a normal category to an unknown value.

There is no Exit Select statement in VBA. Only one matching branch runs, and control proceeds after the structure automatically. If a branch needs to leave the whole procedure early, Exit Sub or Exit Function may be appropriate, but those statements exit the procedure, not just the Select Case.

Select Case or If…Then?

Use Select Case when several alternatives all depend on the same expression, such as a status string or a numeric score. The value appears once at the top, and the possible outcomes are grouped beneath it. It is especially readable when there are several exact matches, alternative values, or clear ranges.

Use If...Then...ElseIf when the logic combines different variables or conditions that do not all test one expression. For example, a rule involving both an order status and an amount may be more naturally expressed with If. An If chain can also express compound conditions with And or Or, which is useful when the decision depends on several facts at once.

Neither structure is inherently faster or more correct for every situation. Prefer the structure that states the business rule plainly, and avoid nesting either one more deeply than necessary. A long list of categories is often easier to maintain in a worksheet lookup table than in code, particularly when non-programmers need to change the categories regularly.

Common Errors and How to Avoid Them

Leaving out the end marker: Every Select Case structure needs a matching End Select. Indent each branch to make missing or misplaced lines easier to spot.
Using a range as though it were a list: Case 1 To 5 is a continuous inclusive range. Use commas for separate choices, such as Case 1, 3, 5.
Assuming text comparisons are always case-insensitive: VBA’s default module setting is Option Compare Binary, which compares strings according to binary ordering. Option Compare Text changes string comparison behavior for that module. Alternatively, normalize the value with a function such as UCase$ or LCase$, as in the worksheet example.
Mixing incompatible values: A number read from a cell may actually be text, and a blank cell may need special treatment. Check the source data and convert or validate it deliberately instead of relying on implicit conversions.
Putting a broad case first: If an earlier clause matches a value, a later overlapping clause will not run. Review ranges and comparisons from top to bottom.
Expecting a branch to continue into the next one: VBA does not use C-style switch fall-through here. Put all equivalent choices in one clause, or write the required statements in the matching branch.

If the rules become hard to follow, split the work into smaller procedures or move editable mappings into a table. Many confusing case errors are really signs that the source values need cleaning or the decision rules need to be documented more clearly.

Running VBA Safely in Excel

VBA macros run in the desktop version of Excel, not in Excel for the web. On Windows, open the Visual Basic Editor with Alt+F11. On Mac, use Option+F11; depending on the keyboard settings, you may also need to hold Fn. You can also open the editor from the Developer tab using Visual Basic. Paste a complete procedure into a standard module, then run it from the editor or Excel’s macro interface.

Save a workbook containing macros in a macro-enabled format such as .xlsm. Excel may display a security warning when a file contains macros. Enable macros only for files from a source you trust; do not weaken security settings just to run an unfamiliar file. Macros can change or overwrite workbook data, and they cannot be undone with Ctrl+Z (or Command+Z on Mac). Save a separate copy of the workbook before running a macro, especially while learning or adapting code.

Frequently Asked Questions

Does VBA have a Case statement?

VBA’s equivalent is the Select Case structure. It begins with Select Case, contains one or more Case clauses, and ends with End Select.

How do I write multiple values in one Case?

Separate the alternatives with commas. For example, Case "Open", "Pending" runs the same branch when the selected expression equals either string.

Can I use a range in Select Case?

Yes. Write a range with To, such as Case 1 To 5. The endpoints are included, so this matches 1, 2, 3, 4, and 5.

Is Select Case case-sensitive for text?

By default, VBA uses binary string comparison in a module. You can use Option Compare Text at the top of a module to change its comparison setting, or normalize the text first with UCase$ or LCase$.

Can Excel for the web run a VBA macro?

No. Excel for the web can open and edit many workbooks, but it cannot create, edit, or run VBA macros. Use the Excel desktop application to work with VBA.

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.