VBA Select Case
VBA Select Case is one of the similar condition we use to test multiple conditions instead of traditional IF condition in VBA. Select Case works exactly the same way 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 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”. Below code will do the job for us.
Select Case Range(“A1”).Value
Case Is > 100
Range(“B1”).Value = “More than 100”
Range(“B1”).Value = “Less than 100”
Run this code using 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.
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”
Cells(i, 3).Value = “Bad”
Copy this code and paste in your module.
Now run this code using 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.
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”
MsgBox “Entered Value is less than 500”
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 case and based on that we will draw the results.
Copy & paste the below code to your module to up & running.
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”
MsgBox “The number you have entered is > 180 & <200”
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 IF statement.
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 –