Select Case in VBA
Learn how to handle multiple conditions using Select Case, Case Else, multiple values, ranges, comparison expressions, and real-world Excel VBA examples.
1. What Is Select Case?
Select Case is a conditional statement in VBA
used to execute different blocks of code depending on the
value of an expression. It is especially useful when one
variable needs to be checked against several possible values
or ranges.
For example, you can use Select Case to assign student grades, calculate employee incentives, identify a month from its number, or classify a sales amount.
End Select.
2. Syntax of Select Case
Select Case expression
Case value1
' Statements for value1
Case value2
' Statements for value2
Case Else
' Statements when no Case matches
End Select
Explanation of each keyword
| Keyword | Purpose |
|---|---|
Select Case |
Starts the selection structure and specifies the expression to evaluate. |
Case |
Defines a value, list of values, range, or comparison to match. |
Case Else |
Provides a default block when no earlier Case matches. |
End Select |
Ends the Select Case structure. |
Case Else is optional,
but it is useful for handling unexpected or unmatched values.
3. Simple Select Case Example
Suppose a variable stores a number from 1 to 3. The macro displays a different message for each matching value.
Sub SimpleSelectCase()
Dim choice As Integer
choice = 2
Select Case choice
Case 1
MsgBox "You selected One."
Case 2
MsgBox "You selected Two."
Case 3
MsgBox "You selected Three."
Case Else
MsgBox "Invalid choice."
End Select
End Sub
The value of choice is 2, so VBA executes
Case 2.
4. Using Multiple Values in One Case
Use commas to specify multiple alternative values in the same Case statement. This is useful when several values should execute the same block.
Example: Identify a weekend
Sub CheckDay()
Dim dayName As String
dayName = "Sunday"
Select Case dayName
Case "Saturday", "Sunday"
MsgBox "It is a weekend."
Case "Monday", "Tuesday", "Wednesday", _
"Thursday", "Friday"
MsgBox "It is a weekday."
Case Else
MsgBox "Invalid day name."
End Select
End Sub
Both Saturday and Sunday share the same Case block.
5. Using To for Value Ranges
Use the To keyword to specify a range of values.
Both the lower and upper boundaries are included in the range.
Example: Grade calculation
Sub GradeUsingRange()
Dim marks As Integer
marks = 82
Select Case marks
Case 90 To 100
MsgBox "Grade A+"
Case 75 To 89
MsgBox "Grade A"
Case 60 To 74
MsgBox "Grade B"
Case 40 To 59
MsgBox "Grade C"
Case 0 To 39
MsgBox "Fail"
Case Else
MsgBox "Invalid marks."
End Select
End Sub
| Marks | Range | Result |
|---|---|---|
| 95 | 90 To 100 | Grade A+ |
| 82 | 75 To 89 | Grade A |
| 68 | 60 To 74 | Grade B |
| 45 | 40 To 59 | Grade C |
| 25 | 0 To 39 | Fail |
6. Using Is with Comparison Operators
In a Select Case statement, Is can be used
with comparison operators such as >,
>=, <, and
<=.
Example: Classify a sales amount
Sub ClassifySales()
Dim salesAmount As Double
salesAmount = 75000
Select Case salesAmount
Case Is >= 100000
MsgBox "Excellent Sales"
Case Is >= 50000
MsgBox "Good Sales"
Case Is >= 20000
MsgBox "Average Sales"
Case Else
MsgBox "Sales Need Improvement"
End Select
End Sub
The amount is at least 50,000 but below 100,000.
How it works: VBA evaluates Case clauses in order. Because 75,000 does not meet the first condition, it checks the second condition and executes that block.
7. Select Case with Text Values
Select Case can also evaluate text such as department names, employee categories, product names, and status values.
Example: Department identification
Sub DepartmentDetails()
Dim departmentName As String
departmentName = "IT"
Select Case departmentName
Case "HR"
MsgBox "Human Resources Department"
Case "IT"
MsgBox "Information Technology Department"
Case "Finance"
MsgBox "Finance Department"
Case "Sales"
MsgBox "Sales Department"
Case Else
MsgBox "Department not found."
End Select
End Sub
Option Compare setting. The default
is usually Binary, which distinguishes text
case. Use consistent input or explicitly normalize text
where appropriate.
8. Select Case vs If...ElseIf
Both constructs support decision-making, but Select Case is often easier to read when one expression is compared against multiple values or ranges.
| Select Case | If...ElseIf |
|---|---|
| Useful for checking one expression against several cases. | Useful for independent or complex Boolean conditions. |
| Supports multiple values using commas. | Conditions are written separately. |
Supports ranges using To. |
Ranges are expressed using comparison operators. |
Can use Case Is >= 100. |
Can combine conditions using And and Or. |
Same task using If...ElseIf
Sub GradeUsingIf()
Dim marks As Integer
marks = 82
If marks >= 90 And marks <= 100 Then
MsgBox "Grade A+"
ElseIf marks >= 75 And marks <= 89 Then
MsgBox "Grade A"
ElseIf marks >= 60 And marks <= 74 Then
MsgBox "Grade B"
ElseIf marks >= 40 And marks <= 59 Then
MsgBox "Grade C"
ElseIf marks >= 0 And marks <= 39 Then
MsgBox "Fail"
Else
MsgBox "Invalid marks."
End If
End Sub
Both approaches can produce the same result. Select Case makes this particular example easier to scan because the ranges are grouped under one expression.
9. Practical Example: Display Month Name
Suppose cell A2 contains a month number from 1 to 12. This macro converts the number into a month name and writes the result to cell B2.
| Cell | Input / Output |
|---|---|
| A2 | 10 |
| B2 | October |
Option Explicit
Sub ConvertMonthNumber()
Dim monthValue As Variant
Dim monthName As String
monthValue = ThisWorkbook.Worksheets("Sheet1").Range("A2").Value
If IsError(monthValue) Then
MsgBox "Cell A2 contains an Excel error.", vbExclamation
Exit Sub
End If
If Not IsNumeric(monthValue) Then
MsgBox "Enter a month number from 1 to 12.", vbExclamation
Exit Sub
End If
If CDbl(monthValue) <> Fix(CDbl(monthValue)) Then
MsgBox "Month number must be a whole number.", vbExclamation
Exit Sub
End If
If CDbl(monthValue) < 1 Or CDbl(monthValue) > 12 Then
MsgBox "Month number must be between 1 and 12.", vbExclamation
Exit Sub
End If
Select Case CLng(monthValue)
Case 1
monthName = "January"
Case 2
monthName = "February"
Case 3
monthName = "March"
Case 4
monthName = "April"
Case 5
monthName = "May"
Case 6
monthName = "June"
Case 7
monthName = "July"
Case 8
monthName = "August"
Case 9
monthName = "September"
Case 10
monthName = "October"
Case 11
monthName = "November"
Case 12
monthName = "December"
Case Else
MsgBox "Invalid month number.", vbExclamation
Exit Sub
End Select
ThisWorkbook.Worksheets("Sheet1").Range("B2").Value = monthName
MsgBox "Month name saved: " & monthName, vbInformation
End Sub
Explanation: The macro validates the input, maps the month number using Select Case, and stores the corresponding name in cell B2.
10. Practical Example: Employee Incentive
Suppose an employee's sales value is stored in cell B2. The macro assigns an incentive rate according to the sales amount and calculates the incentive in cell C2.
| Sales Range | Incentive Rate |
|---|---|
| 100,000 and above | 10% |
| 50,000 to below 100,000 | 7% |
| 20,000 to below 50,000 | 5% |
| Below 20,000 | 0% |
Option Explicit
Sub CalculateIncentive()
Dim salesValue As Variant
Dim incentiveRate As Double
Dim incentiveAmount As Double
salesValue = ThisWorkbook.Worksheets("Sheet1").Range("B2").Value
If IsError(salesValue) Then
MsgBox "Cell B2 contains an Excel error.", vbExclamation
Exit Sub
End If
If Not IsNumeric(salesValue) Then
MsgBox "Enter a valid sales amount.", vbExclamation
Exit Sub
End If
If CDbl(salesValue) < 0 Then
MsgBox "Sales amount cannot be negative.", vbExclamation
Exit Sub
End If
Select Case CDbl(salesValue)
Case Is >= 100000
incentiveRate = 0.1
Case Is >= 50000
incentiveRate = 0.07
Case Is >= 20000
incentiveRate = 0.05
Case Else
incentiveRate = 0
End Select
incentiveAmount = CDbl(salesValue) * incentiveRate
ThisWorkbook.Worksheets("Sheet1").Range("C2").Value = incentiveAmount
MsgBox "Incentive Amount: " & Format(incentiveAmount, "0.00"), _
vbInformation
End Sub
11. Common Mistakes to Avoid
-
Forgetting to close the structure with
End Select. -
Assuming
Case Elseis required in every program. It is optional but often recommended. - Defining overlapping ranges unintentionally.
- Placing a broad comparison before a more specific one.
-
Assuming a text value such as
"10"is always handled exactly like numeric10. - Forgetting to validate worksheet values before converting them to numeric data types.
- Using a worksheet name that does not exist in the workbook.
12. Practice Exercises
Create separate VBA procedures to solve these exercises. Test each macro with normal, boundary, and invalid values.
- Write a Select Case program to display a message for choices 1, 2, 3, and an invalid choice.
- Accept a day name and identify whether it is a weekday or weekend.
- Accept marks and assign grades using ranges.
- Accept a month number and display the corresponding month name.
- Classify sales into Excellent, Good, Average, or Needs Improvement.
- Accept an employee department and display the department details.
-
Calculate a discount based on a purchase amount using
Case Is >=. - Read a month number from cell A2 and write the month name into cell B2.
- Read employee sales from B2 and calculate the incentive in C2.
- Compare Select Case and If...ElseIf by writing both versions of a grade-calculation macro.
Important Points to Remember
Select Caseevaluates an expression.Casedefines the values or conditions to match.- Use commas to match multiple alternative values in one Case.
- Use
Tofor inclusive ranges. - Use
Case Is >= valuefor comparison conditions. Case Elsehandles unmatched values.- Validate Excel data before processing it.
- Use Select Case when it improves readability over multiple ElseIf branches.
Summary
Select Case is a useful VBA decision-making structure for checking
one expression against multiple values, ranges, or comparison
conditions. Multiple values can share a Case block, the
To keyword defines inclusive ranges, and
Case Is supports comparison operators.
Use Case Else for a default outcome and validate
worksheet inputs before processing them. These techniques are
useful for grades, sales categories, incentives, month names,
and other Excel automation tasks.