Updated April 8, 2023
VBA Select Case
VBA Select Case is one of the similar condition we use to test multiple conditions instead of the traditional IF condition in VBA. Select Case works exactly the same way as to how IF condition works.
Like how we test multiple conditions and draw results with IF condition similarly using SELECT CASE, we test the condition and draw results. In this article, I will take you through the ideology of the SELECT CASE statement.
Formula of VBA Select Case:
How to Use Excel VBA Select Case?
Let’s understand how to use a VBA Select Case Excel function with few examples.
VBA Select Case Function – Example #1
Assume you have a value in cell A1 and you want to test whether that value is greater than 100 or not.
If the value is greater than 100, we need the result as “More than 100” in cell B1, or else we need the result as “Less than 100”. The below code will do the job for us.
Code:
Sub SelectCase_Ex() Select Case Range("A1").Value Case Is > 100 Range("B1").Value = "More than 100" Case Else Range("B1").Value = "Less than 100" End Select End Sub
Run this code using the F5 key or manually as shown to get the results in cell B1.
VBA Select Case Function – Example #2
Now we will look into nested Select Case statements. Assume you have a loan recovery table from Jan to Dec.
In the status section of the data, we need to get the result as follows.
- If the Recovery Value is more than 45000, then the result should be “Excellent.”
- If the Recovery Value is more than 40000, then the result should be “Very Good.”
- If the Recovery Value is more than 30000, then the result should be “Good.”
- If the Recovery Value is more than 20000, then the result should be “Not Bad.”
- If all the results are FALSE, then the result should be “Bad.”
Since we need to test multiple cases, we need to use nested Select Case statements much like nested IF statements.
Code:
Sub IF_Results() Dim i As Integer i = 2 For i = 2 To 13 Select Case Cells(i, 2).Value Case Is > 45000 Cells(i, 3).Value = "Excellent" Case Is > 40000 Cells(i, 3).Value = "Very Good" Case Is > 30000 Cells(i, 3).Value = "Good" Case Is > 20000 Cells(i, 3).Value = "Not Bad" Case Else Cells(i, 3).Value = "Bad" End Select Next i End Sub
Copy this code and paste it into your module.
Now run this code using the F5 key or manually to get the desired results in the Status column.
VBA Select Case Function – Example #3
Now we will see Select Case with our own Input value. We will see how to supply value to an input box, and based on the input value given; we will determine the result.
Code:
Sub SelectCase_InputBox() Dim MyValue As Integer MyValue = Application.InputBox("Enter only numerical value", "Enter Number") Select Case MyValue Case Is > 1000 MsgBox "Entered Value is more than 1000" Case Is > 500 MsgBox "Entered Value is more than 500" Case Else MsgBox "Entered Value is less than 500" End Select End Sub
Copy & paste this code to your module.
Step 1: After copying the code to your module, run the code; you will see this input box.
Step 2: Here, we need to enter only numerical values.
Step 3: Now click on OK we will see a message box showing the result of the Select Case statement result.
VBA Select Case Function – Example #4
In this example, we will see how to pass a range of numbers as a test to select a case, and based on that; we will draw the results.
Copy & paste the below code to your module to up & running.
Code:
Sub SelectCase() Dim Mynumber As Integer Mynumber = Application.InputBox("Enter Number", "Please Enter numbers from 100 to 200") Select Case Mynumber Case 100 To 140 MsgBox "The number you have entered is less than 140" Case 141 To 180 MsgBox "The number you have entered is less than 180" Case Else MsgBox "The number you have entered is > 180 & <200" End Select End Sub
Step 1: Run this code; you will see the Input Box.
Step 2: As the prompt says, we need to enter the numbers from 100 to 200. So enter the numbers between 100 to 200.
Step 3: Click on OK. We will see the result based on the select case test result.
Things to Remember
- Unlike in IF condition test will not go through all the conditions in Select Case. As soon as the condition is met, it will exit the other conditions.
- Like all the other conditions here also all the text values should be in double-quotes.
- This works exactly similar to the IF statement in VBA. This is a kind of alternative to the IF statement.
Recommended Articles
This has been a guide to VBA Select Case Function. Here we discussed VBA Select Case and how to use Excel VBA Select Case Function along with some practical examples and downloadable excel template. You can also go through our other suggested articles to learn more –
