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

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. This article will show you how to use the Select Case statement in Excel VBA. This statement is an alternative to the IF-ELSE statement. I will show 2 easy examples of the Select Case statement in this article.

Download Practice Workbook

Download this workbook and practice while going through the article.

Select Case Statement.xlsm

2 Suitable Examples to Use Select Case Statement in Excel VBA

This is the dataset for today’s article. It has some grades and marks. I will determine the grade of a student after putting in his or her mark. The Select Case statement will help here.

1. Apply CommandButton to Use Select Case

In the first example, I will insert a CommandButton to use the Select Case statement in Excel. The steps are here.

Steps:

First of all, go to the Developer
Then, select Insert.
Then, choose the command button from ActiveX Controls.

Then, draw a CommandButton.
After that, right-click your mouse to bring the context bar.
Then, select the view code.

A new VBA module will appear. Write down 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:

Here, I have created two variables Mark and Grade using the dim statement.
The Mark variable is Integer and the Grade is String.
Then, I put the marks in C11.
Finally, I set Mark as my Case and applied the conditions.

After that, go back to the dataset.
Then, put in a mark. (For example 94)
After that, select the command button.

Excel will determine the grade.

Similar Readings

2. Insert InputBox and MsgBox to Use Select Case

Now, I will show another useful example. This is a bit different because here I will use the InputBox and MsgBox features. Let’s do it step by step.

Steps:

First of all, go to the Developer
Then, select Visual Basic.

The Visual Basic window will appear.
Then, go to the Insert
After that, select Module.

Excel will create a new module. After that, write down the following code.

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

Then, press F5 to run the code.
Excel will show this input box.
Insert any number.
Then, press OK.

Excel will show the grade in a message box.

Things to Remember

The Select Case statement is an alternative to the IF-ELSE
You can press ALT+F11 to get the Visual Basic Window.
Activate Design Mode to edit the command button.

Conclusion

In this article, I have explained how to use the Select Case statement in Excel VBA. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit ExcelDemy for more useful articles like this.

Get FREE Advanced Excel Exercises with Solutions!