Compare Two Strings

You can compare two strings to see if they are alike using the built-in StrComp function in VBA. When you compare two strings, the StrComp function returns a value indicating whether the strings are the same. If the strings are different, the function returns a value that shows which string is larger.

The StrComp function has three different arguments, of which the first two arguments are required: string1, string2, and compare. The string1 and string2 arguments indicate the strings to compare. You can use a string enclosed in quotes, a string variable, or a reference to a cell containing a string as the argument value.

An optional constant value, the compare argument, determines how the function compares the strings. If you omit the argument, the function uses a binary comparison of the strings, or the comparsion specified by the Option Compare statement. The vbBinaryCompare constant indicates that you want to compare the strings based upon the ANSI character code for each character in the string. On an ANSI character chart, lowercase letters have a larger value than uppercase characters.

Use the vbTextCompare constant if you want to compare strings based upon the actual text, regardless of the case. With this comparison, the StrComp function sees an M and m as the same character.

You can use the vbUseCompareOption to indicate that you want to use the type of comparison specified by the Option Compare statement. If the statement does not exist, a binary comparison is performed.

The StrComp function returns a value of 0 if the strings are the same. If the first string is larger, it returns 1. If the second string is larger, the function returns -1. If either string is Null, the function returns Null.

COMPARE TWO STRINGS

COMPARE TWO STRINGS

Vba Option Compare Text

□ Type Option Compare Text at the top of the module.

Create a new subroutine.

□ Type Option Compare Text at the top of the module.

Create a new subroutine.

< Type Dim String1 As String replacing Stringl with the first string to compare.

^H Type Dim String2 As String replacing String2 with the second string to compare.

Q Assign values to Stringl and String2.

When comparing strings, the StrComp function treats upper and lowercase characters differently for a binary comparison. In other words, the StrComp function considers a J and j the same. If you want to compare strings to see if they contain the same characters, you must convert the strings to the same case — either all uppercase or all lowercase — before making the comparison. VBA provides two built-in functions that you can use to convert strings. The UCase function, UCase(string), converts the lowercase characters in a string to uppercase. The LCase function, LCase(string), converts all uppercase characters in the string to lowercase. Both functions ignore numbers and symbols in the string and require one argument to convert the string. You can make the string argument an actual string enclosed in quotes, or a reference to a string such as a variable name. If the string contains a Null value, the function returns a value of Null.

You can place the Option Compare statement at the top of a module to indicate the type of comparison to make between strings. To perform a binary comparison of all strings, type:

Option Compare Binary

If you want to compare all strings as text, type:

Option Compare Text

Vba Option Compare Text

Type If StrComp(String1, String2) = 0 Then, replacing Stringl and String2 with the strings in steps 3 and 4.

□ Type VBA statements to perform if strings match.

• Switch to Excel and run the macro.

■ The StrComp function compares the two strings and returns a value.

Type If StrComp(String1, String2) = 0 Then, replacing Stringl and String2 with the strings in steps 3 and 4.

□ Type VBA statements to perform if strings match.

• Switch to Excel and run the macro.

■ The StrComp function compares the two strings and returns a value.

0 0

Responses

Post a comment