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.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *