Home Tutorials Videos Q&A MCQ Shortcuts Exercises Appointment Login
Lesson 34 of 60 – Select Case in VBA
57%

Select Case in VBA

The Select Case statement in VBA is used to test a value against multiple possible cases. It is useful when a program needs to choose one action from several possible options.

For example, a student result program can use Select Case to display different grades based on marks, or a course-management program can use it to perform different actions based on a selected course.

Note: Select Case is often easier to read than a long series of ElseIf statements when you are checking one value against several possible cases.

1. What is Select Case?

Select Case is a VBA decision-making statement used to compare one expression with multiple possible values or conditions.

Basic structure:

Select Case expression

Case value1
    statements

Case value2
    statements

Case Else
    statements

End Select

2. Why Use Select Case?

Select Case is useful when one value can have several possible outcomes.

For example:

  • Student grade
  • Day number
  • Month number
  • Course selection
  • Menu selection
  • Employee category

3. Basic Select Case Syntax

The basic syntax is:

Select Case expression

Case value1
    statement1

Case value2
    statement2

Case Else
    statement3

End Select

VBA evaluates the expression and then executes the matching Case.

4. Simple Select Case Example

Suppose a variable contains a number representing a day. 

Dim dayNumber As Integer

dayNumber = 1

Select Case dayNumber

Case 1
    MsgBox "Monday"

Case 2
    MsgBox "Tuesday"

Case 3
    MsgBox "Wednesday"

End Select

Since the value is 1, VBA executes Case 1.

5. Understanding the Select Expression

The expression after Select Case is the value that VBA evaluates. 

Select Case dayNumber

Here, dayNumber is the expression. VBA compares its value with each Case.

6. Using Numeric Cases

Select Case can compare numeric values. 

Dim number As Integer

number = 2

Select Case number

Case 1
    MsgBox "One"

Case 2
    MsgBox "Two"

Case 3
    MsgBox "Three"

Case Else
    MsgBox "Other Number"

End Select

7. Using Text Cases

Select Case can also be used with text values. 

Dim course As String

course = "ADCA"

Select Case course

Case "ADCA"
    MsgBox "ADCA Course"

Case "Tally"
    MsgBox "Tally Course"

Case "Python"
    MsgBox "Python Course"

Case Else
    MsgBox "Other Course"

End Select

8. Using Case Else

Case Else executes when none of the other Case values match the expression. 

Dim number As Integer

number = 10

Select Case number

Case 1
    MsgBox "One"

Case 2
    MsgBox "Two"

Case Else
    MsgBox "Number not found"

End Select

9. Select Case with Excel Cells

You can use the value of an Excel cell as the Select Case expression. 

Select Case Range("A2").Value

Case 1
    Range("B2").Value = "Monday"

Case 2
    Range("B2").Value = "Tuesday"

Case 3
    Range("B2").Value = "Wednesday"

Case Else
    Range("B2").Value = "Invalid Day"

End Select

10. Select Case with Variables

Variables are commonly used with Select Case. 

Dim marks As Integer

marks = 85

Select Case marks

Case 100
    MsgBox "Perfect Score"

Case 85
    MsgBox "Excellent"

Case 50
    MsgBox "Average"

Case Else
    MsgBox "Other Marks"

End Select

11. Select Case with InputBox

InputBox can collect a value from the user and Select Case can process it. 

Dim choice As Integer

choice = CInt(InputBox("Enter 1, 2 or 3:"))

Select Case choice

Case 1
    MsgBox "You selected Option 1"

Case 2
    MsgBox "You selected Option 2"

Case 3
    MsgBox "You selected Option 3"

Case Else
    MsgBox "Invalid Option"

End Select

12. Select Case for Student Grades

Select Case can be used to classify grades. 

Dim grade As String

grade = "A"

Select Case grade

Case "A"
    MsgBox "Excellent"

Case "B"
    MsgBox "Very Good"

Case "C"
    MsgBox "Good"

Case "D"
    MsgBox "Needs Improvement"

Case Else
    MsgBox "Invalid Grade"

End Select

13. Select Case with Multiple Values

A single Case can contain multiple values separated by commas. 

Dim dayNumber As Integer

dayNumber = 6

Select Case dayNumber

Case 1, 2, 3, 4, 5
    MsgBox "Weekday"

Case 6, 7
    MsgBox "Weekend"

