Programming Excel with VBA and .NET

Compare Strings

By default, Visual Basic compares strings in a case-sensitive way. That means "Jeff" and "jeff" are not considered the same. You can change that by adding an Option Compare Text statement at the beginning of a module or class, as shown here:

    ' Ignore case when comparing 
 strings.
    Option Compare Text

    Sub CompareStrings( )
        ' Displays True if Option Compare is Text.
        Debug.Print "Jeff" = "jeff"
    End Sub

Option Compare applies to all the ways to compare string (=, Like, StrComp) throughout the module or class. You can achieve a similar result on a smaller scale by temporarily converting the strings to upper- or lowercase before comparing them:

    Debug.Print LCase("Jeff") = LCase("jeff")

That approach is actually more common than changing Option Compare since it allows you to use both case-sensitive and case-insensitive comparisons within a class or module.

The Like operator is similar to = in Visual Basic, except it also allows you to match patterns of characters using the comparison characters listed in Table 3-9.

Table 3-9. Pattern-matching characters

Use	To match
?	Any single character
*	Zero or more characters
#	Any single digit
[ list ]	Any single character in list
[! list ]	Any single character not in list

For example, the following function returns True if a passed-in argument is formatted as a Social Security number:

    Function IsSSN(ssn As String) As Boolean
        If ssn Like "###-##-####" Then
            IsSSN = True
        Else
            IsSSN = False
        End If
    End Function

The Instr