Notes on select case

The format of Select Case makes it look as difficult as a complex nested If.Else, but Select Case structures are easier to code and to maintain than their If.. .Else counterparts. Select Case is a good substitute for long, nested If.Else conditions when several choices are possible.

The Case expression can be any VBA expression - such as a calculation, a string value, or a numeric value - provided that it results in an integer or a string value, and this must match the expression's data type.

The Select Case structure is useful when you must make several choices based on data values. It can have two or more Case value sections, and the code that is executed depends on which value matches the expression. You can write an (optional) Case Else body of code which will be executed if none of the values match. Otherwise, nothing happens and control continues with the statement that follows End Select.

Here is an example of a macro based on month_bonus range in the weeklysales worksheet of the SALESMAN.XLS workbook The purpose of the macro is to classify cells in the range weekTotal by assigning different colours to the cells depending on whether the cell value is less than 60, equal to 60, 61 to 70 or greater than 70. The colours then assigned would be green, red, blue and yellow respectively. In this example (Listing 6.7), we can see that the Select Case statement is used for this four-way selection.

Listing 6.7 Implementing a multi-way If using Select Case

Sub HighlightRanges () Dim myCell As Object

For Each myCell In Range ("weekTotal") Select Case myCell.Value Case Is < 60

myCell.Interior.ColorIndex = 4 'green Case Is = 60

myCell.Interior.ColorIndex = 3 'red Case 61 To 70

myCell.Interior.ColorIndex = 5 'blue Case Is > 60

myCell.Interior.ColorIndex = 6 'yellow End Select Next End Sub

Avoid using Select Case when a simple If or If___Else will do the job. Unless you need to compare against more than a couple of values, use If and If___Else statements because of their simplicity. Use Select Case instead of the embedded If___Else when you have three or more options, because it keeps the code much simpler and easier to maintain.

0 0

Post a comment