Page 115 - Microsoft Office Excel 2003 Programming Inside Out

Creating Sub and Function Procedures

Inside Out

The Pitfalls of Case Statements and Conditional Formats
If you compare the If…Then and Select Case versions of the AvailableCredit routines side by
side, you might notice that the If…Then statements check for values greater than some other
value (for example, If .Value > 5000 Then .Font.Color = vbBlue),
whereas all but the last Case statement checks for values in a definite range. You should
use definitive rules in a Select Case statement because the instant Excel finds a case that’s
true, it exits the Select Case statement. So, if you were to evaluate a cell value of 5500
using the If…Then statement listed in the preceding example, the procedure would go
through the following steps:
1 Is the cell blank? No, so take no action.
2 Is the value less than 1000? No, so take no action.
3 Is the value greater than 1000? Yes, so change the font color to black.
4 Is the value greater than 5000? Yes, so change the font color to blue.
5 Is the value greater than 10,000? No, so take no action.
The routine changed the font color an extra time (first to black, and then to blue), but you
got the right result and the extra step is not a problem for a simple program on a computer
that can perform millions of calculations per second. However, because the rules in the fol
lowing Select Case statement are constructed in the same order, the cell’s contents would
be displayed in black type, not blue.

Select Case Remaining
Case ""
Exit Sub
Case Is < 1000
ActiveCell.Font.Color = vbRed
Case Is >= 1000
ActiveCell.Font.Color = vbBlack
Case Is >= 5000
ActiveCell.Font.Color = vbBlue
Case Is >= 10000 Chapter 5
ActiveCell.Font.Color = vbGreen
End Select
You get incorrect results because the routine quits when it finds the cell value is less than or
equal to 9999. You’ll run into the same problem when you create conditional formats, which
you do by clicking Format, Conditional Formatting and using the controls in the Conditional
Formatting dialog box to create your rules. The rules in the following graphic correspond to
the incorrect order noted earlier and would also result in an improperly formatted cell value.
continued

89
Part 2: Visual Basic for Applications