Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use VBA StrComp in Excel (5 Common Examples)

In our daily life, we need to compare different things. In Excel, we have some inbuilt functions to perform this kind of comparison. The StrComp is one of them. It is a VBA-based string comparison function of Excel. After comparing two different strings and giving a return. In this article, we will discuss the VBA StrComp function with examples.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to StrComp Function

Function Objective:

The StrComp function compares two strings and gives an output.

Syntax:

StrComp(String1,String2,[compare])

Argument:

Argument Required/Optional Explanation
String1 Required A proper string data for use.
String2 Required Another proper string data for use.
compare Optional This determines the comparison method between the strings.

Settings:

Some setting details of the StrComp is given below:

Constant Value Meaning
vbUseCompareOption -1 This determines a comparison based on Option Compare function.
vbBinaryCompare 0 This determines a binary comparison.
vbTextCompare 1 This determines a text comparison.
vbDatabaseCompare 2 This determines a comparison according to the user’s database.

Returns:

The return pattern of the StrComp function is given in the below table.

Condition Return
String1 < String2 -1
String1 = String2 0
String1 > String2 1
If any of the string is void Null

Available In:

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000


5 Examples of Using VBA StrComp in Excel

We will show some examples of VBA StrComp here.

VBA StrComp in Excel

The above data set will be used in this article.


1. All Returns of StrComp in a Single Window

We know that StrComp delivers four types of returns. Here we will see all the returns in a single window.

Step 1:

  • First, go to the Developer tab.
  • Then, choose the Record Macro option.
  • Now, put Str_Compare_1 on the Macro name box.
  • Then, press OK.

Step 2:

  • Press the Alt+F11 to enter the VBA window.
  • Then, choose Module from the Insert tab.

Step 3:

  • When the module opens, write the below code.
Sub Str_Compare_1()
Dim Result As Variant
MsgBox ("Segment 1 :" & StrComp("Jessica", "Jessica"))
MsgBox ("Segment 2 :" & StrComp("Jessica", "Jesica"))
MsgBox ("Segment 3 :" & StrComp("Jesica", "Jessica"))
MsgBox ("Segment 4 :" & StrComp("Jessica", Null))
End Sub

StrComp in a Single Window

Step 4:

  • Then, press the marked button from the tab or press F5 to run the code.

Step 5:

  • Now, get all the results by pressing OK one after one.
  • All the results collage to get an overall view.

StrComp in a Single Window

Read More: How to Use VBA Str Function in Excel (4 Examples)


2. Take Cell Reference in StrComp

We will take user input to compare strings.

Cell Reference in StrComp

We will use this data set for this example.

Step 1:

  • First, create a macro like the previous way named Str_Compare_2.
  • Then, press OK.

Step 2:

  • Go to the VBA Module by pressing Alt+F11.
  • Put the below code on the module.
Sub Str_Compare_2()
Range("D5").Value = StrComp(Range("B5"), Range("C5"))
End Sub

Cell Reference in StrComp

Step 3:

  • Now, run the code by pressing F5.

In the return box, we get 0, as both of the comparing objects are the same.

Here, we used the code without using any variable. Now, we will insert variables to make the code more effective.

Step 4:

  • Apply the below code on the command module.
Sub Str_Compare_2()
Dim A, B, C As Variant
Set A = Range("B5")
Set B = Range("C5")
Set C = Range("D5")
C.Value = StrComp(A, B)
End Sub

Cell Reference in StrComp

Step 5:

  • Again, run the code applying pressing F5.

We can see that both the results are the same.

Now, change any of the strings to see what happens.

Step 6:

  • We changed the string of Cell C5 by a new string.

Step 7:

  • Again go to the command module and run the code pushing F5 button.

Cell Reference in StrComp

Here, we can see that return is “1” as string1 is larger than string2.

Read More: How to Use VBA StrConv Function (5 Examples)


3. Insert User Input to Perform VBA StrComp

