Excel VBA Select Case: Syntax and Examples

In Excel VBA, Select Case runs one block of code based on the value of an expression, making it a clear alternative to a long chain of If...ElseIf statements. Use it when one value—such as a status, score, or amount—needs to be compared with several possible values or ranges.

What Select Case does

A Select Case statement evaluates an expression and checks it against the conditions in its Case clauses. VBA runs the first matching block, then continues after End Select; it does not run every matching block. If nothing matches and there is no Case Else, VBA simply continues without running a case block.

This structure is useful when the branches all depend on the same value. For example, a macro can read a delivery status from a cell and choose a message for values such as Shipped, Pending, or Cancelled. The selected expression can be a variable, a cell value, or another expression that VBA can evaluate.

Basic syntax and how it works

The statement starts with Select Case followed by the expression to evaluate. Each Case introduces one or more values or conditions; the matching instructions go below it. End Select closes the structure. The optional Case Else is a catch-all and is conventionally placed last.

VBA checks the cases in the order they appear. This matters when conditions overlap: if a value could match more than one case, only the first matching case runs. Put more specific conditions before broader ones to make the intended result clear. The expression after Select Case is evaluated once, which can also make this easier to read than repeating the same calculation in multiple If conditions.

Compare several exact values

A case can contain one value or a comma-separated list of alternatives. This is handy when different inputs should produce the same result. In the example below, a macro reads a status from cell B2 on the Orders sheet, normalizes its capitalization and surrounding spaces, then writes a standardized label to C2.

Sub LabelOrderStatus()
    Dim paymentStatus As String
    Dim resultText As String

    ' Read and normalize the status in Orders!B2.
    paymentStatus = LCase$(Trim$(CStr(Worksheets("Orders").Range("B2").Value)))

    Select Case paymentStatus
        Case "paid", "complete"
            resultText = "Ready to process"
        Case "pending", "processing"
            resultText = "Still in progress"
        Case "cancelled", "canceled"
            resultText = "Do not process"
        Case Else
            resultText = "Check the status"
    End Select

    Worksheets("Orders").Range("C2").Value = resultText
End Sub

Trim$ removes leading and trailing spaces, while LCase$ converts the text to lowercase. This makes inputs such as PAID and Paid match the same case. The list after a Case means any listed alternative selects that block; it does not mean all the values must match.

Match ranges and comparison conditions

Use To for an inclusive range, such as Case 1 To 59, or use Is with a comparison operator for a threshold, such as Case Is >= 90. These forms work well for grades, quantity bands, and other numeric categories. Here is a complete example that reads a score from B2 and places a result in C2:

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

    ' Read the numeric score from the active sheet.
    score = CDbl(ActiveSheet.Range("B2").Value)

    Select Case score
        Case Is < 0, Is > 100
            grade = "Check score"
        Case 0 To 59
            grade = "F"
        Case 60 To 69
            grade = "D"
        Case 70 To 79
            grade = "C"
        Case 80 To 89
            grade = "B"
        Case 90 To 100
            grade = "A"
        Case Else
            grade = "Check score"
    End Select

    ActiveSheet.Range("C2").Value = grade
End Sub

The range endpoints are included: a score of 90 belongs to the A case, and 59 belongs to the F case. The first case also shows that comparisons can be combined with a comma. The conversion with CDbl assumes B2 contains a value VBA can convert to a number; a blank or nonnumeric entry may raise a type-conversion error. If the worksheet contains untrusted or inconsistent input, validate it before converting.

Choosing between Select Case and If

Use Select Case when several branches test the same expression against distinct values, ranges, or comparison conditions. It keeps the expression in one place and makes a long list of alternatives easier to scan. It is not automatically faster or better in every situation; the main benefit is usually clarity.

Use If...Then...ElseIf when each decision involves a different variable, a more complex logical test, or conditions that combine several facts. For instance, deciding whether an order is eligible based on both its status and its amount may be clearer as an If statement. A useful rule is:

Choose Select Case when one expression is being sorted into alternatives.
Choose If when the decision combines different expressions or requires more elaborate Boolean logic.
For one simple yes-or-no condition, a short If statement is usually more direct.

VBA also allows the pattern Select Case True, where each Case contains a Boolean condition. It can be useful in limited situations, but it often obscures the decision compared with ordinary If...ElseIf logic. Prefer a direct Select Case on the value when possible.

Common mistakes and how to avoid them

A frequent problem is leaving out Case Else. Without it, unexpected values are silently ignored, which may leave an output cell unchanged or a result variable empty. Add a fallback when an unknown value should trigger a warning, review label, or safe default.

Other issues to check include:

Overlapping conditions: VBA uses the first matching case, so reorder the cases if a broad condition appears before a more specific one.
Gaps in numeric ranges: for example, ranges ending at 59 and starting again at 61 do not account for 60. Check the boundary values and decide whether decimals are possible.
Text differences: spaces, spelling, and capitalization can affect matching. Normalize input where appropriate, or account for expected variations in the cases.
Wrong data type: comparing text with numeric cases or converting an invalid cell value can cause unexpected results or errors. Check input before conversion.
Code placed outside the structure: every Select Case needs a corresponding End Select, and each case block must be inside that structure.

Text comparisons also depend on the module’s comparison setting. VBA uses binary comparison by default, which distinguishes uppercase and lowercase letters. A module can use Option Compare Text at the top to make text comparisons case-insensitive, but normalizing input with LCase$ or UCase$ makes that choice visible in the procedure. Do not add Option Compare inside a procedure; it belongs in the module declarations.

Run and protect a VBA macro

In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, insert a standard module through Insert > Module, and place the procedure in that module. You can run a macro from the editor with the cursor inside its procedure by pressing F5, or return to Excel and use Developer > Macros to select and run it. If the Developer tab is not visible, enable it in Excel Options under Customize Ribbon.

On a Mac, the VBA editor is available through Tools > Macro > Visual Basic Editor; the keyboard shortcut can vary with the keyboard and function-key settings, commonly requiring Option+F11 or Fn+Option+F11. VBA is supported in desktop Excel for Mac, although some Windows-specific features and APIs are not. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros.

Before running a macro that changes a workbook, save a separate copy. VBA changes cannot be reversed with Ctrl+Z on Windows or Command+Z on Mac. If you need to keep the macro, save the workbook as an Excel Macro-Enabled Workbook (.xlsm); a standard .xlsx file does not store VBA code. Only enable macros in files you trust.

Frequently Asked Questions

Does Select Case stop after the first match?
Yes. VBA runs the instructions under the first matching Case and then continues after End Select. It does not fall through to later matching cases.

Is Case Else required?
No. It is optional, but it is useful when you want to handle an unexpected value explicitly. Without it, VBA does nothing within the structure if no case matches.

Can one Case contain multiple values?
Yes. Separate alternatives with commas, as in Case "paid", "complete". Either matching value selects the same block.

Can Select Case test a range?
Yes. Use Case lowerValue To upperValue for an inclusive range, or a comparison such as Case Is >= 100. Check for gaps and overlaps when defining boundaries.

Can I use Select Case with text?
Yes. It can compare strings as well as numbers. Matching may depend on the module’s text comparison setting, and leading or trailing spaces can cause surprises; normalize text when appropriate.

Can I use Select Case in an Excel worksheet formula?
No. Select Case is a VBA language statement, not a worksheet function. In a cell formula, use functions such as IFS or SWITCH when they fit the task, or use VBA inside a macro.

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.