Quick Jump
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.

Example

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

Tutorial

StrComp Input

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:

Constant Description
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.


StrComp Output

The StrComp Function can result in 3 valid output values.

Result Description
-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.

StrCompDemo Explanation

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.

VBA StrComp Demo Original Lists
Original List of Deluxe Disney Resorts

First, we’ll execute the macro, as-is. Notice the Compare argument is set to vbBinaryCompare. What happens?

VBA StrComp vbBinaryCompare
vbBinaryCompare Results

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)

to

iComp = StrComp(str1, str2, vbTextCompare)

Run your macro again. Now what happens?

VBA StrComp vbTextCompare
vbTextCompare Results

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!

Application Ideas

You can use StrComp anywhere you need to compare strings. Here are a few ideas to get your creative juices flowing.

  1. Compare lists of email addresses
  2. Match a column of employee names with other employee attributes
  3. Since you can now control how you want to handle letter case, create interactive dashboards with user input text prompts
  4. Do the same for interactive VBA UserForms
  5. 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.

Comments

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.


wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


About Ryan Wells


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.