Quick Jump
Introduction | Example | Tutorial | Applications | Comments

Introduction - Compare

I have another great VBA function for you all! This VBA UDF (User Defined Function) will compare two cells in Excel and return whether or not the cells are identical. If they are not identical, the function can optionally show the delta between the values as long as both values are numeric.

Because of its flexibility, this function is perfect for comparing data from two different sources. It also makes a great companion to my Compare Two Columns for Differences macro!


Example - Compare

VBA Function

Copy and paste the code* below if you’re in a hurry. If you want to learn how it works and how to use it, scroll down to the Tutorial section!

Function compare(ByVal Cell1 As Range, ByVal Cell2 As Range, Optional CaseSensitive As Variant, Optional delta As Variant, Optional MatchString As Variant)
'******************************************************************************
'***DEVELOPER:   Ryan Wells (wellsr.com)                                      *
'***DATE:        04/2016                                                      *
'***DESCRIPTION: Compares Cell1 to Cell2 and if identical, returns "-" by     *
'***             default but a different optional match string can be given.  *
'***             If cells are different, the output will either be "FALSE"    *
'***             or will optionally show the delta between the values if      *
'***             numeric.                                                     *
'***INPUT:       Cell1 - First cell to compare.                               *
'***             Cell2 - Cell to compare against Cell1.                       *
'***             CaseSensitive - Optional boolean that if set to TRUE, will   *
'***                             perform a case-sensitive comparison of the   *
'***                             two entered cells. Default is TRUE.          *
'***             delta - Optional boolean that if set to TRUE, will display   *
'***                     the delta between Cell1 and Cell2.                   *
'***             MatchString - Optional string the user can choose to display *
'***                           when Cell1 and Cell2 match. Default is "-"     *
'***OUTPUT:      The output will be "-", a custom string or a delta if the    *
'***             cells match and will be "FALSE" if the cells do not match.   *
'***EXAMPLES:     =compare(A1,B1,FALSE,TRUE,"match")                          *
'***              =compare(A1,B1)                                             *
'******************************************************************************
 
'------------------------------------------------------------------------------
'I. Declare variables
'------------------------------------------------------------------------------
Dim strMatch As String 'string to display if Cell1 and Cell2 match
 
'------------------------------------------------------------------------------
'II. Error checking
'------------------------------------------------------------------------------
'Error 0 - catch all error
On Error GoTo CompareError:
 
'Error 1 - MatchString is invalid
If IsMissing(MatchString) = False Then
    If IsError(CStr(MatchString)) Then
        compare = "Invalid Match String"
        Exit Function
    End If
End If
 
'Error 2 - Cell1 contains more than 1 cell
If IsArray(Cell1) = True Then
    If Cell1.Count <> 1 Then
        compare = "Too many cells in variable Cell1."
        Exit Function
    End If
End If
 
'Error 3 - Cell2 contains more than 1 cell
If IsArray(Cell2) = True Then
    If Cell2.Count <> 1 Then
        compare = "Too many cells in variable Cell2."
        Exit Function
    End If
End If
 
'Error 4 - delta is not a boolean
If IsMissing(delta) = False Then
    If delta <> CBool(True) And delta <> CBool(False) Then
        compare = "Delta flag must be a boolean (TRUE or FALSE)."
        Exit Function
    End If
End If
 
'Error 5 - CaseSensitive is not a boolean
If IsMissing(CaseSensitive) = False Then
    If CaseSensitive <> CBool(True) And CaseSensitive <> CBool(False) Then
        compare = "CaseSensitive flag must be a boolean (TRUE or FALSE)."
        Exit Function
    End If
End If

'------------------------------------------------------------------------------
'III. Initialize Variables
'------------------------------------------------------------------------------
If IsMissing(CaseSensitive) Then
    CaseSensitive = CBool(True)
ElseIf CaseSensitive = False Then
    CaseSensitive = CBool(False)
Else
    CaseSensitive = CBool(True)
End If

If IsMissing(MatchString) Then
    strMatch = "-"
Else
    strMatch = CStr(MatchString)
End If
 
If IsMissing(delta) Then
    delta = CBool(False)
ElseIf delta = False Then
    delta = CBool(False)
Else
    delta = CBool(True)
End If
 
'------------------------------------------------------------------------------
'IV. Check for matches
'------------------------------------------------------------------------------
If Cell1 = Cell2 Then
    compare = strMatch
ElseIf CaseSensitive = False Then
    If UCase(Cell1) = UCase(Cell2) Then
        compare = strMatch
    ElseIf delta = True And IsNumeric(Cell1) And IsNumeric(Cell2) Then
        compare = Cell1 - Cell2
    Else
        compare = CBool(False)
    End If
ElseIf Cell1 <> Cell2 And delta = True Then
    If IsNumeric(Cell1) And IsNumeric(Cell2) Then
        'No case sensitive check because if not numeric, doesn't matter.
        compare = Cell1 - Cell2
    Else
        compare = CBool(False)
    End If
