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.


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.

Dataset for excel vba select case


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.

CommandButton to use excel vba select case

  • 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.

CommandButton to use excel vba select case

  • Excel will determine the grade.

Read More: How to Open Workbook from Path Using Excel VBA (4 Examples)


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.

MsgBox and InputBox to use excel vba select case

  • 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

MsgBox and InputBox to use excel vba select case

  • 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. 

MsgBox and InputBox to use excel vba select case

Read More: Learn Excel VBA Programming & Macros (Free Tutorial – Step by Step)


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.


Related Articles

Tags:

Taryn N

Taryn N

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo