Excel VBA StrComp Function

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. vbBinaryCompare1 = Text Compare. This option performs the non-case-sensitive calculations. For example, “Hello” is equal to “HELLO” even though both word cases differ. vbTextCompare2 = 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 stringVBA StringString functions in VBA do not replace the string; instead, this function creates a new string. There are numerous string functions in VBA, all of which are classified as string or text functions.read more comparison function. The results are not the same.

  • 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.

  • 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 2We get NULL when String 1 or String 2 value is NULL.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA StrComp (wallstreetmojo.com)

Examples to use VBA StrComp Function

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

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.“

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.

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 ExcelVBA Code In ExcelVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more, 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.

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.

This article has been a guide to VBA STRCOMP. Here, we discuss using 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 InStrString Conversion in VBAEnd in VBACall Sub in VBAVBA Const