The VBA StrComp Function


The VBA StrComp function compares two strings and returns an integer representing the result of the comparison.

The syntax of the function is:

StrComp( String1, String2, [Compare] )

Where the function arguments are:

String1 - The first string to be compared.
String2 - The second string to be compared.
[Compare] -

An optional argument, specifying the type of String comparison to make.

This can be any of the following values:

vbBinaryCompare - performs a binary comparison
vbTextCompare - performs a text comparison
vbDatabaseCompare - performs a database comparison
If omitted, the [Compare] argument takes on the default value vbBinaryCompare.

The Integer that is returned from the StrComp function is either:

0 - indicates that the two strings are equal;
-1 - indicates that String1 is less than String2;
+1 - indicates that String1 is greater than String2;
Null - indicates that String1 or String2 is Null.

VBA StrComp Function Examples

The following VBA code compares three different pairs of Strings.

' Example 1 - Compare the Strings "John Smith" and "John Smith".
Dim res1 As Integer
res1 = StrComp( "John Smith", "John Smith" )
' The Integer res1 is now equal to 0 (the two strings are equal).
' Example 2 - Compare the Strings "John Smith" and "Wilbur Cross".
Dim res2 As Integer
res2 = StrComp( "John Smith", "Wilbur Cross" )
' The Integer res2 is now equal to -1 (String1 is less than String2).
' Example 3 - Compare the Strings "John Smith" and "Charlie Jones".
Dim res3 As Integer
res3 = StrComp( "John Smith", "Charlie Jones" )
' The Integer res3 is now equal to 1 (String1 is greater than than String2).

After running the above VBA code:

res1 =  0   (String1 and String2 are equal)
res2 = -1   (String1 and String2 are not equal; String1 is less than String2)
res3 =  1   (String1 and String2 are not equal; String1 is greater than String2)

Note that the [Compare] argument is omitted from the above calls to the StrComp function. The function therefore uses the default value vbBinaryCompare in each case.

Note that the vbBinaryCompare option is case-sensitive and so would not, for example, consider the Strings "John Smith" and "John SMITH" to be equal.