Excel VBA does not have a switch-case statement named Switch; its equivalent is Select Case. Use it to run one block of code based on a value or expression, with Case Else as an optional fallback when no case matches.
Why VBA Uses Select Case
Many programming languages use a construct called switch or switch-case to compare one value against several alternatives. VBA uses different wording for the same general job: Select Case. The name can be confusing if you are adapting code from another language, but the basic idea is familiar: evaluate a test expression, find the first matching case, run its statements, and continue after End Select.
For example, a macro might assign a shipping label based on an order total, choose an action based on a status field, or translate a numeric score into a grade. When the alternatives all depend on the same value, Select Case is often easier to scan than a long chain of If...ElseIf statements.
There is also a VBA function named Switch, but it is not a switch-case block. It takes condition-and-result pairs and returns a result. For ordinary branching statements, Select Case is usually the clearer choice.
Basic Select Case Syntax
The general structure is:
Option Explicit
Sub CheckStatus()
Dim status As String
status = "Open"
Select Case status
Case "Open"
Debug.Print "Work is still in progress."
Case "Closed"
Debug.Print "The work is complete."
Case Else
Debug.Print "The status is not recognized."
End Select
End Sub
Select Case status identifies the expression to test. Each Case lists a value or condition that could match it. VBA runs the statements under the first matching case and then leaves the structure at End Select; it does not continue into the following case as a C-style switch might. Case Else is optional, but it is useful when unexpected values need a defined response.
The example uses Debug.Print to show the result in the Visual Basic Editor’s Immediate window. In a worksheet macro, the statements inside a case can instead assign a cell value, update a variable, or call another procedure.
Match One Value, Several Values, or a Range
A case can contain one value, multiple comma-separated values, or a range. The following macro reads a numeric total from B2 on the active worksheet and writes a label in C2. It assumes B2 contains a number; it does not include input validation for text or Excel error values.
Option Explicit
Sub LabelOrderTotal()
Dim ws As Worksheet
Dim orderTotal As Currency
Dim label As String
Set ws = ActiveSheet
orderTotal = ws.Range("B2").Value
Select Case orderTotal
Case Is < 0
label = "Check the amount"
Case 0 To 99.99
label = "Small order"
Case 100 To 499.99
label = "Standard order"
Case Is >= 500
label = "Large order"
Case Else
label = "Unclassified"
End Select
ws.Range("C2").Value = label
End Sub
With B2 equal to 250, the macro assigns “Standard order” to C2. The endpoints in a To range are included, so 99.99 matches the first nonnegative range and 100 matches the next one. Is introduces a comparison such as less than, greater than, or equal to. It is not required for a simple value or range.
To group exact values, separate them with commas. For instance, Case "NY", "CA", "WA" matches any one of those three text values. A range is suited to continuous values; a comma-separated list is suited to distinct alternatives.
How VBA Chooses a Matching Case
VBA checks the cases in the order written and runs the statements for the first match. This makes the order important when conditions overlap. In the numeric example, a broad case placed before a narrower one could catch values that you intended to handle separately.
- Put special or narrow conditions before broad conditions when their matching values overlap.
- Check range boundaries carefully so adjacent ranges do not leave gaps or unintentionally overlap.
- Use
Case Elseto make the behavior for unmatched values explicit. - Keep each case’s action short enough that a reader can see the relationship between the condition and the result.
For example, if a score of 100 needs a special message, test that exact value before a general range such as 0 To 100. Otherwise, the range matches first and the special case is never reached. This first-match behavior is a common source of logic errors.
Using Select Case with Text
Select Case works with text as well as numbers. Here is a simple example that reads a status from A2 and writes an instruction in B2. It assumes the cell contains a text value and uses the active worksheet.
Option Explicit
Sub ExplainStatus()
Dim ws As Worksheet
Dim status As String
Dim instruction As String
Set ws = ActiveSheet
status = CStr(ws.Range("A2").Value)
Select Case status
Case "New"
instruction = "Review the request."
Case "In progress", "Waiting"
instruction = "Check the next action."
Case "Done"
instruction = "No further action is needed."
Case Else
instruction = "Confirm the status value."
End Select
ws.Range("B2").Value = instruction
End Sub
String comparison behavior can depend on the module’s Option Compare setting. If no setting is specified, VBA uses binary comparison, which distinguishes text according to its character values; Option Compare Text makes text comparisons case-insensitive. This setting belongs at the top of a module, before procedures. Decide deliberately if your data may contain differences in capitalization, and test with realistic values. Extra spaces are a separate issue: “Done” and “Done ” are not the same string unless you trim the input.
Select Case True for Multiple Conditions
Most Select Case blocks compare one test expression with case values. A related pattern is Select Case True, which lets each case hold a Boolean condition. It can be useful when several conditions classify the same input, although an If...ElseIf chain may be more immediately familiar.
Option Explicit
Sub ClassifyScore()
Dim score As Double
Dim result As String
score = 84
Select Case True
Case score >= 90
result = "A"
Case score >= 80
result = "B"
Case score >= 70
result = "C"
Case Else
result = "Below C"
End Select
Debug.Print result
End Sub
For a score of 84, this prints “B.” The conditions are checked in order, so the highest threshold comes first. If score >= 70 appeared first, a score of 95 would match it and the later, more specific conditions would not run. Use this style only when the order is clear; for complicated rules, separate Boolean variables or If...ElseIf statements can be easier to understand.
Select Case Compared with If and Switch
Choose the control structure that best expresses the logic rather than choosing one simply because it is shorter.
- Use
Select Casewhen one value is being compared with several alternatives, especially exact values or numeric ranges. - Use
If...ElseIfwhen conditions involve different variables, compound tests joined withAndorOr, or logic that is not naturally a set of cases. - Consider the
Switchfunction when an expression should return one of several values. It is a function, not a block of statements, and returnsNullif no condition is true. ASelect Caseblock is generally clearer when you need to perform several statements or handle a fallback explicitly.
For example, checking whether a status equals “Open,” “Closed,” or “Waiting” is a natural Select Case job. Testing whether a cell is blank and another cell is greater than a limit involves separate conditions, so an If statement is usually more direct.
Run a VBA Macro Safely in Excel
In Microsoft Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. Choose Insert > Module, paste the procedure into the module, and run it with the cursor inside the procedure by pressing F5. You can also return to Excel and run a macro from Developer > Macros. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon, then select Developer.
On a Mac, VBA is available in the desktop Excel app, though menus and keyboard shortcuts differ. The Visual Basic Editor can generally be opened with Option+F11; some keyboards require Fn as well. Use the Mac ribbon or menus to insert a module and run a macro if the shortcut differs on your keyboard. Excel for the web does not create, edit, or run VBA macros; open the workbook in desktop Excel to use VBA.
Before running any macro, save a separate copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, so a macro that changes or clears cells may be difficult to undo. Save a workbook that contains VBA in a macro-enabled format such as .xlsm; saving as a standard .xlsx file does not preserve VBA code. Only enable macros in files you trust, and do not lower macro security settings just to run code from an unknown source.
Common Problems and Fixes
- Using “Switch” as a statement: VBA expects
Select CaseandEnd Select. A line beginning withSwitchdoes not create a switch-case block. - Missing a case or fallback: If no case matches and there is no
Case Else, VBA simply continues afterEnd Select. Add a fallback when silent omission could hide bad input. - Putting cases in the wrong order: Overlapping conditions are resolved by the first match. Move more specific conditions earlier.
- Unexpected text matches: Check capitalization, leading or trailing spaces, and the module’s
Option Comparesetting. Convert or clean values consistently where appropriate. - Type mismatch: A numeric variable cannot safely receive arbitrary text or an Excel error value. Validate worksheet input before assigning it to a typed variable if users can enter anything.
- Writing to the wrong sheet:
ActiveSheetrefers to whichever sheet is active when the macro runs. If the procedure must always use a particular sheet, reference that worksheet explicitly rather than relying on the current selection.
Frequently Asked Questions
Does VBA have a switch-case statement?
No. VBA’s branching equivalent is Select Case. VBA also has a Switch function, but it returns a value from condition-and-result pairs rather than running a switch-case block.
Do I need Case Else?
No, it is optional. Add it when you want a defined response for values that do not match any listed case, such as flagging an unknown status or assigning a default result.
Can one Case contain multiple values?
Yes. Separate exact alternatives with commas, as in Case "Open", "Waiting". VBA runs that case if the selected expression matches either value.
Can Case test a range?
Yes. Use Case 10 To 20 for an inclusive range, or a comparison such as Case Is > 20. Check endpoints and case order, especially when ranges overlap.
Can I use Select Case in an Excel worksheet formula?
No. Select Case is a VBA language structure used in macros and other VBA procedures, not an Excel cell formula. For worksheet logic, use functions such as IF or IFS where they fit; for VBA logic, use Select Case or If.
Related
- Hide Column in Tableau: Steps and Tips
- Group Rows in Smartsheet: Sheets, Reports, and Views
- Numbers Text to Columns: Split Your Data Correctly
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA MsgBox Buttons: Options, Examples, and Return ValuesIn VBA, the MsgBox function’s buttons argument controls which buttons and icon appear, which button is selected by…
- Excel Code to Insert a Row: VBA and ShortcutsTo insert a worksheet row with Excel VBA, use Rows(ActiveCell.Row).Insert Shift:=xlDown in a macro; it inserts a row…
- Macro Recorder for Mac: Record and Run Excel MacrosIn Excel for Mac, turn on the Developer tab, choose Developer > Record Macro, perform the steps you…
- Excel VBA Can’t Find Project or Library? Fix the ErrorIf Excel VBA says “Can’t find project or library,” the VBA project usually has a reference to a…
- VBA User Input Box: InputBox, Validation, and ExamplesA VBA user input box is a small dialog that asks someone to enter text, a number, or…
- Excel VBA If Statements: Syntax, Examples, and TipsIn Excel VBA, an If statement runs code only when a condition is true; use ElseIf for additional…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.