Simplify & Reduce steps in the IsDistinct function Because I often have to deal with two Variants that may or may not be Null, we need a null-safe equality test, so I came up with IsDistinct which works but I have some issues with the code:


When I tried to keep it terse, the readability was harmed.
When I tried to expand the logic for readability, it still makes for some thinking. 
I looked for potentials to short-circuit or otherwise result the number of steps to arrive at a result. In this case, it all takes 2 evaluations, unless both sides are non-null, in which case we have 3 evaluations.1 


Can we do better? 

Public Function IsDistinct(LeftValue As Variant, RightValue As Variant) As Boolean
    If IsNull(LeftValue) Then
        If IsNull(RightValue) Then
            IsDistinct = False
        Else
            IsDistinct = True
        End If
    Else
        If IsNull(RightValue) Then
            IsDistinct = True
        Else
            IsDistinct = Not (LeftValue = RightValue)
        End If
    End If
End Function


Inputs &amp; Expected Outputs

LeftValue RightValue Result
 1         1          False
 1         0          True
 Null      1          True
 0         Null       True
 Null      Null       False
 ""        ""         False
 ""        Null       True


Note that it doesn't have to be just 0 and 1; it could be text, dates, or numbers. It's more important that when either inputs are Null, it should automatically be True since Null will always be "distinct" from any non-Null values. However, when both inputs are Null, then it's always False because we are considering them "equal" in this situation.2

The special case of an empty string and a Null is arguably problematic. I've swung both ways; sometimes I want empty string to be considered "equal" to a Null, sometimes I don't. In the IsDistinct as defined, they are not considered equal.3



1) In one of iterations, I considered starting with Result = (LeftValue = RightValue) and doing additional evaluation if the Result was Null, signaling that either or both inputs were Null. But IIRC, I found that it made for more steps since I had to evaluate each inputs with IsNull to determine whether both were Null and thus not distinct. 

2) As a matter of fact, when I look at the first 3 lines:

If IsNull(LeftValue) Then
    If IsNull(RightValue) Then
        IsDistinct = False


My instinct is to go "wait, that's not right.", thinking about it and realize it IS correct. The fact that I stumble on that even more than once tells me that it's quite hard to read. Boo. 

3) Thanks to @Ryan Wildry for pointing this blind spot out!
You have no idea how excited I am about this.  Years after learning about the Xor operator and I finally get to use it!!  

VBA - Logical Operators: Xor Operator


  Called Logical Exclusion. It is the combination of NOT and OR Operator. If one, and only one, of the expressions evaluates to be True, the result is True.


The Xor seems like a great fit because both the parameters need to be tested for nulls and the values can not equal one another.

My Version

Public Function NotEquals(LeftValue As Variant, RightValue As Variant) As Boolean
    If IsNull(LeftValue) Xor IsNull(RightValue) Then
        NotEquals = True
    ElseIf LeftValue &lt;&gt; RightValue Then
        NotEquals = True
    End If
End Function


Results



Crude Tests

Option Explicit
Public Const Repetitions = 10000000

Public Function IsDistinct(LeftValue As Variant, RightValue As Variant) As Boolean
    If IsNull(LeftValue) Then
        If IsNull(RightValue) Then
            IsDistinct = False
        Else
            IsDistinct = True
        End If
    Else
        If IsNull(RightValue) Then
            IsDistinct = True
        Else
            IsDistinct = Not (LeftValue = RightValue)
        End If
    End If
End Function

Public Function NotEquals(LeftValue As Variant, RightValue As Variant) As Boolean
    If IsNull(LeftValue) Xor IsNull(RightValue) Then
        NotEquals = True
    ElseIf LeftValue &lt;&gt; RightValue Then
        NotEquals = True
    End If
End Function

Sub CompareResults()
    Dim SumOfTime As Double
    Debug.Print "Number of Repetitions: "; FormatNumber(Repetitions, 0)

    Debug.Print
    Debug.Print "IsDistinct Test"
    Debug.Print "Left"; Tab(8); "Right"; Tab(16); "Time"; Tab(24); "Result"
    SumOfTime = TestIsDistinct(1, 1)
    SumOfTime = SumOfTime + TestIsDistinct(1, 0)
    SumOfTime = SumOfTime + TestIsDistinct(Null, 1)
    SumOfTime = SumOfTime + TestIsDistinct(0, Null)
    SumOfTime = SumOfTime + TestIsDistinct(Null, Null)
    Debug.Print "Total Time for All IsDistinct Test: "; SumOfTime

    Debug.Print
    Debug.Print "NotEquals Test"
    Debug.Print "Left"; Tab(8); "Right"; Tab(16); "Time"; Tab(24); "Result"
    SumOfTime = TestNotEquals(1, 1)
    SumOfTime = SumOfTime + TestNotEquals(1, 0)
    SumOfTime = SumOfTime + TestNotEquals(Null, 1)
    SumOfTime = SumOfTime + TestNotEquals(0, Null)
    SumOfTime = SumOfTime + TestNotEquals(Null, Null)
    Debug.Print "Total Time for All NotEquals Test: "; SumOfTime

End Sub

Function TestIsDistinct(LeftValue As Variant, RightValue As Variant) As Double
    Dim t As Double
    t = Timer
    Dim Result As Boolean
    Dim n As Long
    For n = 1 To Repetitions
        Result = IsDistinct(LeftValue, RightValue)
    Next

    Dim SumOfTime As Double
    SumOfTime = Round(Timer - t, 4)

    Debug.Print LeftValue; Tab(8); RightValue; Tab(16); SumOfTime; Tab(24); Result

    TestIsDistinct = SumOfTime
End Function

Function TestNotEquals(LeftValue As Variant, RightValue As Variant) As Double
    Dim t As Double
    t = Timer
    Dim Result As Boolean
    Dim n As Long
    For n = 1 To Repetitions
        Result = NotEquals(LeftValue, RightValue)
    Next

    Dim SumOfTime As Double
    SumOfTime = Round(Timer - t, 4)

    Debug.Print LeftValue; Tab(8); RightValue; Tab(16); SumOfTime; Tab(24); Result

    TestNotEquals = SumOfTime
End Function