How to Use Select Case Statement in Excel VBA (2 Examples)

Download Practice Workbook

Select Case Statement.xlsm

Using the sample dataset below, we will illustrate two examples to use the Select Case statement in Excel VBA. This statement is an alternative to the IF-ELSE statement.

Example 1 – Apply CommandButton to Use Select Case

Steps:

Go to the Developer tab.
Select Insert.
Choose the command button from ActiveX Controls.

Draw a CommandButton.
Right-click your mouse.
In the Context menu, select view code.

A new VBA module will open. Enter the following code in that module.

Private Sub CommandButton1_Click()
Dim Mark As Integer, Grade As String
Mark = Range("C11").Value
Select Case Mark
    Case Is >= 90
        Grade = "A"
    Case Is >= 80
        Grade = "B"
    Case Is >= 70
        Grade = "C"
    Case Is >= 60
        Grade = "D"
    Case Else
        Grade = "F"
End Select
Range("C12").Value = Grade
End Sub

Code Explanation:

We have created two variables Mark and Grade using the dim statement.
The Mark variable is Integer and the Grade is String.
We entered the marks in C11.
We have set Mark as our Case and applied the conditions.

Go back to the dataset.
Enter a mark. (For example, 94)
Select the command button.

Excel will determine the grade.

Similar Readings

Example 2 – Insert InputBox and MsgBox to Use Select Case

Steps:

Go to the Developer tab.
Select Visual Basic.

The Visual Basic window will open.
Go to Insert.
Select Module.

Excel will create a new module. Enter the following code into the module.

Sub Select_Case_Statement()
        Dim Mark  As Integer
        Dim Grade As String
    Mark = InputBox("Enter Mark")
    Select Case Mark
        Case Is >= 90
            Grade = "A"
        Case Is >= 80
            Grade = "B"
        Case Is >= 70
            Grade = "C"
        Case Is >= 60
             Grade = "D"
        Case Else
            Grade = "F"
   End Select
    MsgBox "The Student got : " & Grade
End Sub