Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

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.

Start VBA program Evaluate expression Case 1 matches Execute block 1 Case 2 matches Execute block 2 No case matches Case Else Only the first matching Case block is executed.
Remember: VBA evaluates the expression, executes the first matching Case block, and then continues with the statement after 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.
Important: 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
Output: You selected Two.

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
Output: It is a weekend.

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
Important: This example uses whole-number marks. If your application accepts decimal marks, define the ranges accordingly and validate the allowed limits.

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
Output: Good Sales.

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
Tip: Text comparisons can depend on the module's 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
Example output: If B2 contains 75,000, the rate is 7% and the incentive is 5,250.

11. Common Mistakes to Avoid

  • Forgetting to close the structure with End Select.
  • Assuming Case Else is 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 numeric 10.
  • 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.

  1. Write a Select Case program to display a message for choices 1, 2, 3, and an invalid choice.
  2. Accept a day name and identify whether it is a weekday or weekend.
  3. Accept marks and assign grades using ranges.
  4. Accept a month number and display the corresponding month name.
  5. Classify sales into Excellent, Good, Average, or Needs Improvement.
  6. Accept an employee department and display the department details.
  7. Calculate a discount based on a purchase amount using Case Is >=.
  8. Read a month number from cell A2 and write the month name into cell B2.
  9. Read employee sales from B2 and calculate the incentive in C2.
  10. Compare Select Case and If...ElseIf by writing both versions of a grade-calculation macro.

Important Points to Remember

  • Select Case evaluates an expression.
  • Case defines the values or conditions to match.
  • Use commas to match multiple alternative values in one Case.
  • Use To for inclusive ranges.
  • Use Case Is >= value for comparison conditions.
  • Case Else handles 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.