Simplify & Reduce steps in the IsDistinct function

Question

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:

1. When I tried to keep it terse, the readability was harmed.
2. When I tried to expand the logic for readability, it still makes for some thinking.
3. 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.¹

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

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

---

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!

Answer 1

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

Answer 2

Nested ifs are something I've come to knee-jerk refactor into similar below. Writing functions that state what they're doing reduces the burden to understand. When future-Iven comes along and evaluates the logic I won't be asking myself 'What's this doing?' as I'll already know.

Public Function IsDistinct(leftValue As Variant, rightValue As Variant) As Boolean
    If AreBothValuesNull(leftValue, rightValue) Then
        IsDistinct = False
        Exit Function
    End If

    If IsOnlyOneValueNull(leftValue, rightValue) Then
        IsDistinct = True
        Exit Function
    End If

    IsDistinct = Not (leftValue = rightValue)
End Function

Private Function AreBothValuesNull(ByVal leftValue As Variant, ByVal rightValue As Variant) As Boolean
    AreBothValuesNull = IsNull(leftValue) And IsNull(rightValue)
End Function

Private Function IsOnlyOneValueNull(ByVal leftValue As Variant, ByVal rightValue As Variant) As Boolean
    IsOnlyOneValueNull = (IsNull(leftValue) And Not IsNull(rightValue) _
                        Or Not IsNull(leftValue) And IsNull(rightValue))
End Function

What about the same value but with different types? IsDistinct(1, "1") comes to mind as a test case.

Answer 3

Yet another approach, this time with string coercion. Knowing the type certainly helps simplify the tests, however it does come at a cost of speed. However thought I'd share for those who may be unfamiliar with the approach.

Option Explicit

Public Function IsDistinct(LeftValue As Variant, RightValue As Variant) As Boolean
    IsDistinct = Not ((LeftValue & vbNullString) = (RightValue & vbNullString))
End Function

Public Sub Tests()
    Debug.Print IsDistinct(1, 1)
    Debug.Print IsDistinct(1, 0)
    Debug.Print IsDistinct(Null, 1)
    Debug.Print IsDistinct(0, Null)
    Debug.Print IsDistinct(Null, Null)

    Dim i As Long
    Dim t As Double
    Dim result As Boolean
    t = Timer
    'How long does it take?
    For i = 1 To 100000
        result = IsDistinct(1, 1)
        result = IsDistinct(1, 0)
        result = IsDistinct(Null, 1)
        result = IsDistinct(0, Null)
        result = IsDistinct(Null, Null)
    Next

    Debug.Print "This took " & Timer - t & " seconds"
End Sub

Here are the results of the tests:

False
True
True
True
False
This took 0.2421875 seconds

Answer 4

Personally, when iterating possibilities, I always use SELECT CASE True instead of nested If and Else statements to just have a list of possibilities for readability purposes.

Performance might not be 100% optimal, since this can lead to slightly more comparisons, but I will gladly take that to have more readable code.

Public Function IsDistinct(LeftValue As Variant, RightValue As Variant) As Boolean
    Select Case True
        Case IsNull(LeftValue) And IsNull(RightValue)
            IsDistinct = False
        Case IsNull(LeftValue) Or IsNull(RightValue)
            IsDistinct = True
        Case LeftValue = RightValue
            IsDistinct = False
        Case Else
            IsDistinct = True
    End Select
End Function

Note that because I'm iterating possibilities, I prefer not to do IsDistinct = Not (LeftValue = RightValue) but only assign literals for readability (I'll just have Case Condition Then Literal), but if you don't care, you can of course save two lines.

Answer 5

Developing further based on TinMan's excellent answer and other posters' contributions, I came up with this:

Public Function IsDistinct(LeftValue As Variant, RightValue As Variant) As Boolean
    If LeftValue <> RightValue Then
        IsDistinct = True
    ElseIf IsNull(LeftValue) Xor IsNull(RightValue) Then
        IsDistinct = True
    End If
End Function

Both this version and TinMan's version reduces the number of evaluations to only 1 in the best case and 2 for the worst case. However, this version exploits the fact that the If conditions will treat Null result as a falsy result.¹

Therefore, only equal values and Null result will proceed to the ElseIf condition, which we use the Xor to verify that they aren't both Null. If that doesn't match, then we know that they are considered the same value.

This is more terse but I think the readability hurts a bit because of the LeftValue <> RightValue potentially returning a Null result which does not make for most intuitive thinking through the logical procession. But I would say that it's more common to compare one value against another value than it is to have Null against any other value or having both Null so that is also a good optimization, I think. Need to test whether this is in fact an improvement in speed, though.

---

1) Aside: I found out that Select Case True does not like Null results; we get an Invalid Use of Null error with that structure. Thus, it must be a If/ElseIf which tolerates Null result. However, It's very important to remember that with a Null result, it will never ever enter any If or ElseIf branch, and therefore always fall into the Else or through.