How to use StrComp function in VBA?

In VBA (Visual Basic for Applications), the `StrComp` function is used to compare two strings and return a value indicating the result of the comparison. The function can perform a binary comparison or a textual comparison, depending on the specified comparison mode.

The syntax of the `StrComp` function is as follows:

StrComp(string1, string2, [compare])

`string1` is the first string to compare.
`string2` is the second string to compare.
`compare` is optional. It specifies the type of comparison. The value can be:

`vbUseCompareOption` (-1): Uses the option compare setting.
`vbBinaryCompare` (0): Performs a binary comparison, which is case-sensitive.
`vbTextCompare` (1): Performs a textual comparison, which is not case-sensitive.
`vbDatabaseCompare` (2): Microsoft Access only. It is related to the database sort order.

The result of the `StrComp` function can be:

`-1`: `string1` is less than `string2`.
`0`: `string1` is equal to `string2`.
`1`: `string1` is greater than `string2`.
`Null`: Either `string1` or `string2` is `Null`.

Here’s an example of using the `StrComp` function in VBA:

Sub UseStrComp()
    Dim result As Integer
    Dim string1 As String
    Dim string2 As String
    
    string1 = "Hello"
    string2 = "hello"
    
    ' Perform a binary comparison (case-sensitive)
    result = StrComp(string1, string2, vbBinaryCompare)
    MsgBox "Binary comparison result: " & result
    
    ' Perform a textual comparison (not case-sensitive)
    result = StrComp(string1, string2, vbTextCompare)
    MsgBox "Textual comparison result: " & result
End Sub

When you run this code, it will show two message boxes. The first one will display the result of the binary comparison, which in this case is `-1` because “Hello” is lexically less than “hello” when case is considered. The second message box will show the result of the textual comparison, which will be `0` because when ignoring case, the two strings are considered equal.

facebooklinkedin