Alif Bin Hussain
VBA Select Case True is a very useful statement while working with multiple conditions. This statement is more concise and presents the conditions in a more structured and readable format. In this article, we will demonstrate six examples of how to use the VBA Select Case True statement in Excel.
Download Practice Workbook
Download this practice workbook to exercise while reading this article.
Introduction to VBA Select Case True Statement in Excel
The Select Case True statement is a logical function. It is a very useful alternative to the If Else statement when you need to evaluate a single variable against multiple conditions. It is more efficient than the If Else statement when you have a large number of conditions.
Case Statement Options
| Case Options | Description |
|---|---|
| Comma | Used to write multiple conditions |
| To | Used for a range of numbers |
| Is | Used with <,>,= operators |
| No operator | Same as “Is=” |
Select Case Syntax
Let’s have a look at the syntax of Select Case before going through the examples.
Select Case [Variable]
Case [Criteria 1]
[Statement 1 if Criteria 1 is satisfied]
Case [Criteria 2]
[Statement 2 if Criteria 2 is satisfied]
Case [Criteria n]
[Statement n if Criteria n is satisfied]
Case Else
[Statement else if none are satisfied]
End SelectAll you need to start with is Select keyword defining the variable and mention the scenarios of tasks to perform for a particular case with Case keyword.
How to Launch VBA Editor in Excel
To access the Microsoft Visual Basic window, go to the Developer tab and then click on Visual Basic. You can also open it by pressing Alt+F11 on your keyboard.
Go to the Insert tab and click on Module to launch code Module.
VBA Select Case True in Excel: 6 Examples
To demonstrate to you the examples we will use the dataset containing several students’ information.
1. Use Select Case True with Text String
In this example, we will determine the grades of students using Select Case True. We will insert the student’s name in the Input Box and get the grades in the output.
- First of all, open the Microsoft Visual Basic code Module.
- Then write the following code in the Module.
Sub Select_Case_True_Text()
Dim Name As String
Dim grade As String
Name = InputBox("Enter Student's name:")
Select Case True
Case Name = "John"
grade = Range("D5").Value
Case Name = "Alex"
grade = Range("D6").Value
Case Name = "Wendy"
grade = Range("D7").Value
Case Name = "Gary"
grade = Range("D8").Value
Case Name = "Claire"
grade = Range("D9").Value
Case Name = "Drew"
grade = Range("D10").Value
Case Name = "Robert"
grade = Range("D11").Value
Case Name = "Paige"
grade = Range("D12").Value
Case Else
MsgBox "The student is not on the list"
Exit Sub
End Select
MsgBox "The grade for " & Name & " is " & grade
End Sub
Code Breakdown
- First, name the sub-procedure and declare the necessary variables.
- Prompt the user to enter a student’s name in an Input Box.
Select Case True
Case Name = "John"
grade = Range("D5").Value
MsgBox "The grade for " & Name & " is " & grade- If the student’s name is John, it will show his grade that is stored in cell D5 in a MsgBox.
- Finally, press F5 or Run button to run the code.
- As a result, a MsgBox will appear with the grade.
Alternative Way: Using Colon
We can get the same results as before by writing the code using Colon. It reduces the number of lines in the code. The code is given below.
Sub Select_Case_Colon()
Dim Name As String
Dim grade As String
Name = InputBox("Enter Student's name:")
Select Case Name
Case "John": grade = Range("D5").Value
Case "Alex": grade = Range("D6").Value
Case "Wendy": grade = Range("D7").Value
Case "Gary": grade = Range("D8").Value
Case "Claire": grade = Range("D9").Value
Case "Drew": grade = Range("D10").Value
Case "Robert": grade = Range("D11").Value
Case "Paige": grade = Range("D12").Value
Case Else: MsgBox "The student is not on the list": Exit Sub
End Select
MsgBox "The grade for " & Name & " is " & grade
End Sub
Code Breakdown
- First, name the sub-procedure and declare the necessary variables.
- Prompt the user to enter a student’s name in an Input Box.
Select Case Name
Case "Alex": grade = Range("D6").Value
MsgBox "The grade for " & Name & " is " & grade- If you insert Alex in the Input Box, it will check the grade in cell D6 and display it in a MsgBox.
- Run the code to get the output.
1.1 Use Select Case to Make Text Case Insensitive
In the previous example, if we insert the name “john” instead of “John”, it will display “The student is not on the list” as VBA is case sensitive by default. We want to make the above code case insensitive so that even if users insert the names in any case, they still get their desired output. We can do it easily by adding an extra line, Option Compare Text, to the above code.
The whole code is given below.
Option Compare Text
Sub Select_Case_Case_Sensitive()
Dim Name As String
Dim grade As String
Name = InputBox("Enter Student's name:")
Select Case Name
Case "John": grade = Range("D5").Value
Case "Alex": grade = Range("D6").Value
Case "Wendy": grade = Range("D7").Value
Case "Gary": grade = Range("D8").Value
Case "Claire": grade = Range("D9").Value
Case "Drew": grade = Range("D10").Value
Case "Robert": grade = Range("D11").Value
Case "Paige": grade = Range("D12").Value
Case Else: MsgBox "The student is not on the list": Exit Sub
End Select
MsgBox "The grade for " & Name & " is " & grade
End Sub
Code Breakdown
Option Compare Text- The Option Compare Text tells VBA to use a case-insensitive text comparison. This means that string values that differ only in their case (uppercase vs lowercase) are considered the same when compared.
1.2 Partial Match: Select Case True Like
In the previous example, if the users insert a part of the full name, they won’t get their desired output. Therefore, we will write a code that displays the correct output even if only a part of the name is inserted.
Sub Select_Like_Case_Text()
Dim Name As String
Dim grade As String
Dim fullName As String
Dim foundName As Boolean
Name = InputBox("Enter Student's name:")
Select Case True
Case InStr(Range("B5").Value, Name) = 1
grade = Range("D5").Value
fullName = Range("B5").Value
foundName = True
Case InStr(Range("B6").Value, Name) = 1
grade = Range("D6").Value
fullName = Range("B6").Value
foundName = True
Case InStr(Range("B7").Value, Name) = 1
grade = Range("D7").Value
fullName = Range("B7").Value
foundName = True
Case InStr(Range("B8").Value, Name) = 1
grade = Range("D8").Value
fullName = Range("B8").Value
foundName = True
Case InStr(Range("B9").Value, Name) = 1
grade = Range("D9").Value
fullName = Range("B9").Value
foundName = True
Case InStr(Range("B10").Value, Name) = 1
grade = Range("D10").Value
fullName = Range("B10").Value
foundName = True
Case InStr(Range("B11").Value, Name) = 1
grade = Range("D11").Value
fullName = Range("B11").Value
foundName = True
Case InStr(Range("B12").Value, Name) = 1
grade = Range("D12").Value
fullName = Range("B12").Value
foundName = True
Case Else
MsgBox "The student is not on the list"
Exit Sub
End Select
If foundName = True Then
MsgBox "The grade for " & fullName & " is " & grade
End If
End Sub
Code Breakdown
- First, the required variables are declared.
- Then, the user is prompted to enter the student’s name using an input box.
Select Case True
Case InStr(Range("B5").Value, Name) = 1
grade = Range("D5").Value
fullName = Range("B5").Value
foundName = True- The InStr function is used to check whether the entered name is a substring of the name in each row of the list. If the name is found in the list, the corresponding grade and full name are stored in the “grade” and “fullName” variables, and the “foundName” variable is set to true.
If foundName = True Then
MsgBox "The grade for " & fullName & " is " & grade
End If- If the name is found in the list, a Msgbox appears and displays the grade.
2. Use Select Case True with Numbers
In this example, we will use Select Case True with numbers. We will determine grades based on the score that is stored in the worksheet. We will use the following code for this purpose.
Sub Select_Case_Numbers()
Dim Score As Integer
Dim grade As String
For i = 5 To 12
Score = Range("C" & i).Value
Select Case True
Case Score >= 80
grade = "A+"
Case Score >= 70
grade = "A"
Case Score >= 60
grade = "B"
Case Score >= 50
grade = "C"
Case Score >= 40
grade = "D"
Case Else
grade = "F"
End Select
Range("D" & i).Value = grade
Next i
End Sub
Code Breakdown
For i = 5 To 12
Score = Range("C" & i).Value
Select Case True
Case Score >= 80
grade = "A+"
Case Score >= 70
grade = "A"
Case Score >= 60
grade = "B"
Case Score >= 50
grade = "C"
Case Score >= 40
grade = "D"
Case Else
grade = "F"
End Select
Range("D" & i).Value = grade
Next i- The code loops through cell C5 to C12 where the scores are stored.
- It uses the Select case statement to check each score with all the cases.
- Finally, it assigns grades to each score and stores the grades in cells D5 to D12.
3. Apply Multiple Conditions Using Select Case True
In this example, we will apply multiple conditions using Select Case True to get the grades. We will use the following code for this purpose.
Sub Select_Case_Multiple_Condition()
Dim Score As Integer
Dim grade As String
For i = 5 To 12
Score = Range("C" & i).Value
Select Case True
Case Score >= 80
grade = "A+"
Case Score >= 70 And Score <= 79
grade = "A"
Case Score >= 60 And Score <= 69
grade = "B"
Case Score >= 50 And Score <= 59
grade = "C"
Case Score >= 40 And Score <= 49
grade = "D"
Case Else
grade = "F"
End Select
Range("D" & i).Value = grade
Next i
End Sub
Code Breakdown
Select Case True
Case Score >= 80
grade = "A+"
Case Score >= 70 And Score <= 79
grade = "A"
Case Score >= 60 And Score <= 69
grade = "B"
Case Score >= 50 And Score <= 59
grade = "C"
Case Score >= 40 And Score <= 49
grade = "D"
Case Else
grade = "F"
End Select- Within the For loop (iterates through C5 to C12) it uses the Select Case statement to write multiple conditions separated by AND.
- It checks all the scores with these criteria and assigns grades to each score.
4. Create Nested Conditions Using Select Case True
In this example, we will grade the students based on Student ID. If the Student ID starts with 17, we will use Pass/Fail. If the Student ID starts with 18, we will grade them A/B/C, etc based on the score. We will use nested conditions using Select Case to do so.
- First of all, write the code given below in the code Module.
Sub GradeStudents()
Dim i As Integer
For i = 5 To 12
Dim student_id As String
Dim Score As Integer
student_id = Cells(i, 3)
Score = Cells(i, 4)
Select Case True
Case Left(student_id, 2) = "17"
Select Case True
Case Score >= 50
Cells(i, 5) = "Pass"
Case Else
Cells(i, 5) = "Fail"
End Select
Case Left(student_id, 2) = "18"
Select Case Score
Case Is >= 80
Cells(i, 5) = "A+"
Case Is >= 70
Cells(i, 5) = "A"
Case Is >= 60
Cells(i, 5) = "B"
Case Is >= 50
Cells(i, 5) = "C"
Case Is >= 50
Cells(i, 5) = "D"
Case Else
Cells(i, 5) = "F"
End Select
End Select
Cells(i, 6) = Cells(i, 5)
Next i
End Sub
Code Breakdown
Select Case True
Case Left(student_id, 2) = "17"
Select Case True
Case Score >= 50
Cells(i, 5) = "Pass"
Case Else
Cells(i, 5) = "Fail"
End Select- The code checks if the Student ID starts with 17 or not.
- If it is true, then it assigns Pass for scores greater than or equal to 50 and assigns fails otherwise.
Case Left(student_id, 2) = "18"
Select Case Score
Case Is >= 80
Cells(i, 5) = "A+"
Case Is >= 70
Cells(i, 5) = "A"
Case Is >= 60
Cells(i, 5) = "B"
Case Is >= 50
Cells(i, 5) = "C"
Case Is >= 50
Cells(i, 5) = "D"
Case Else
Cells(i, 5) = "F"
End Select
End Select- This part assigns grades to each score based on the criteria if the Student ID starts with 18.
- Now run the code and you will get your results in cells E5 to E12.
5. Create VBA Function Using Select Case True
We will now create a VBA function using Select Case to assign grades to all the scores. Follow these steps to learn how to do it.
- First, open the VBA code editor.
- Next, insert the following code in the editor.
Function GradeScore(Score As Integer) As String
Dim grade As String
Select Case True
Case Score >= 80
grade = "A+"
Case Score >= 70
grade = "A"
Case Score >= 60
grade = "B"
Case Score >= 50
grade = "C"
Case Score >= 40
grade = "D"
Case Else
grade = "F"
End Select
GradeScore = grade
End Function
Code Breakdown
Function GradeScore(Score As Integer) As String- Declared a function GradeScore which allows an Integer type argument Score.
Select Case True
Case Score >= 80
grade = "A+"
Case Score >= 70
grade = "A"
Case Score >= 60
grade = "B"
Case Score >= 50
grade = "C"
Case Score >= 40
grade = "D"
Case Else
grade = "F"
End SelectGradeScore = grade
- This function takes a number as input and checks the number with the given criteria.
- Then it assigns a grade to the input.
- Then go to your Excel sheet and write the following formula.
<span style="font-size: 14pt;"><strong>=GradeScore(C5)</strong></span>- Press Enter and you will get a grade as output for the score stored in cell C5.
- Finally, AutoFill this formula to the rest of the cells.
6. Checking If a Number Is Odd or Even
In this example, we will check numbers to see if they are odd or even. We will use the Select Case True statement for this purpose. The process is discussed in the following section.
- First, launch the VBA code Module.
- Then write the following code in the Module.
Sub CheckOddEven()
Dim i As Integer
For i = 5 To 12
Select Case True
Case Cells(i, 3) Mod 2 = 0
Cells(i, 4) = "even"
Case Else
Cells(i, 4) = "odd"
End Select
Next i
End Sub
Code Breakdown
For i = 5 To 12
Select Case True
Case Cells(i, 3) Mod 2 = 0
Cells(i, 4) = "even"
Case Else
Cells(i, 4) = "odd"
End Select
Next i- The code loops through cells C5 to C12 and uses the Mod function to determine the remainder.
- If the remainder is zero, it stores Even in the corresponding cell in the D
- If it is one, it stores Odd.
- Now run the code and you will get your output in column D.
How to Use VBA Select Case for True Condition in Excel
Earlier we have demonstrated to you six examples of how to use Select Case True statement. Now we will walk you through two examples of using the Select Case statement where the provided condition becomes true to execute the result.
1. Apply Select Case with a Range of Numbers
We can also use Select Case when dealing with a range of numbers. In this example, we will write the conditions using different ranges of numbers to get the output when the condition gets true. The procedure is described in the following.
- First, write the following code in the VBA code Module.
Sub Select_Case_Numbers_Range()
Dim Score As Integer
Dim grade As String
For i = 5 To 12
Score = Range("C" & i).Value
Select Case Score
Case 80 To 100
grade = "A+"
Case 70 To 79
grade = "A"
Case 60 To 69
grade = "B"
Case 50 To 59
grade = "C"
Case 40 To 49
grade = "D"
Case Else
grade = "F"
End Select
Range("D" & i).Value = grade
Next i
End Sub
Code Breakdown
For i = 5 To 12
Score = Range("C" & i).Value
Select Case Score
Case 80 To 100
grade = "A+"
Case 70 To 79
grade = "A"
Case 60 To 69
grade = "B"
Case 50 To 59
grade = "C"
Case 40 To 49
grade = "D"
Case Else
grade = "F"
End Select
Range("D" & i).Value = grade
Next i- The code loops through cell C5 to C12 where the scores are stored.
- It uses the Select Case statement to write conditions using different ranges of numbers.
- Then, it assigns grades to each score based on the conditions and stores the grades in cells D5 to D12.
- Run the code and get your desired output.
2. Determine Day of the Week from a Date Using Select Case
In the last example, we will determine the day of the week from a date. We will use Select Case with the Weekday function for this purpose. The steps of the procedure are given below.
- First, insert the following code in the Module.
Sub FindDayOfWeek()
Dim i As Integer
For i = 5 To 12
Select Case Weekday(Cells(i, 3))
Case 1
Cells(i, 4) = "Sunday"
Case 2
Cells(i, 4) = "Monday"
Case 3
Cells(i, 4) = "Tuesday"
Case 4
Cells(i, 4) = "Wednesday"
Case 5
Cells(i, 4) = "Thursday"
Case 6
Cells(i, 4) = "Friday"
Case 7
Cells(i, 4) = "Saturday"
End Select
Next i
End Sub
Code Breakdown
For i = 5 To 12
Select Case Weekday(Cells(i, 3))
Case 1
Cells(i, 4) = "Sunday"
Case 2
Cells(i, 4) = "Monday"
Case 3
Cells(i, 4) = "Tuesday"
Case 4
Cells(i, 4) = "Wednesday"
Case 5
Cells(i, 4) = "Thursday"
Case 6
Cells(i, 4) = "Friday"
Case 7
Cells(i, 4) = "Saturday"
End Select
Next i- This code checks each date in the range C5:C12 and uses the Weekday function to determine the day of the week.
How to Apply Select Case Is Statement with Condition in Excel
The Select Case can also be used with Is condition. We will write a code to enter the test score and show a MsgBox that says “You failed” or “You passed”. The code is given below.
Sub Select_Case_Is()
Dim Score As Integer
Score = InputBox("Please enter your score")
Select Case Score
Case Is < 40
MsgBox "You failed"
Case Is >= 40
MsgBox "You passed"
End Select
End Sub
Code Breakdown
Select Case Score
Case Is < 40
MsgBox "You failed"
Case Is >= 40
MsgBox "You passed"
End Select- The code uses Case Is to create criteria.
- When the number is less than 40, it shows a MsgBox that says “You failed”.
- When the number is greater than or equal to 40, it pops up a MsgBox that says “You passed”.
Select Case Statement vs If Else Statement in Excel VBA
The Select Case statement uses a more concise syntax than the If Else statement. Select Case statement is more useful for testing multiple conditions on the same variable. On the other hand, If Else can check only one condition at a time.
If Else statement is more appropriate when you need to test one condition and the condition is more complex. However, In case of multiple conditions, Select Case is faster to run and easier to read than the If Else statement.
Things to Remember
- Select Case statement is better suited when there are multiple conditions for the same variable.
- Use Is, To, and Comma options for writing a single condition, condition with a range of numbers, and for multiple conditions respectively.
Conclusion
Thank you for reading this article. Hope you find it helpful. In this article, we have demonstrated six practical examples of how to use the Select Case True statement to determine grades. We have used the Is option to write a single condition, the To option to write conditions for a range of numbers, and the Comma to write multiple conditions in these examples. If you have any queries regarding this article, feel free to ask us in the comment section below.
