Page 137 - Excel Progamming Weekend Crash Course

h540629 ch09.qxd 9/2/03 9:34 AM Page 112

112 Saturday Morning

Table 9-3 InStr and InStrRev Examples

Function call Return value
InStr(, “Microsoft”, “o”) 5
InStrRev(“Microsoft”, “o”) 7
InStrRev(“Microsoft”, “o”, 6) 5
InStr(,”Microsoft”, “MICRO”, vbBinaryCompare) 0

To find the second and subsequent occurrences of one string within another,
call InStr repeatedly. For the second and subsequent calls, set the start
Tip argument equal to one more than the value returned by the previous call.

Comparing Strings
The StrComp function compares two strings. The syntax is as follows:

StrComp(str1, str2, compare)
Str1 and str2 are the strings to compare. Compare is a constant that specifies how the
comparison is to be performed. This is the same as the values for the InStr and InStrRev
function, explain in Table 9-1 earlier in this session. The function returns:
-1 if str1 is less than str2.
0 of str1 and str2 are equal.
1 if str1 is greater than str2.
Null if either str1 or str2 is Null.
The concepts of “less than” and “greater than” are interpreted using the ASCII values of
the characters in the strings. For the letters, this corresponds to alphabetical order with the
caveat that all uppercase letters are less than the corresponding lowercase letters when a
binary comparison is used.

String Conversions
VBA provides various functions for converting string data between one form and another.
These functions are covered in this section.

The StrConv Function
The StrConv function performs a variety of conversions. Its syntax is:

StrConv(string, conversion)