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:

Select Case in Excel VBA

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.

Dim score As Integer, result As String

2. We initialize the variable score with the value of cell A1.

score = Range("A1").Value

3. Add the Select Case structure.

Select Case score
    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.

Range("B1").Value = result

5. Test the program.

Result when you click the command button on the sheet:

Select Case Result

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:

Select Case with To Keyword

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.

Dim number As Integer, result As String

2. We initialize the variable number with the value of cell A1.

number = Range("A1").Value

3. Add the Select Case structure.

Select Case number
    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.

Range("B1").Value = result

5. Test the program.

Result when you click the command button on the sheet:

Select Case with Exact Matching

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.