Case Else
    MsgBox "Invalid Day"

End Select

Here, Cases 1 through 5 represent weekdays and Cases 6 and 7 represent weekends.

14. Select Case with Ranges

The To keyword can be used to specify a range of values. 

Dim marks As Integer

marks = 75

Select Case marks

Case 80 To 100
    MsgBox "Grade A"

Case 60 To 79
    MsgBox "Grade B"

Case 40 To 59
    MsgBox "Grade C"

Case 0 To 39
    MsgBox "Fail"

Case Else
    MsgBox "Invalid Marks"

End Select

15. Select Case for Student Result

A practical student result system can use ranges to determine grades. 

Dim marks As Integer

marks = 82

Select Case marks

Case 80 To 100
    MsgBox "Grade A"

Case 60 To 79
    MsgBox "Grade B"

Case 40 To 59
    MsgBox "Grade C"

Case Else
    MsgBox "Fail"

End Select

16. Select Case with Is

The Is keyword can be used with comparison operators in a Case statement. 

Dim marks As Integer

marks = 85

Select Case marks

Case Is >= 80
    MsgBox "Grade A"

Case Is >= 60
    MsgBox "Grade B"

Case Is >= 40
    MsgBox "Grade C"

Case Else
    MsgBox "Fail"

End Select

This allows Select Case to work with comparison conditions.

17. Select Case for Age Groups

Select Case can classify people into age groups. 

Dim age As Integer

age = 25

Select Case age

Case 0 To 12
    MsgBox "Child"

Case 13 To 19
    MsgBox "Teenager"

Case 20 To 59
    MsgBox "Adult"

Case Is >= 60
    MsgBox "Senior Citizen"

Case Else
    MsgBox "Invalid Age"

End Select

18. Select Case for Menu Selection

Select Case is useful for creating simple menu-driven programs. 

Dim choice As Integer

choice = CInt(InputBox("Enter 1, 2 or 3:"))

Select Case choice

Case 1
    MsgBox "Add Student"

Case 2
    MsgBox "View Student"

Case 3
    MsgBox "Delete Student"

Case Else
    MsgBox "Invalid Choice"

End Select

19. Select Case for Months

A month number can be converted into a month name using Select Case. 

Dim monthNumber As Integer

monthNumber = 4

Select Case monthNumber

Case 1
    MsgBox "January"

Case 2
    MsgBox "February"

Case 3
    MsgBox "March"

Case 4
    MsgBox "April"

Case 5
    MsgBox "May"

Case Else
    MsgBox "Other Month"

End Select

20. Select Case for Attendance

Select Case can classify attendance percentages. 

Dim attendance As Double

attendance = 82

Select Case attendance

Case 90 To 100
    MsgBox "Excellent Attendance"

Case 75 To 89
    MsgBox "Good Attendance"

Case 60 To 74
    MsgBox "Average Attendance"

Case 0 To 59
    MsgBox "Low Attendance"

Case Else
    MsgBox "Invalid Attendance"

End Select

21. Select Case for Fee Status

You can use Select Case to classify fee payment amounts. 

Dim paid As Double

paid = 5000

Select Case paid

Case 0
    MsgBox "Fee Pending"

Case 1 To 4999
    MsgBox "Partial Payment"

Case 5000 To 9999
    MsgBox "More Payment Required"

Case Is >= 10000
    MsgBox "Fee Fully Paid"

End Select

22. Select Case with Multiple Statements

Each Case can contain multiple VBA statements. 

Dim marks As Integer

marks = 85

Select Case marks

Case Is >= 80

    Range("C2").Value = "A"
    Range("D2").Value = "Excellent"
    MsgBox "Grade A"

Case Is >= 60

    Range("C2").Value = "B"
    Range("D2").Value = "Very Good"
    MsgBox "Grade B"

Case Else

    Range("C2").Value = "C"
    Range("D2").Value = "Needs Improvement"
    MsgBox "Other Grade"

End Select

23. Select Case with Excel Worksheet Data

The value from a worksheet can be used directly. 

Select Case Range("B2").Value

Case Is >= 80
    Range("C2").Value = "A"

Case Is >= 60
    Range("C2").Value = "B"

Case Is >= 40
    Range("C2").Value = "C"

Case Else
    Range("C2").Value = "Fail"

End Select

