Excel VBA Case Statement

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Case Statement (wallstreetmojo.com)

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 VBAIF Statement In VBAThe If Else or If Then Else statement of VBA executes a set of instructions depending on whether the specified condition is met or not. If the condition (logical test) is true, one set of actions is performed. However, if the condition is false, an alternative set of actions is performed.read more 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.

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 excelApply Logical Tests In ExcelA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical test.read more using the “Case Is” word..

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

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.

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.

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 boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more 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 codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more below.

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 variableDeclared The VariableVariable declaration is necessary in VBA to define a variable for a specific data type so that it can hold values; any variable that is not defined in VBA cannot hold values.read more as “Integer.” Then, for this variable, we have assigned the InputBox in VBAInputBox In VBAVBA InputBox is inbuilt function used to get a value from the user, this function has two major arguments in which one is the heading for the input box and another is the question for the input box, input box function can store only the data types input which it variable can hold.read more, 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.

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.

This article is a guide to what is a VBA Case Statement. Here we discuss using Select Case Statements in VBA Excel, examples, and a downloadable Excel template. Below you can find some useful Excel VBA articles: –

  • VBA Switch CaseVBA Select CaseCDate VBA FunctionIFERROR in VBA