VBA String Comparison

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 what 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 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 choose “vbTextCompare”.

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 the 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 StrComp function in excel VBA along with examples and download excel template. You may also have a look at other articles related to Excel VBA –

• Guide to VBA String Functions
• VBA Split String into Array
• VBA SubString Methods
• VBA Text