Using Select Case

Using Select Case

The If statement can get quite cumbersome to read and understand if there are many ElseIf clauses. To simplify your code while maintaining the same basic structure, you can use the Select Case statement. Here's the previous example rewritten as a Select Case:

 Function GetDayName(dtmDayToCheck) As String   ' Returns the day of the specified date   Select Case DatePart("w", dtmDayToCheck)     Case 1       GetDayName = "Sunday"     Case 2       GetDayName = "Monday"     Case 3       GetDayName = "Tuesday"     Case 4       GetDayName = "Wednesday"     Case 5       GetDayName = "Thursday"     Case 6       GetDayName = "Friday"     Case Else       GetDayName = "Saturday"   End Select End Function 

To use the Select Case structure, you place an expression that returns a value in the Select Case statement itself. After that, you can have any number of Case statements, each containing a value. VBA matches the result of the expression against each Case value in turn, and when it finds a match, it executes the statements immediately following that Case statement, stopping at the next Case or at the End Select. Note that you can also use Case Else to match anything that's not matched by an earlier case.

TIP

It's best to end a Select Case statement with a Case Else. That way, if anything goes wrong and none of the Case conditions are matched, the Case Else can alert you to the fact that no condition was matched. This approach can probably avoid data errors or even a runtime error.