We will take user input and perform the VBA StrComp process.

Step 1:

  • First, create a macro named Str_Compare_3.
  • Then, press OK.

Step 2:

  • Go to the Developer tab.
  • Choose Macros.
  • Select the newly created macro and then click Step Into option.

User Input to Perform VBA StrComp

Step 3:

  • Now, put the below code on the command module.
Sub Str_Compare_3()
Dim Value_1, Value_2, Final_Result As String
Value_1 = InputBox("Enter a Value_1")
Value_2 = InputBox("Enter a Value_2")
Final_Result = StrComp(Value_1, Value_2)
MsgBox Final_Result
End Sub

User Input to Perform VBA StrComp

Step 4:

  • Put the inputs of Value_1 and Value_2 by clicking OK.
  • Then, press F5 to run the code.

Step 5:

  • We will get the return, after pressing the OK on the last input section.

User Input to Perform VBA StrComp

Here, we get -1 after comparing both strings.


Similar Readings


4. Text and Binary Comparison Using the StrComp

We will check the text and binary comparison ability of the StrComp function.

Step 1:

  • Create a new macro named Str_Compare_4.
  • Then press OK.

Step 2:

  • Go to the command module shown in the previous sections.
  • Write the following code.
Sub Str_Compare_4()
Dim Value_1, Value_2, Final_Result As String
Value_1 = InputBox("Enter a Value_1")
Value_2 = InputBox("Enter a Value_2")
Final_Result = StrComp(Value_1, Value_2, vbTextCompare)
MsgBox Final_Result
End Sub

Text Comparison Using the StrComp

Step 3:

  • Then press F5 to run the code.
  • Also, put the inputs mentioned in the below image.

Text Comparison Using the StrComp

Step 4:

  • After pressing OK in the last input field we will get the result.

Text Comparison Using the StrComp

Here, we input “John” and “john”. This is a text comparison and it will only cheeks if all the characters are the same of both strings.

Step 5:

  • We change the inputs and run the code again.

Here is our final result. As both strings are not the same result is not 0.

We will show the binary comparison here.

Step 6:

  • Modify the previous code inserting vbBinaryCompare
  • Now, put the below code on the command module.
Sub Str_Compare_4()
Dim Value_1, Value_2, Final_Result As String
Value_1 = InputBox("Enter a Value_1")
Value_2 = InputBox("Enter a Value_2")
Final_Result = StrComp(Value_1, Value_2, vbBinaryCompare)
MsgBox Final_Result
End Sub

Binary Comparison Using the StrComp

Step 7:

  • Run the code by pressing the F5
  • Put “John” as the Value_1 and “john” as Value_2.

Binary Comparison Using the StrComp

Step 8:

  • Finally, press OK on the second input dialog box.

Binary Comparison Using the StrComp

In the case of text comparison result was 0, but here this is -1.

Read More: How to Use InStr Function in VBA (3 Examples)


5. Group Comparison Using VBA StrComp

We will apply the StrComp function on a certain range using the If function.

Step 1:

  • We add a column named Compare in the data set.

Step 2:

  • Create another macro named Str_Compare_5.
  • Then, press OK.

Step 3:

  • Now, put the below code on the command module.
Sub Str_Compare_5()
Dim Value As String
For i = 5 To 8
Value = StrComp(Range("B" & i), Range("C" & i))
If Value = 0 Then
Range("D" & i).Value = "Equal"
Else
Range("D" & i).Value = "NOT Equal"
End If
Next i
End Sub

Group Comparison Using VBA StrComp

Step 4:

  • Press F5 to run the code.

Group Comparison Using VBA StrComp


Things to Remember

  • This function compares the maximum of two objects at a time.
  • The return only 4 values -1,0,1 and null. Any other value will not be possible to show.
  • Compare argument will need to be input carefully.

Conclusion

In this article, we described the VBA StrComp function in EXcel. We described with details using examples. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo