In our daily life, we need to compare different things. In Excel, we have some built-in 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.
VBA StrComp Function Overview
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.
We will show some examples of VBA StrComp here.
The above data set will be used in this article.
1. Returning All Outputs of StrComp Function 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
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 collaged to get an overall view.
Read More: How to Use VBA Str Function in Excel (4 Examples)
2. Taking Cell Reference in StrComp Function
We will take user input to compare strings.
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
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
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.
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. Inserting User Input to Perform Computation with 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.
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
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.
Here, we get -1 after comparing both strings.
Similar Readings
- How to Unhide Top Rows in Excel (7 Methods)
- Excel Date Picker for Entire Column
- Excel Formula to Generate Random Number (5 examples)
- How to Use VBA Len Function in Excel (4 Examples)
- How to Use VBA ChDir Function in Excel (4 Suitable Examples)
4. Applying the StrComp Function to Compare Text and Binary
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
Step 3:
- Then press F5 to run the code.
- Also, put the inputs mentioned in the below image.
Step 4:
- After pressing OK in the last input field we will get the result.
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
Step 7:
- Run the code by pressing the F5
- Put “John” as the Value_1 and “john” as Value_2.
Step 8:
- Finally, press OK on the second input dialog box.
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. Comparing Values in a Group Using VBA StrComp
We will apply the StrComp function on a certain range using the If function.
Step 1:
- We added 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
Step 4:
- Press F5 to run the code.
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 arguments will need to be input carefully.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