This is useful for automated Excel result systems.

24. Select Case vs ElseIf

Both Select Case and ElseIf can be used for multiple decisions, but their structures are different.

ElseIf:

If marks >= 80 Then
    MsgBox "A"
ElseIf marks >= 60 Then
    MsgBox "B"
Else
    MsgBox "C"
End If

Select Case:

Select Case marks

Case Is >= 80
    MsgBox "A"

Case Is >= 60
    MsgBox "B"

Case Else
    MsgBox "C"

End Select

Select Case can make some multiple-choice logic easier to read.

25. Practical Student Grade Example

The following program takes marks from the user and automatically assigns a grade. 

Dim marks As Double
Dim grade As String

marks = CDbl(InputBox("Enter student marks:"))

Select Case marks

Case 80 To 100
    grade = "A"

Case 60 To 79
    grade = "B"

Case 40 To 59
    grade = "C"

Case 0 To 39
    grade = "Fail"

Case Else
    grade = "Invalid Marks"

End Select

Range("B2").Value = marks
Range("C2").Value = grade

MsgBox "Grade: " & grade

26. Common Mistakes with Select Case

Beginners commonly make these mistakes:

  • Forgetting End Select.
  • Forgetting the Case keyword.
  • Using incorrect Case values.
  • Using overlapping ranges without understanding the order.
  • Forgetting Case Else when a default result is required.
  • Using To or Is incorrectly.

Correct structure:

Select Case value

Case 1
    MsgBox "One"

Case 2
    MsgBox "Two"

Case Else
    MsgBox "Other"

End Select

27. Practical Menu Example

Select Case can be used to create a simple student-management menu. 

Dim choice As Integer

choice = CInt(InputBox( _
"1 - Add Student" & vbCrLf & _
"2 - View Student" & vbCrLf & _
"3 - Exit" & vbCrLf & _
"Enter your choice:"))

Select Case choice

Case 1
    MsgBox "Add Student Selected"

Case 2
    MsgBox "View Student Selected"

Case 3
    MsgBox "Exit Selected"

Case Else
    MsgBox "Invalid Choice"

End Select

28. Best Practices for Select Case

  • Use meaningful expressions.
  • Keep Case blocks easy to read.
  • Use Case Else for unexpected values when appropriate.
  • Use ranges when values fall into categories.
  • Use multiple values in one Case when they share the same action.
  • Test every important Case.
  • Keep the Case order logical.

29. Complete Select Case Workflow

A typical Select Case workflow is:

  1. Declare a variable.
  2. Get or assign a value.
  3. Write Select Case.
  4. Specify the expression.
  5. Add Case values or conditions.
  6. Write the required statements.
  7. Add Case Else if needed.
  8. Close with End Select.
  9. Test different values.
Dim choice As Integer

choice = 2

Select Case choice

Case 1
    MsgBox "Option 1"

Case 2
    MsgBox "Option 2"

Case 3
    MsgBox "Option 3"

Case Else
    MsgBox "Invalid Option"

End Select

30. Complete Understanding of Select Case

The Select Case statement is an important VBA decision-making tool. It allows one expression to be compared with multiple values, ranges, or conditions.

It is especially useful for student result systems, menus, course selection, attendance classification, fee status, and other Excel automation projects. 

Dim marks As Double
Dim grade As String

marks = CDbl(InputBox("Enter student marks:"))

Select Case marks

Case 80 To 100
    grade = "A"

Case 60 To 79
    grade = "B"

Case 40 To 59
    grade = "C"

Case 0 To 39
    grade = "Fail"

Case Else
    grade = "Invalid Marks"

End Select

Range("B2").Value = marks
Range("C2").Value = grade

MsgBox "Grade: " & grade

The next lesson will introduce the For...Next loop, which is used to repeat a block of VBA code a specific number of times.

📌 Key Points

  • Select Case is used for multiple-choice decision making.
  • The expression is written after Select Case.
  • Each possible result is written using Case.
  • Case Else handles values that do not match another Case.
  • Multiple values can be placed in one Case.
  • The To keyword can define a range.
  • The Is keyword can be used with comparison operators.
  • Select Case can work with variables and Excel cells.
  • Select Case is useful for grades, menus, categories, and automation.

🧠 Quick Quiz

Question: Which keyword is used to define the possible values in a Select Case statement?




← Previous Next →