VBA StrComp

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 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 worksheet we get the result as either TRUE or FALSE. But with 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.

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 the Result as “Exact” if both are the same, else 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 –

• VBA InStr
• String Conversion in VBA
• End in VBA
• Call Sub in VBA