VBA StrComp

VBA StrComp is a built-in function used to compare whether the two string values are identical. However, results are not defaulted as TRUE or FALSE like in the 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. It performs case-sensitive calculations. For example, "Hello" is not equal to "HELLO" because both word cases differ. It 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 "HELLO" even though both word cases differ. vbTextCompare
  • 2 = Access Compare. It performs database comparisons.

Results of String Comparison (StrComp) Function

When we compare two values in the worksheet, we get the result as either TRUE or FALSE. But with the VBA string comparison function. The results are not the same.

• We get zero (0) when String 1 equals String 2.
• We get one (1) when the String 1 value exceeds 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.

Example #1

Let us start with a simple example. For example, we will compare two values: "Excel VBA" and "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 we run this code, we will get Zero (0) because both String 1 and 2 values are the same.

Example #2

Now, we 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 we run this code, we will get 1 because since we supplied the Compare argument as "vbBinaryCompare,"it will check for case-sensitive characters.

Now, we 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 we run the above VBA code in Excel, we will get the below result.

If you look at C4 cells, String 1 and 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.