JIYIK CN >

We will look at how to compare strings in VBA using different methods.

Comparing Strings in VBA

While working with excel worksheets, there are situations where we have to compare strings and want to get the result of which string is less than or greater than the other string. VBA provides a perfect method for this called StrComp(), which compares two strings to get a numerical result.

This method will return if the two strings we compare are equal 0. If the first string is less than the other, we will get -1as the return value.

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

StrComp()The String comparison method uses three comparison methods, but only two of them are most commonly used, and we will discuss these two methods. If we want to compare two strings in binary, we can use vbBinaryCompareString comparison, which is case-sensitive.

If we want to compare the text of two strings, we can use vbTextCompare, a case insensitive method. Let's take 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:

In the above example, we passed the same string that we got 0as the 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 above code, we are passing different strings we get -1the length of as return value since the second string is greater than the first. Let us try StrComp()the string comparison method in and check how the result is different if we use the same string but with different case and use vbTextComparethe 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 method in our example by adding an extra space vbBinaryCompare.

Code:

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

compare = StrComp(stringOne, stringTwo, vbBinaryCompare)

MsgBox compare

End Sub

Output:

This way, we can use StrComp()the method and built-in methods such as vbTextCompareand vbBinaryCompareto compare two strings based on length or text.