Introduction | Example | Tutorial | Applications
Introduction - StrComp Function
You should use the StrComp VBA function to compare strings in Excel. StrComp VBA can perform both case sensitive and case insensitive string comparisons. This tutorial will walk you through an StrComp example and teach you how to use it to compare two strings in VBA.
StrComp Function Demo
Option Explicit Sub strCompDemo() Dim iComp As Integer, i As Integer Dim str1 As String, str2 As String For i = 1 To 8 str1 = Range("A" & i) str2 = Range("B" & i) iComp = StrComp(str1, str2, vbBinaryCompare) Select Case iComp Case 0 Range("C" & i) = "Match" Case Else Range("C" & i) = "Not a match" End Select Next i End Sub
Write macros like this in just 7 days
Over 10,000 people have finished our free Write Better Macros in 7 Days guided challenge. It's powerful, effective and comes with a VBA Developer's Kit full of shortcuts, tips and pre-written macros to make writing VBA easier.
The VBA StrComp Function returns an Integer and accepts three arguments:
StrComp(String1, String2, Compare)
String1 and String2 are required and must be string data types, or at least data types that evaluate to string expressions (such as a string Variant).
Now, for a closer look at the Compare argument, which is optional. This is where things get interesting. Here’s an incomplete list of the the Compare constants:
|vbBinaryCompare||Compares the binary equivalent of your strings. Use this to perform a case sensitive comparison. With this method, “John Smith” does not equal “john smith”|
|vbTextCompare||Compares string text, regardless of letter case. Use this to ignore the case and perform a case insensitive comparison. With this method, “John Smith” equals “john smith”|
|vbUseCompareOption||Default option - typically Binary. To change the default, you must change the Option Compare statement. For example, add the line Option Compare Text to the top of your module to default to the Text comparison instead of the binary comparison.|
When comparing strings, the StrComp Function can result in 3 valid output values.
|-1||String1 is less than String2|
|0||String1 equals String2|
|1||String 1 is greater than String2|
String comparison is bizarre - I’m not going to go into details here. If you’re like me, you’re rarely interested in whether one string is “greater” than another. You can certainly use it to determine which string is greater, but I, and I suspect you will, find it more useful just to see if the strings are equal.
To check whether the strings are equal, you only need to see if the resulting integer is a 0. If it’s not, the strings are not equal! You can do this check with an
If Then statement or a
Select Case statement, like I did in the StrCompDemo example.
Let’s apply my StrComp macro example to two lists of Disney Deluxe Resorts. The macro will compare the strings in the two lists and report back whether or not they are equal.
Original List of Deluxe Disney Resorts
First, we’ll execute the macro, as-is. Notice the Compare argument is set to
vbBinaryCompare. What happens?
The binary comparison reports that everything is a match except for Row 4. See how cell A4 capitalizes the first letters in “Contemporary Resort,” while cell B4 keeps the text lowercase. If you’re using the binary comparison option, vbBinaryCompare, the strings must be completely identical to result in a match. This includes capital and lower case letters!
What if you set the Compare argument to
vbTextCompare. Change the line
iComp = StrComp(str1, str2, vbBinaryCompare)
iComp = StrComp(str1, str2, vbTextCompare)
Run your macro again. Now what happens?
Each cell in Column C now says “Match” because the text comparison doesn’t care about letter case! The string “abc” is the same as “ABC” when you use vbTextCompare.
This is useful because it means you no longer have to worry about whether you (or your user) capitalized letters in your input. Before I discovered the VBA StrComp Function, I would force all my strings to uppercase,
UCase(String1), while comparing strings. I no longer have to do that, and neither do you!
You can use StrComp anywhere you need to compare strings. Here are a few ideas to get your creative juices flowing.
- Compare lists of email addresses
- Match a column of employee names with other employee attributes
- Since you can now control how you want to handle letter case, create interactive dashboards with user input text prompts
- Do the same for interactive VBA UserForms
- Why not modify my Compare Columns example and replace the UCase equations with the equivalent StrComp equations?
The ability to handle strings without worrying about uppercase and lowercase letters is an important skill - a skill you just strengthened by completing my StrComp VBA Tutorial.
I hope you find the StrComp function as exciting as I do. I compare strings using VBA more than almost anything else I do in Excel. The StrComp VBA function makes it a breeze!
When you’re ready to take your VBA to the next level, subscribe using the form below.