VBA Case Statement

Last Updated :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

Excel VBA Case Statement

VBA Case Statement is one of the logical functions. The Case Statement tests multiple logical tests and arrives at the result in two ways: if the result or logical test is TRUE, one set of results. If the result or logical test is FALSE, then the second set of results.

Excel VBA Case Statement

Typically, one conducts logical tests using IF formulas, whether a worksheet formula or VBA coding. On both platforms, this function helps us to conduct many kinds of complicated calculations. However, few of us realize that we have an alternative to the IF statement in VBA i.e., “Case Statement.” This article provides you with full details about this logical statement.

Syntax

Below is the syntax of the “Select Case” statement.

Select Case “Value to be Test”

       Case Is “Logical Test”

            Result if  Case 1 is TRUE

       Case Is “Logical Test”

            Result if Case 2 is TRUE

       Case Is “Logical Test”
 
            Result if Case 3 is TRUE

      Case Else

           If none of the results are TRUE

End Select

It is almost similar to IF statement syntax. But instead of using ELSEIF, we use Case 1, Case 2, Case 3, and so on.

Examples of VBA Case Statement

Example #1

In cell A1, we have entered the number 240.

Now, we will test whether this number is greater than 200 or not by using the SELECT CASE statement.

Step 1: Open the Select Case statement now.

Code:

Sub Select_Case_Example1()

    Select Case

End Sub

Step 2: Once we open the “Select Case,” we need to supply what is the value we are testing. In this case, we are testing cell A1 values.

Code:

Sub Select_Case_Example1()

     Select Case Range("A1").Value

End Sub

Step 3: Once we give the value to the test, we need to apply logical tests in excel using the “Case Is” word..

Code:

Sub Select_Case_Example1()

     Select Case Range("A1").Value
     Case Is > 200

End Sub

Step 4: In the next line, we need to supply the “result” value if the applied logical test is TRUE. In the message box, we need the result as “Number is >200”.

Code:

Sub Select_Case_Example1()

     Select Case Range("A1").Value
     Case Is > 200
       MsgBox "Number is >200"

End Sub

Step 5: We need only two results in this example, so we will not use more “Case Is” statements. Next, we will use the “Case Else” word to close the VBA “Select Case” statement.

Code:

Sub Select_Case_Example1()

     Select Case Range("A1").Value
     Case Is > 200
        MsgBox "Number is >200"
     Case Else
        MsgBox "Number is <200"

End Sub

Step 6: Once we supply all the cases, we need to close the select case statement using the “End Select” word.

Code:

Sub Select_Case_Example1()

     Select Case Range("A1").Value
     Case Is > 200
        MsgBox "Number is >200"
     Case Else
        MsgBox "Number is <200"
     End Select

End Sub

Step 7: Now, run the code and see what the result we get in the VBA message box is.

The result is “Number is >200” because, in cell A1, the value is 240, which is >200.

Example #2

Now, we will see some practical real-time examples of testing scores. First, look at the VBA code below.

Code:

Sub Select_Case_Example2()

     Dim ScoreCard As Integer

     ScoreCard = Application.InputBox("Score should be b/w 0 to 100", "What is the score you want to test")

     Select Case ScoreCard
         Case Is >= 85
             MsgBox "Distinction"
         Case Is >= 60
             MsgBox "First Class"
         Case Is >= 50
             MsgBox "Second Class"
         Case Is >= 35
             MsgBox "Pass"
         Case Else
             MsgBox "Fail"
     End Select
End Sub

Let us explain the code line by line to understand it better.

First, we have declared the variable as "Integer." Then, for this variable, we have assigned the InputBox in VBA, where a user must enter the score between 0 and 100.

When we run the code, we will see the input box like the one below. In this input box, you need to enter the score.

Whatever we enter in the input box will be stored in the variable "ScoreCard."

In the next line, we have applied a select case statement to test this score.

First, it will test whether the ScoreCard >=85 or not. If this is TRUE, we will get the value in the message box as "Distinction."

Select Case ScoreCard
    Case Is >= 85
        MsgBox "Distinction"

Similarly, in the following lines, we have applied the second test as ScoreCard >=60. Again, if this is TRUE, it will show the result as "First."

Case Is >= 60
    MsgBox "First Class"

Like this, we have also applied other tests. In the end, we have used the "Case Else" statement. If all the applied logical tests are FALSE, we will get the result as "Fail."

Case Else
    MsgBox "Fail"

Now, we have supplied 68 as the score. Therefore, we should get the "First Class" result in the message box.

Example #3 - Using the “To” keyword

In the above example, we have used student scores to arrive at the result. However, one can conduct the same test using the "To" word to determine the lower limit and upper limit of the logical test.

Code:

Sub Select_Case_Example3()

  Dim ScoreCard As Integer

  ScoreCard = Application.InputBox("Score should be b/w 0 to 100", "What is the score you want to test")

     Select Case ScoreCard
         Case 85 To 100
             MsgBox "Distinction"
         Case 60 To 84
             MsgBox "First Class"
         Case 50 To 59
             MsgBox "Second Class"
         Case 35 To 49
             MsgBox "Pass"
         Case Else
             MsgBox "Fail"
     End Select
End Sub

We have used the same code as above. But, the only yellow-colored area we have changed here. We will get the result based on the number we type in the input box accordingly.

Things to Remember

The Select Case is an alternative to the IF statement.
The Select Case is available only with VBA.
In the first line of "Select Case," we only need to supply the value to be tested. Then in the "Case" line, we need to apply the logical test. It is unlike our IF condition.