Excel VBA StrComp Function
VBA StrComp is a built-in function used to compare whether the two string values are same or not. However, results are not defaulted TRUE or FALSE like in worksheet rather it is different.
Before we look at the results, let me show you the syntax of the StrComp function first.
- String 1: String 1 is the first string or value we are comparing.
- String 2: String 2 is the second string or value we are comparing against String 1.
- Compare: We can supply three options here.
-
-
- 0 = Binary Compare. This performs case sensitive calculations. For example, “Hello” is not equal to “HELLO” because both the words cases are different. This is the default value if you ignore this parameter. vbBinaryCompare
- 1 = Text Compare. This option performs the non-case sensitive calculations. For example, “Hello” is equal to the “HELLO” event though both the words cases are different. vbTextCompare
- 2 = Access Compare. This performs database comparison.
-
Results of String Comparison (StrComp) Function
As I told when we compare two values in the worksheet, we get the result as either TRUE or FALSE. But with the VBA string comparison function, results are not the same.
- We get zero (0) when String 1 is equal to String 2.
- We get one (1) when the String 1 value is greater than the String 2 Value.
- We get minus one (-1) when String 1 value is less than String 2
- We get NULL when String 1 or String 2 value is NULL.
Examples to use VBA StrComp Function
Example #1
Let’s start with a simple example. For example, we will compare two values i.e., “Excel VBA” & “Excel VBA.”
Code:
Sub StrComp_Example1() Dim FirstValue As String 'To Store String 1 value Dim SecondValue As String 'To Store String 2 value Dim Result As String 'To Store Result of the StrComp formula FirstValue = "Excel VBA" 'Assign the String 1 value SecondValue = "Excel VBA" 'Assign the String 2 value Result = StrComp(FirstValue, SecondValue, vbBinaryCompare) 'Apply StrComp function MsgBox Result 'Show the result in message box End Sub
When I run this code, we will get Zero (0) as the result because both String 1 and String 2 values are the same.

4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
Example #2
Now I will change the cases of two words.
String 1 = Excel Vba
String 2 = Excel VBA
Code:
Sub StrComp_Example2() Dim FirstValue As String 'To Store String 1 value Dim SecondValue As String 'To Store String 2 value Dim Result As String 'To Store Result of the StrComp formula FirstValue = "Excel Vba" 'Assign the String 1 value SecondValue = "Excel VBA" 'Assign the String 2 value Result = StrComp(FirstValue, SecondValue, vbBinaryCompare) 'Apply StrComp function MsgBox Result 'Show the result in message box End Sub
When I run this code, we will get 1 because since we supplied the Compare argument as “vbBinaryCompare,”, it will check for case-sensitive characters.
Now I will change the Compare option from “vbBinaryCompare” to “vbTextCompare”
Code:
Sub StrComp_Example3() Dim FirstValue As String 'To Store String 1 value Dim SecondValue As String 'To Store String 2 value Dim Result As String 'To Store Result of the StrComp formula FirstValue = "Excel Vba" 'Assign the String 1 value SecondValue = "Excel VBA" 'Assign the String 2 value Result = StrComp(FirstValue, SecondValue, vbTextCompare) 'Apply StrComp function MsgBox Result 'Show the result in message box End Sub
With this Compare, we will get zero (0) because vbaTextCompare ignores case sensitive words.
Example #3
Case Study of VBA StrComp with IF Condition
Assume you have the data like the below image.
We need to compare String 1 with String 2 and arrive at the Result as “Exact” if both are the same, else the result should be “Not Exact.”
The below code will do the job for us.
Code:
Sub StrComp_Example4() Dim Result As String Dim I As Integer For i = 2 To 6 Result = StrComp(Cells(i, 1).Value, Cells(i, 2).Value) If Result = 0 Then Cells(i, 3).Value = "Exact" Else Cells(i, 3).Value = "Not Exact" End If Next i End Sub
When I run the above VBA code in Excel, we will get the below result.
If you look at C4 cell String 1 and String 2 are the same, but the characters are case sensitive, so the result is “Not Exact.” To overcome this issue, we need to supply the Compare as vbTextCompare.
Below is the modified code to get the result as “Exact” for the C4 cell.
Code:
Sub StrComp_Example4() Dim Result As String Dim I As Integer For i = 2 To 6 Result = StrComp(Cells(i, 1).Value, Cells(i, 2).Value, vbTextCompare) If Result = 0 Then Cells(i, 3).Value = "Exact" Else Cells(i, 3).Value = "Not Exact" End If Next i End Sub
This code will return the below result.
Recommended Articles
This has been a guide to VBA STRCOMP. Here we discuss how to use StrComp to compare two values in VBA along with examples and download an excel template. You may also have a look at other articles related to Excel VBA –
- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion