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 valuefor grouped exact matches and ranges. - Use
Select Case Truefor a short, ordered list of related conditions. - Use
If...ElseIfwhen 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
ActiveSheetwrites 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
- Excel Shortcut to Select All Data in a Column Quickly
- Excel Shortcut to Select a Whole Column in Seconds
- Excel Shortcut to Insert a Row: Windows and Mac
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary Exists: Check Whether a Key Is PresentYes. In VBA, a Scripting.Dictionary has an Exists method that returns True when a specified key is present…
- Excel VBA: Open a File Based on a Cell Value SafelyTo open a workbook whose path is stored in a cell, read that cell into a VBA string…
- VBA Code to Send an Email With an AttachmentTo send an email with an attachment from Excel VBA on Windows, create an Outlook mail item, add…
- Excel VBA Array Filter: Methods, Code, and ExamplesTo filter a one-dimensional array of strings in Excel VBA, use the built-in Filter function, for example Filter(names,…
- Dim VBA String: How to Declare and Use Text VariablesIn VBA, declare a text variable with Dim variableName As String, such as Dim customerName As String. The…
- Excel VBA Trim Text: What It Removes and How to Use ItIn Excel VBA, use Trim$(textValue) to remove ordinary spaces at the beginning and end of a string; it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.