How to Compare Strings in VBA

We will introduce how to compare strings with different methods in VBA.

Compare Strings in VBA

While working with excel sheets, there are some situations in which we have to compare strings and want to get results on which string is lesser or greater than the other one. VBA provides a perfect method for this purpose known as StrComp(), which compares two strings to get the results in numeric values.

If both the strings we are comparing are equal, this method will return 0. If the first string is lesser than the other string, we will get the -1 as a return value.

If the first input string is larger than the other string, we will get the value 1 as a result. If one of the user input strings is empty, the return value will be Null.

Three comparison methods are used by the StrComp() method, but only two are most commonly used, and we will discuss these two methods. If we want to compare the binary of two strings, we can use the vbBinaryCompare, and this method is case-sensitive.

If we want to compare the text of both strings, we can use the vbTextCompare, a case-insensitive method. Let’s have an example and compare two strings.

Code:

# VBA
Sub test()
Dim compare As Integer
stringOne = "Hello World"
stringTwo = "Hello World"

compare = StrComp(stringOne, stringTwo)

MsgBox compare

End Sub

Output:

From the above example, we passed the same strings we got 0 as a return value. Let’s pass different values and check what return value we get.

Code:

# VBA
Sub test()
Dim compare As Integer
stringOne = "Hello World"
stringTwo = "Hello World!"

compare = StrComp(stringOne, stringTwo)

MsgBox compare

End Sub

Output:

From the code above, we passed the different strings with length we got -1 as a return value because the second string was greater than the first one. Let’s try the string comparison methods in StrComp() and check how the results differ if we use the same strings but different in capitalization and use the vbTextCompare method.

Code:

# VBA
Sub test()
Dim compare As Integer
stringOne = "Hello World"
stringTwo = "Hello WorLd"

compare = StrComp(stringOne, stringTwo, vbTextCompare)

MsgBox compare

End Sub

Output:

Now, we use the vbBinaryCompare method in our example by adding a single extra space.

Code:

# VBA
Sub test()
Dim compare As Integer
stringOne = "Hello World"
stringTwo = "Hello  World"

compare = StrComp(stringOne, stringTwo, vbBinaryCompare)

MsgBox compare

End Sub

Output:

In this way, we can compare two strings based on their length or the text using the StrComp() method with built-in methods such as vbTextCompare and vbBinaryCompare.