Excel VBA Select Case Statement with Examples

The Select Case statement can offer a neater and more concise alternative to nested If Then Else statements in Excel VBA.

So when you are performing complex logical tests, the Select Case statement can break it down into more logical chunks of code. This then makes it easier to write, read and understand what the code does, which everyone will appreciate.

In this tutorial, we will look at a few examples to show the different ways that the Excel VBA Select Case statement can be written.

Watch the Video – Excel VBA Select Case

The Select Case Syntax

Let us begin by looking at the structure of the Select Case statement.

Select Case TestExpression
  Case Condition
    actions to perform
  Case Condition
    actions to perform
  Case Condition
    actions to perform
  Case Condition
    actions to perform
  Case Else
    Default actions to perform
End Select

In the Select Case syntax above, the TestExpression refers to the variable, cell or form control to be tested.

There is then each case block starting with the Case Condition. This is the logical test against the TestExpression. This is then followed by the actions to perform if the condition is true.

The Case Else is the default case action to perform if none of the previous case evaluated to be true. It is optional to use this Case Else.

Excel VBA Select Case Statement Examples

Now we shall look at some practical examples of the Select Case statement in action.

Select Case to Test Numbers

In the example below we have some exam scores. We wish to assign a grade for each score using a macro.

The following code uses a loop to go through each exam score in the list, and then more importantly uses the Select Case statement to assign the correct grade.

Dim RowNum As Long
RowNum = 2
Do Until Cells(RowNum, 1).Value = ""
 Select Case Cells(RowNum, 2).Value
   Case Is >= 0.9
     Cells(RowNum, 3).Value = "A"
   Case Is >= 0.8
     Cells(RowNum, 3).Value = "B"
   Case Is >= 0.7
     Cells(RowNum, 3).Value = "C"
   Case Is >= 0.6
     Cells(RowNum, 3).Value = "D"
   Case Else
     Cells(RowNum, 3).Value = "E"
 End Select
 RowNum = RowNum + 1
Loop

The keyword Is was used in the conditional tests. If omitted, this word is inserted automatically.

The order of the tests is important. It goes from the highest value to test, down to the lowest to ensure the correct grade is assigned.

The Case Else is used for any grade below 0.6.

Testing Value Ranges with the Select Case Statement

In this example we look at a different approach to assigning the correct grades to the exam scores.

This approach tests if the score falls within a range of values. By using this technique the order of the test is irrelevant, so I went from testing the smallest value to the largest for simplicity of reading.

Dim RowNum As Long
RowNum = 2
Do Until Cells(RowNum, 1).Value = ""
 Select Case Cells(RowNum, 2).Value
   Case 0 To 0.59: Cells(RowNum, 3).Value = "E"
   Case 0.6 To 0.69: Cells(RowNum, 3).Value = "D"
   Case 0.7 To 0.79: Cells(RowNum, 3).Value = "C"
   Case 0.8 To 0.89: Cells(RowNum, 3).Value = "B"
   Case 0.9 To 1:Cells(RowNum, 3).Value = "A"
 End Select
 RowNum = RowNum + 1
Loop

The keyword To is used for testing value ranges.

A couple of interesting things in this example are that the Case Else is omitted, and the conditional test and action to perform are written on the same line this time, separated by a colon.

Excel VBA Select Case Example to Test Text Strings

In this last example we test text strings using the Select Case statement.

We have a list of locations and we want to assign them to specific regions.

In the code below we are looping through the different locations in the same way as previously.

The interesting technique here is how we can test multiple strings by just separating them with a comma.

Dim RowNum As Long
RowNum = 2
Do Until Cells(RowNum, 1).Value = ""
 Select Case Cells(RowNum, 2).Value
   Case Is = "London", "Brighton", "Southampton"
     Cells(RowNum, 3).Value = "South"
   Case Is = "Leicester", "Milton Keynes", "Peterborough"
     Cells(RowNum, 3).Value = "Midlands"
   Case Is = "Manchester", "Leeds"
     Cells(RowNum, 3).Value = "North"
   Case Is = "Glasgow", "Aberdeen"
     Cells(RowNum, 3).Value = "Scotland"
 End Select
 RowNum = RowNum + 1
Loop

Conclusion

The Select Case statement can provide more clarity and smaller code when writing difficult and complex logical tests.
We refer to the variable, cell or form control we are testing just once and then run it through different cases.

The last example is a classic example of many tests performed in a clear and concise manner. A If Then Else attempt would look a lot more messy.

Want to learn Excel VBA fast? Enrol in the complete Excel VBA course for beginners.