Else
    compare = CBool(False)
End If
Exit Function
 
'------------------------------------------------------------------------------
'V. Final Error Handling
'------------------------------------------------------------------------------
CompareError:
    compare = "Error Encountered: " & Err.Number & ", " & Err.Description
End Function

Tutorial - Compare

Arguments

The flexible compare function, =compare()*, accepts 5 arguments, but only 2 of them are required:

=compare(Cell1, Cell2, [CaseSensitive], [delta], [MatchString])

Here’s a description of what you need to know about each of these arguments:

Argument            Type       Description
Cell1 Cell First cell to compare.
Cell2 Cell Cell to compare against Cell1.
CaseSensitive   Boolean Optional boolean that if set to TRUE, will perform a case-sensitive comparison of the two entered cells. Default is TRUE.
delta Boolean   Optional boolean that if set to TRUE, will display the delta between Cell1 and Cell2. Default is FALSE.
MatchString String Optional string the user can choose to display when Cell1 and Cell2 match. Default is “-“


If there’s one thing I want you to remember, it’s that you really only need 2 arguments - Cell1 and Cell2. If you mess up the other arguments, don’t worry. The UDF is smart enough to tell you when you’ve made an error and how to fix the errors!

See It In Action

Because of all the optional arguments, this compare function can do many incredible things. We’ll go through a couple examples in a little bit, but first, let’s create a hypothetical scenario.

  1. Let’s assume you have some data in a spreadsheet. The data could be anything from names on a wedding list to fuel assemblies in a nuclear reactor.
  2. Now, you come across a second set of data from a different source. A friend or coworker may have made his or her own list or you may have stumbled across data from an online PDF.
  3. You want to make sure your spreadsheet matches the data you found in the other source so you copy and paste them right beside each other.

Right now, your spreadsheet probably looks something like this:

Excel Compare Function Data

Instead of typing a complicated IF statement in Excel to see if the values are identical, you can use this compare macro!

Okay, okay. before we move on to the demonstrations, go ahead and paste the example macro in an Excel VBA module.

Example 1 - Basic Comparison

We’ll start by performing a basic comparison. Type a function similar to the following one into Excel:

=compare(B2,G2)

By default, the =compare() function will look to see if cell B2 matches cell G2. In my example above, the cells do match so the macro UDF returns a value of “-“. You can drag this function down and over, just like any other Excel function to compare large ranges at once. Here’s how it will look:

Excel Compare UDF Case Sensitive

Notice how the values in cells B4 and G4 are identical, except one has a lower case “c” and one has an upper case “C.” What if we don’t care about letter casing? Let’s take a look in Example 2!

Example 2 - Case-Insensitive Comparison

The compare function is capable of ignoring letter case and checking for a match regardless of upper and lower case letters.

To do that, we’ll just add FALSE to the third argument in the function - the CaseSensitive argument. Let’s take a look:

=compare(B2,G2,FALSE)

How does this change our results?

Excel Compare UDF Case Insensitive

Now, the comparison of cells B4 and G4 returns a match!

Example 3 - Deltas and Match Strings

For the final example, let’s go all in.

Do you see how cells D7 and I7 are numeric? What if instead of just showing which cells don’t match, we want to find out what the difference between the numeric cells is.

You can do that with the optional 4th argument - the delta argument. Just set it to TRUE to perform the math for you.

While we’re at it, let’s pretend you still don’t care about case-sensitivity but you don’t like showing “-“ when the cells match. Instead, you want to read the words “match.” We’ll combine all these tricks in this demonstration:

=compare(B2,G2,FALSE,TRUE,"match")

Here’s how the results look:

Excel Compare UDF Case Demo

The value in cell C16 still reports FALSE because the cells being compared are not numeric and can’t be subtracted from one another. However, the value in cell D18 shows “-1” because the difference between cells D7 and I7 is -1!


Application Ideas

Having a great comparison tool at your disposal is great for verifying your work is correct. You can compare and check results to prevent yourself from accidentally publishing an embarrassing typo or miscalculation.

I’ve already suggested many great ways to use this UDF macro. I regularly use it to compare results from two different code executions or to verify fuel assembly names developed under two separate processes are identical.

You can use the =compare() function to compare names, phone numbers, addresses, mathematical expressions and countless other items. If you need to compare it, the compare UDF can do it.


Comments

If you like the compare function, subscribe to my email list, share this article on Facebook, Twitter or your favorite social media platform and follow me on Google+ for more great VBA content.

*Ryan Wells and wellsr.com are not responsible for erroneous calculations or other undesirable results encountered while using the compare function.

Discover how this Nuclear Engineer Mastered Excel VBA

And why you should, too


Your time is valuable. It's time to become a VBA expert.

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.