Excel 2010 Macro Select Case and End Select Statements
When you have a lot of different conditions, it becomes unmanageable to use many Else If statements. This is where VBA comes to use. VBA offers another construct known as the Select Case construct. In your running example, you should always check the value of the Class in column A. This value is called the test expression.
The basic syntax of this construct starts with the words Select Case followed by the test expression:
Select Case Cells(i, 1).Value
There is no need to write out a complete sentence, like “In cases where the record is fruit, color the record with red.” VBA uses a shorthand version of this. Write the word Case followed by the literal “Fruit”. Any statements that follow Case “Fruit” will be executed whenever the test expression is a fruit. After these statements, you have the next Case statement: Case “Vegetables”. Continue this way, writing a Case statement followed by the program lines that will be executed if that case is true. After listing all possible conditions you can think of, you may optionally include a Case Else section at the end.
The Case Else section includes what the program should do if the test expression matches none of your cases. Finally, close the entire construct with the End Select statement.
The following does the same operation as the previous macro but uses a Select Case statement:
Sub SelectCase()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To FinalRow
Select Case Cells(i, 1).Value
Case “Fruit”
Cells(i, 1).Resize(1, 3).Font.ColorIndex = 3
Case “Vegetable”
Cells(i, 1).Resize(1, 3).Font.ColorIndex = 50
Case “Herbs”
Cells(i, 1).Resize(1, 3).Font.ColorIndex = 5
Case Else
End Select
Next i
MsgBox “Fruit is red / Veggies are green / Herbs are blue”
End Sub
It is possible to have complex expressions in Case statements. Perform the same actions for all berry records:
Case “Strawberry”, “Blueberry”, “Raspberry”
AdCode = 1
If it makes sense, code a range of values in the Case statement:
Case 1 to 20
Discount = 0.05
Case 21 to 100
Discount = 0.1
You can include the keyword Is and a comparison operator, such as > or <:
Case Is < 10
Discount = 0
Case Is > 100
Discount = 0.2
Case Else
Discount = 0.10