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.

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

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

I'll take a free VBA Developer Kit

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


StrComp Output

When comparing strings, 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 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.

StrCompDemo Explanation

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.

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.

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.