Introduction | Example | Tutorial | Applications | Comments
Introduction - StrComp Function
I’m a little embarrassed to admit it, but I had never heard of the VBA StrComp function until recently. That’s a shame, really, because it’s so powerful! Hey. If you aren’t learning, you aren’t living. Yeah, you should tweet that.
Use the VBA StrComp function to compare strings. It can perform both case sensitive and case insensitive comparisons. Do you know what that means? No more forcing a UCase for case insensitive string comparisons! This VBA tutorial will walk you through an StrComp example and show you how to interpret the output.
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
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 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.|
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 match.
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 elements 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. If you like what you see, share this article with a friend, submit a comment below and follow me on Google+! Come back often and subscribe to my email list to see more great VBA ideas.
Get these macros delivered right to your spreadsheet for free
Access tutorials and import my macros without ever leaving ExcelShow me - It's free
About Ryan Wells
Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.Follow