Page 98 - Excel Progamming Weekend Crash Course

h540629 ch05.qxd 9/2/03 9:33 AM Page 73

Session 5 — Operators 73

There’s a twist, however, in that upper- and lowercase letters have different ASCII codes,
with the uppercase codes all being less than the lowercase codes. VBA’s default, therefore, is
to consider “Apple” to be unequal to, and less than, “apple.” In most cases, this type of text
comparison is not wanted, and you have two ways around it.
One approach is to include the statement Option Compare Text at the start of the
module, outside of any procedure. This instructs VBA to perform text comparisons with
upper- and lowercase letters being equivalent, so that “Apple” does equal “apple.” This
applies to all text comparisons within the module.
The other approach is to use VBA’s UCase or LCase function to convert a string to all
upper- or lowercase, and then perform the comparison:
UCase(string1) = UCase(string2)
These functions do not change the original strings but only make temporary uppercase
copies for the comparison.
The final string comparison operator is Like, which determines if a string matches a
pattern. It is used as follows:

string Like pattern
Pattern is itself a string composed of the elements described in Table 5-2. The elements
you use, and their order, depend on what you want to match. Table 5-3 shows some examples
of patterns, and what they match and do not match.

Table 5-2 Elements Used in the Pattern for the Like Operator

Element Matches
A single character The character itself
* Any sequence of 0 or more characters
? Any single character

# Any digit
[list] Any character in list

[!list] Any character not in list

Table 5-3 Pattern Examples for the Like Operator

Pattern Matches Does Not Match
b[ae]t bat, bet but, bit
[a-d]?? ace, bad, cat, dig fix, apple, coast, vice

Continued