Page 116 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Tip You’re limited to three conditions for conditional formatting
The conditional format in the graphic also points out one of the advantages of VBA: you
are allowed only three conditions in the Conditional Formatting dialog box.

Of course, if you were to reverse the order of the Case statements (ignoring the first case,
which checks for a blank cell), the most restrictive case would come first, the second most
restrictive next, and so on. And that’s the trick to creating effective Select Case and
If…Then statements: after you check for a blank cell, you should always check for the most
restrictive set of values. Also bear in mind that the comparison operator and the statement
you use determine the order in which the sets become more or less restrictive. In a Select
Case statement, if you want to check whether values are greater than other values, you
need to check for the higher values first (for example, you ask “is the value greater than
10,000” before asking “is the value greater than 5000”); if you check whether values are
less than other values, you need to check for the lower values first (for example, you ask “is
the values less than 1000” before asking “is the value less than 5000”).

Defining the Scope of a Sub Procedure

The first element of a Sub procedure, the optional Public or Private declaration, determines
Chapter 5
the scope of the procedure. Simply put, a procedure with a Private scope can be referred to
only by other procedures in the same module, whereas procedures with a Public scope can be
referred to by any procedure in any module.

Note Unless otherwise stated, every procedure is a Public procedure.

90
Part 2: Visual Basic for Applications