Select Case
Instead of multiple If Then statements in Excel VBA, you can use the Select Case structure. Let's explore this through two examples.
Case 1
The first Select Case structure on this page only uses >= operators to check if a score is greater than or equal to specific boundary levels.
Situation:
Place a command button on your worksheet and add the following code lines:
1. First, declare two variables. One variable of type Integer named score and one variable of type String named result.
2. We initialize the variable score with the value of cell A1.
3. Add the Select Case structure.
Case Is >= 80
result = "very good"
Case Is >= 70
result = "good"
Case Is >= 60
result = "sufficient"
Case Else
result = "insufficient"
End Select
Note: Excel VBA automatically adds the Is keyword when using comparison operators.
Explanation: Excel VBA uses the value of the variable score to test each subsequent Case statement to see if the code under the Case statement should be executed.
4. Write the value of the variable result to cell B1.
5. Test the program.
Result when you click the command button on the sheet:
Explanation: Excel VBA executes the code under the second Case statement for all values greater than or equal to 70 and less than 80.
Case 2
The second Select Case structure on this page simply uses the To keyword to check if a number falls within specific boundaries. It also demonstrates how to check if a number is exactly equal to certain values.
Situation:
Place a command button on your worksheet and add the following code lines:
1. First, declare two variables. One variable of type Integer named number and one variable of type String named result.
2. We initialize the variable number with the value of cell A1.
3. Add the Select Case structure.
Case 1 To 10
result = "Number between 1 and 10"
Case 11
result = "Number equal to 11"
Case 12 To 17
result = "Number between 12 and 17"
Case 18, 19, 20
result = "Number equal to 18, 19 or 20"
Case Else
result = "Number not between 1 and 20"
End Select
Explanation: Excel VBA checks if the value of the variable number is between 1 and 10, exactly 11, between 12 and 17, exactly 18, 19, or 20, or falls outside these conditions.
4. Write the value of the variable result to cell B1.
5. Test the program.
Result when you click the command button on the sheet:
Note: the Case Else part in a Select Case structure is always optional, and it is executed when none of the specified conditions in the previous Case statements is met. It serves as a catch-all for situations not covered by the explicitly defined conditions.
