How to Check If Two Variant Values Are Equal (or Not) in VBA

Directly comparing two Variant variables using an equality (=) or inequality (<>) operator is not a reliable way to check whether they contain the same value.

For background, check out my article on the Strange Behavior of Null in VBA:

The Strange Behavior of Null

When are two identical values not equal? When they’re both Null! If you are a Microsoft Access developer, you need to understand how (and why) this works.

No Longer SetMike Wolfe

Here's the tl;dr version:

• Null = Null evaluates to Null, not True
• Null = 1 evaluates to Null, not False
• If Null Then evaluates to False

Null behaves this way because it doesn't represent any value at all.  Rather, it is a way of saying "I don't know" what this value actually is.  

Null can also be used to represent the absence of a value.  

Identifying Changes to Optional Fields

In Microsoft Access, we spend most of our time working with databases.  And that means we spend a lot of time working with Nulls.  

A common scenario is checking to see whether the value of a particular field has changed in the Before_Update event of a form.  There are many reasons you may want to do this:

• Validate that the new value is appropriate
• Log changes to certain fields in an auditing table
• Warn the user that they are about to change a field that shouldn't normally be changed

In fact, Access makes this even easier by including the .OldValue property on bound controls.

You may be tempted to use the following code to perform such a check:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.tbNotes.OldValue <> Me.tbNotes.Value Then
        'The value has changed
    Else
        'The value is the same
    End If
End Sub

The problem with this approach is that it fails to account for the strange behavior of Null I mentioned at the beginning of the article.  If the old value is Null and the new value is not Null, then this code will execute the "Else" portion of the If...Then...Else statement.  

Remember:

• Anything compared to Null results in Null
• Null evaluates to False

A Safer Approach

To handle this situation, I wrote a simple function to detect changes between two possibly Null values.  

• If both values are Null it returns False (no change detected)
• If one or the other value is Null it returns True (change detected)
• If neither value is Null it returns False only if they are equal (no change detected) otherwise it returns True (change detected)

Private Function VarValChanged(OldVal As Variant, NewVal As Variant) As Boolean
    If IsNull(OldVal) And IsNull(NewVal) Then
        VarValChanged = False
    ElseIf IsNull(OldVal) Xor IsNull(NewVal) Then
        VarValChanged = True
    Else
        VarValChanged = OldVal <> NewVal
    End If
End Function

Be sure to check out the documentation for the eXclusive OR comparison operator (Xor) if you are not familiar with it.

Here is how we would rewrite the above Form_BeforeUpdate() code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If VarValChanged(Me.tbNotes.OldValue, Me.tbNotes.Value) Then
        'The value has changed
    Else
        'The value is the same
    End If
End Sub

Working Example

To demonstrate both the problem and solution, I created a simple form with two bound fields.  The Notes field is optional.  Here's the form's BeforeUpdate code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim IsChanged_DirectComparison As Boolean
    If (Me.tbNotes.OldValue <> Me.tbNotes.Value) Then
        IsChanged_DirectComparison = True
    Else
        IsChanged_DirectComparison = False
    End If
    
    Dim IsChanged_VarValChanged As Boolean
    IsChanged_VarValChanged = VarValChanged(Me.tbNotes.OldValue, Me.tbNotes.Value)
    
    Dim OldVal As String: OldVal = Nz(Me.tbNotes.OldValue, "{NULL}")
    Dim NewVal As String: NewVal = Nz(Me.tbNotes.Value, "{NULL}")
    
    MsgBox "Old Value: " & OldVal & vbNewLine & _
           "New Value: " & NewVal & vbNewLine & _
           vbNewLine & vbNewLine & _
           "Direct Comparison: " & _
              IIf(IsChanged_DirectComparison, "CHANGED", "not changed") & _
           vbNewLine & vbNewLine & _
           "VarValChanged: " & _
              IIf(IsChanged_VarValChanged, "CHANGED", "not changed")

End Sub

To test this code, I ran four different scenarios.  For each one, I show the before and after values of the Notes field.  I then show whether using direct comparison (<>) WORKS or FAILS.  I also show whether using the VarValChanged function WORKS or FAILS.

1. Notes Field: NULL --> NULL

In the first situation, the Notes field remained null.  I changed the account number to trigger the before update event.  

• Direct Comparison: WORKS
• VarValChanged: WORKS

. Notes Field: NULL --> "test"

In this situation, the Notes field changed from Null to the string "test."

• Direct Comparison: FAILS
• VarValChanged: WORKS

. Notes Field: "test" --> "test"

This time, the Notes field remained set to the string "test."  Once again, I changed the account number to trigger the before update event.

• Direct Comparison: WORKS
• VarValChanged: WORKS

. Notes Field: "test" --> NULL

For this test, I changed the Notes field from the string "test" to Null.

• Direct Comparison: FAILS
• VarValChanged: WORKS

As you can see, any time the value changed to or from Null, the direct comparison failed to work as expected.

Demonstration

Here's a demonstration of the above four scenarios in animated gif form:

Referenced articles

The Strange Behavior of Null

When are two identical values not equal? When they’re both Null! If you are a Microsoft Access developer, you need to understand how (and why) this works.

No Longer SetMike Wolfe

External references

TextBox.OldValue property (Access)

Office VBA reference topic

Microsoft Docso365devx

Xor operator

Office VBA reference topic

Microsoft Docso365devx