VBA String Comparison

Excel VBA String Compare

In our day to day life, we often do comparisons. Comparisons between two people, comparison between two products, comparison between two numbers and for that sake, comparison between two strings as well. While, for comparing two numbers, we have different mathematical functions available such as equality operator, we are in this lecture about to have a look at how to compare strings in under VBA in Excel. The conventional equality operator provides either TRUE or FALSE as a result of a comparison between two numbers or texts. Under VBA, we have STRCOMP as a function that specifies the comparison between two strings. However, the results are not as same as the equality operator (TRUE/FALSE) instead we have three possible results, based on the comparison. We will go through this in detail in the next session of this article. Let’s see the syntax and working of the VBA STRCOMP function.

Syntax of VBA StrComp function:

 

Where,

String1 and String2 are the two string values we wanted to compare with each other.

How to Compare Strings in VBA?

VbCompareMethod is a method of comparison for the two strings provided. There are three types of comparison mentioned below:

• vbBinaryCompare: Compares two strings binarily and is case sensitive (‘UPPER’ does not equal to ‘upper’). This is the default comparison method if not specified.
• vbTextCompare: Compares two strings as texts and is not case sensitive (‘Upper’ equals to ‘upper’).
• vbDatabaseCompare: Compares the text through the database and is only available for Microsoft Access not under VBA.

This function can return three possible results as follows:

• 0 – If String1 matches with String2.
• 1 – If String1 does not match with String2 and in binary comparison case String1 is lesser than String2.
• -1 – If String 1 does not match with String 2 and in binary comparison case, String 1 is greater than String2.

Examples of String Comparison in VBA

Below are the examples of Excel VBA String Comparison:

VBA String Comparison – Example #1

Let’s see some examples of the VBA StrComp function. For this, follow the below steps:

Step 1: Open a new Excel file and hit Alt + F11 to navigate towards the Visual Basic Editor. You can navigate to the Developers tab and click on the Visual Basic button to achieve the same result.

Step 2: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Step 3: Define a new sub-procedure using the Sub-End clause that can hold your macro.

Code:

Sub Strcomp_Ex1()

End Sub

Step 4: Define two new variables String1 and String2 with data type as a string that can be used to store the string values, we wanted to compare with each other.

Code:

Sub Strcomp_Ex1()

Dim String1 As String
Dim String2 As String

End Sub

Step 5: Assign text values to the two variables we have created using the assignment operator. I will assign values as ‘welcome to vba!’ and “WELCOME TO VBA!” respectively to the two variables.

Code:

Sub Strcomp_Ex1()

Dim String1 As String
Dim String2 As String
String1 = "welcome to vba!"
String2 = "WELCOME TO VBA!"

End Sub

Step 6: Now, define a new variable named CompResult as a string. This variable will be used to store the result of the StrComp function which compares whether two of the strings are equal or not.

Code:

Sub Strcomp_Ex1()

Dim String1 As String
Dim String2 As String
String1 = "welcome to vba!"
String2 = "WELCOME TO VBA!"
Dim CompResult As String

End Sub

Step 7: Now, use the assignment operator to assign the value of StrComp function to this newly defined variable.

Code:

Sub Strcomp_Ex1()

Dim String1 As String
Dim String2 As String
String1 = "welcome to vba!"
String2 = "WELCOME TO VBA!"
Dim CompResult As String
CompResult = StrComp(

End Sub

Step 8: Provide the arguments this function statement requires. Use String1 as the first argument String2 variable as the second argument and use vbTextCompare as the comparison argument to this statement.

Code:

Sub Strcomp_Ex1()

Dim String1 As String
Dim String2 As String
String1 = "welcome to vba!"
String2 = "WELCOME TO VBA!"
Dim CompResult As String
CompResult = StrComp(String1, String2, vbTextCompare)

End Sub

Step 9: Use VBA MsgBox property to display the output of the StrComp statement which is stored under CompResult variable.

Code:

Sub Strcomp_Ex1()

Dim String1 As String
Dim String2 As String
String1 = "welcome to vba!"
String2 = "WELCOME TO VBA!"
Dim CompResult As String
CompResult = StrComp(String1, String2, vbTextCompare)
MsgBox CompResult

End Sub

Step 10: Run this code by hitting F5 or Run button placed at the uppermost ribbon of Visual Basic Editor.

Zero here in message box means that two texts we have stored under String1 and String2 are matching with each other though there is a case difference. This is because we have specified vbTextCompare as a comparison argument. This argument checks whether the texts are matching with each other or not irrespective of checking the cases.

VBA String Comparison – Example #2

Now, follow the below steps to compare strings in VBA.

Step 1: Define sub-procedure which can hold your macro.

Code:

Sub strcomp_Ex2()

End Sub

Step 2: Define a variable Result as String so that we can assign a value of StrComp function to it.

Code:

Sub strcomp_Ex2()

Dim Result As String

End Sub

Step 3: Now use the Assignment operator to assign the value of StrComp to the variable named Result.

Code:

Sub strcomp_Ex2()

Dim Result As String
Result = StrComp(

End Sub

Step 4: Now, we need to specify the arguments for this function. Use “india” as a String1, “INDIA” as String2 arguments.

Code:

Sub strcomp_Ex2()

Dim Result As String
Result = StrComp("india", "INDIA"

End Sub

Step 5: For comparison of two strings, use vbBinaryCompare as an argument. It is OK if you put is as blank because it is an optional argument and has a default value as vbBinaryCompare itself. Due to this comparison operation, the system checks the binary codes of two strings given as input (which apparently will be different for two strings provided).

Code:

Sub strcomp_Ex2()

Dim Result As String
Result = StrComp("india", "INDIA",

End Sub

Step 6: Use VBA MsgBox Property to display the Result in the Message box.

Code:

Sub strcomp_Ex2()

Dim Result As String
Result = StrComp("india", "INDIA", vbBinaryCompare)
MsgBox Result

End Sub

Step 7: Run this code by hitting F5 or Run button that is placed at the upper ribbon in Visual Basic Editor.

As soon as you run this code, you’ll see an output as “1” in the message box which means these two texts are not equal. Rightly so because of the use of vbBinaryCompare property. This is how we can compare two strings in VBA using the StrComp function. Let’s close this article with some points to be remembered:

Things to Remember

• Comparison argument is optional and can be skipped as it has a default value as vbBinaryCompare which checks the two strings with their binary codes. It means “UPPER” and “upper” are both different.
• If no string value provided, this function returns a Null value
• We can use this function to compare the email addresses of different clients.
• If the String1 is greater than String2. This happens in the case of Binary Comparison; the output will be -1 in that case.

Recommended Articles

This is a guide to the VBA String Comparison. Here we discuss how to Compare Strings in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA SendKeys
2. VBA Name Worksheet
3. VBA CInt
4. VBA SubString