The End Select statement in Excel VBA closes a Select Case block, marking the end of the conditional structure. Without it, your code will not compile, showing a “Block If without End If” or similar syntax error. Here’s how to use it correctly, what it does, and how to avoid common mistakes.
What Is Select Case and Why End Select Matters
Select Case is a VBA branching structure that tests an expression against one or more values or conditions, running the first matching block. It is cleaner than a long chain of If...ElseIf statements when you have multiple discrete cases. The structure looks like this:
Select Case score
Case Is >= 90
grade = "A"
Case Is >= 80
grade = "B"
Case Else
grade = "F"
End Select
The End Select line is mandatory—it tells VBA where the block ends. If you omit it, VBA throws a compile error and the macro will not run. Think of it as the closing parenthesis for the whole construct.
How to Write a Select Case Block with End Select
Follow these steps in the VBA editor (Alt+F11):
- Open the module where you want the code.
- Type
Select Casefollowed by the expression to test (e.g., a variable or cell value). - Add one or more
Caselines with conditions. - Include an optional
Case Elsefor anything not matched. - Close with
End Select.
Example using a cell value:
Sub CheckDay()
Dim dayNum As Integer
dayNum = Range("A1").Value
Select Case dayNum
Case 1
MsgBox "Monday"
Case 2
MsgBox "Tuesday"
Case Else
MsgBox "Other day"
End Select
End Sub
Save a copy of your workbook before running any macro, as macros cannot be undone with Ctrl+Z.
Case Expressions: What You Can Test
The Case line can use several forms:
- Exact values:
Case 5orCase "Red" - Ranges:
Case 1 To 5 - Comparison operators:
Case Is > 10 - Multiple values:
Case 1, 3, 5 - Mixed conditions:
Case Is < 0, 100 To 200
You can also use Case Else to catch everything else. The expression after Select Case can be a number, string, date, or any valid VBA expression. For strings, comparisons are case-insensitive by default unless you add Option Compare Binary at the top of the module.
Common Mistakes and How to Fix Them
Here are frequent errors beginners hit:
- Forgetting End Select: You’ll see “Compile error: End Select without Select Case” or “Block If without End If”. Add the missing line.
- Extra End Select: If you have an extra one, VBA says “Select Case without End Select”. Remove the duplicate.
- Nesting without proper closure: If you put a
Select Caseinside another, each must have its ownEnd Select. The inner one closes first. - Using
End Ifinstead: These are different structures.End Ifcloses anIfblock, not aSelect Case.
Example of nesting:
Select Case a
Case 1
Select Case b
Case 2
MsgBox "a=1, b=2"
End Select
Case Else
MsgBox "other"
End Select
Notice the inner End Select comes before the outer one.
Select Case vs. If-ElseIf: Which to Use?
Both work, but Select Case is often more readable when you have three or more distinct conditions. It evaluates the selector expression once, then compares it to each case. If...ElseIf can handle more complex logical combinations (e.g., If x > 0 And y < 0).
Performance differences are negligible in normal use. Choose based on clarity. For a simple two-way decision, an If is fine. For a menu of options, Select Case is cleaner.
Example of a complex condition that Select Case cannot easily handle:
If x > 0 And y > 0 Then
MsgBox "Both positive"
ElseIf x < 0 And y < 0 Then
MsgBox "Both negative"
Else
MsgBox "Mixed"
End If
You would need to use Case Is > 0 on a combined expression, which is awkward. Stick with If for such logic.
End Select in Other Contexts: With or Without Select Case
There is no standalone End Select—it only works as a terminator for Select Case. You cannot use it to end a loop or an If block. Some users confuse it with End With (closes a With block) or End Sub (ends a procedure).
Also note that Select Case can be used in a Sub, Function, or even inside a loop. The End Select always pairs with the nearest unmatched Select Case.
Frequently Asked Questions
Can I omit End Select if I only have one Case?
No. Every Select Case must end with End Select, regardless of how many cases you have. Omitting it causes a compile error.
What is the difference between End Select and End If?End Select closes a Select Case block; End If closes an If...Then...Else block. They are not interchangeable.
Does End Select need a colon or semicolon after it?
No. In VBA, line breaks are the separators. You can put End Select on its own line, or use a colon to put it after another statement, but that is rare and not recommended.
Can I use Exit Select to leave a Select Case early?
Yes, but it is rarely needed. Exit Select jumps out of the block immediately, skipping remaining cases. Use it sparingly, as it can make code harder to follow.
Why do I get “Compile error: End Select without Select Case”?
This means you have an End Select that does not match any open Select Case. Check for a missing Select Case line or an extra End Select.
Does Select Case work with text strings?
Yes. You can test strings directly, like Case "Yes" or Case "A" To "C". Remember that comparisons are case-insensitive by default.
Related
- Free Excel Project Timeline Templates: How to Get One
- Excel Print Merge File Showing X? Fixes
- Excel Profit and Loss Template: Free Options
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Import CSV to Excel Using VBA: A Practical GuideTo import a CSV file into Excel with VBA, use Workbooks.OpenText to parse the file, then copy its…
- Macro Recorder for Windows 11: Options That Actually WorkWindows 11 does not include a universal built-in macro recorder that can record and replay actions across every…
- SteelSeries Macros: How to Create and Assign OneTo get a macro on a SteelSeries device, open SteelSeries GG, choose your device in Engine, create or…
- Excel VBA Loop Over an Array: Examples and PatternsTo loop over a VBA array in Excel, use For Each when you need to process every item,…
- VBA Range Variables: How to Declare and Use ThemA VBA Range variable is an object variable that refers to one or more cells in an Excel…
- Excel UserForm Grid Size: Spacing, Settings, and LayoutIn Excel VBA, a UserForm’s design grid is adjustable in the Visual Basic Editor; it does not have…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.