Comparing Strings by Using Comparison Operators
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Because you're actually comparing ANSI values when you compare two strings, you can use the same comparison operators that you would use with numeric expressions — greater than (>), less than (<), equal to (=), and so on. In addition to these numeric comparison operators, you can use the Like operator, which is specifically for use in comparing strings, including strings that contain wildcard characters.
Using Comparison Operators
When you use comparison operators such as the greater than (>) and less than (<) operators to compare two strings, the result you get depends on the string-comparison setting for the module. Consider the following example:
"vba" > "VBA"
If the string-comparison setting is Option Compare Binary, the comparison returns True.
When Microsoft® Visual Basic® for Applications (VBA) performs a binary text comparison, it compares the binary values for each corresponding position in the string until it finds two that differ. In this example, the lowercase letter "v" corresponds to the ANSI value 118, while the uppercase letter "V" corresponds to the ANSI value 86. Because 118 is greater than 86, the comparison returns True.
If the string-comparison setting is Option Compare Text, "vba" > "VBA" returns False, because the strings are equivalent apart from case.
In a Microsoft® Access database, if the string-comparison setting is Option Compare Database and the New Database Sort Order option is set to General (the default setting), the string comparison is case-insensitive and the example returns False.
Using the Like Operator
You can perform wildcard string comparisons by using the Like operator. The following table shows the wildcard characters supported by VBA.
| Wildcard | Represents | Example |
|---|---|---|
| * | Any number of characters | t* matches any word beginning with "t." |
| ? | Any single character | t??t matches any four-letter word beginning and ending with "t." |
| # | Any single digit (0–9) | 1#3 matches any three-digit number beginning with "1" and ending with "3." |
| [charlist] | Any single character in charlist | [a-z] matches any letter that falls between "a" and "z" (case-sensitivity depends on Option Compare setting). |
| [!charlist] | Any single character not in charlist | [!A-Z] excludes the uppercase alphabetic characters (case-sensitivity depends on Option Compare setting). |
You can use the Like operator to perform data validation or wildcard searches. For example, suppose you want to ensure that a user has entered a telephone number in the format nnn-nnn-nnnn. You can use the Like operator to check that the entry is valid, as the following procedure does:
Function ValidPhone(strPhone As String) As Boolean
' This procedure checks that the passed-in value is
' a valid, properly formatted telephone number.
ValidPhone = strPhone Like "###-###-####"
End Function
This procedure compares characters in a string to make sure that certain positions contain numeric characters. To return True, all characters must be digits between 0 and 9 or hyphens, and the hyphens must be present at the correct position in the string.
See Also
Getting the Most Out of Visual Basic for Applications | Comparing Strings | Specifying the String-Comparison Setting for a Module | Overriding the Default String-Comparison Setting