Excel VBA String Comparison
To compare two strings in VBA we have a built-in function i.e. “StrComp”. This we can read it as “String Comparison”, this function is available only with VBA and is not available as a worksheet function. It compares any two strings and returns the results as “Zero (0)” if both the strings are matching and if both supplied strings are not matching then we would get “One (1)” as the result.
In VBA or excel, we face plenty of different scenarios. One such scenario is “comparing two string values.” In a regular worksheet, we can do these multiple ways, but in VBA, how do you do this?
Below is the syntax of the “StrComp” function.
First, two arguments are quite simple,
- for String 1, we need to supply what the first value we are comparing and
- for String 2, we need to supply the second value we are comparing.
- [Compare] this is the optional argument of StrComp function. This is helpful when we want to compare case sensitive comparison. For example, in this argument, “Excel” is not equal to “EXCEL” because both these words are case sensitive.
We can supply three values here.
- Zero (0) for “Binary Compare,” i.e., “Excel,” is not equal to “EXCEL.” For case sensitive comparison, we can supply 0.
- One (1) for “Text Compare,” i.e., “Excel,” is equal to “EXCEL.” This is a non-case sensitive comparison.
- Two (2) this only for database comparison.
The results of the “StrComp” function do not default TRUE or FALSE but vary. Below are the different results of the “StrComp” function.
- We will get “0” as the result if the supplied strings are matching.
- We will get “1” if the supplied strings are not matching, and in case of numerical matching, we will get 1 if String 1 is greater than string 2.
- We will get “-1” if the string 1 number is less than the string 2 number.
How to Perform String Comparison in VBA?
Example #1
We will match “Bangalore” against the string “BANGALORE.”
First, declare two VBA variables as the string to store two string values.
Code:
Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String End Sub
For these two variables, store two string values.
Code:
Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" End Sub
Now declare one more variable to store the result of the “StrComp” function.
Code:
Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" Dim FinalResult As String End Sub
For this variable, open the “StrComp” function.
Code:
Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" Dim FinalResult As String FinalResult = StrComp( End Sub
For “String1” & “String2” we have already assigned values through variables, so enter variable names, respectively.
Code:
Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" Dim FinalResult As String FinalResult = StrComp(Value1, Value2, End Sub
The last part of the function is “Compare” for this choice “vbTextCompare.”

4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Code:
Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbTextCompare) End Sub
Now show the “Final Result” variable in the message box in VBA.
Code:
Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbTextCompare) MsgBox FinalResult End Sub
Ok, let’s run the code and see the result.
Output:
Since both the strings “Bangalore” and “BANGALORE” are the same, we got the result as 0, i.e., matching. Both the values are case sensitive since we have supplied the argument as “vbTextCompare” it has ignored case sensitive match and matched only values, so both the values are the same, and the result is 0, i.e., TRUE.
Code:
Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbTextCompare) MsgBox FinalResult End Sub
Example #2
For the same code, we will change the compare method from “vbTextCompare” to “vbBinaryCompare.”
Code:
Sub String_Comparison_Example2() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbBinaryCompare) MsgBox FinalResult End Sub
Now run the code and see the result.
Output:
Even though both the strings are the same, we got the result as 1, i.e., Not Matching because we have applied the compare method as “vbBinaryCompare,” which compares two values as case sensitive.
Example #3
Now we will see how to compare numerical values. For the same code, we will assign different values.
Code:
Sub String_Comparison_Example3() Dim Value1 As String Dim Value2 As String Value1 = 500 Value2 = 500 Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbBinaryCompare) MsgBox FinalResult End Sub
Both the values are 500, and we will get 0 as a result because both the values are matched.
Output:
Now I will change the Value1 number from 500 to 100.
Code:
Sub String_Comparison_Example3() Dim Value1 As String Dim Value2 As String Value1 = 1000 Value2 = 500 Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbBinaryCompare) MsgBox FinalResult End Sub
Run the code and see the result.
Output:
We know Value1 & Value2 aren’t the same, but the result is -1 instead of 1 because for numerical comparison when the String 1 value is greater than String 2, we will get this -1.
Code:
Sub String_Comparison_Example3() Dim Value1 As String Dim Value2 As String Value1 = 1000 Value2 = 500 Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbBinaryCompare) MsgBox FinalResult End Sub
Now I will reverse the values.
Code:
Sub String_Comparison_Example3() Dim Value1 As String Dim Value2 As String Value1 = 500 Value2 = 1000 Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbBinaryCompare) MsgBox FinalResult End Sub
Run the code and see the result.
Output:
This is not special. If not match, we will get 1 only.
Things to Remember here
- [Compare] argument of “StrComp” is optional, but in case of case sensitive match, we can utilize this, and the option is “vbBinaryCompare.”
- The result of numerical values is slightly different in case String 1 is greater than string 2, and the result will be -1.
- Results are 0 if matched and 1 if not matched.
Recommended Articles
This has been a guide to the VBA string comparison. Here we discuss how to compare two string values using the StrComp function in excel VBA along with examples and download an excel template. You may also have a look at other articles related to Excel VBA –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion