Code Library

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

While tempting, you can't simply use the equality (=) or inequality (<>) operators to compare Variant values in VBA. Here's a safer alternative.

Mike Wolfe

Mar 19, 2022 • 5 min read

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:

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

WARNING: Bad code. Do Not Use. Does not handle Nulls correctly.

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.