How to check if multiple values ​​are not equal in VBA?

I want to print a list of eight numbers per sheet, but only if they are all unique.

Ideal code would be something like strings

If a <> b <> c Then



If a <> b And a <> c And b <> c Then


Is this possible, given that the values ​​are called from an array using the following code:

Cells(2, 8) = numarr(i)
Cells(2, 9) = numarr(j)
Cells(2, 10) = numarr(k)
Cells(2, 11) = numarr(l)
Cells(3, 8) = numarr(m)
Cells(3, 9) = numarr(n)
Cells(3, 10) = numarr(o)
Cells(3, 11) = numarr(p)




source to share

3 answers

The quick and dirty way to do this is with a dictionary that requires a unique key. Just keep dumping the numbers from your array until you hit the one already in the dictionary. Just turn it into a function and pass the array:

Private Function AllUnique(incoming As Variant) As Boolean

    If Not IsArray(incoming) Then Err.Raise 13

    Dim candidates As Scripting.Dictionary
    Set candidates = New Scripting.Dictionary

    Dim index As Long
    For index = LBound(incoming) To UBound(incoming)
        If candidates.Exists(incoming(index)) Then Exit Function
        candidates.Add incoming(index), index
    Next index

    AllUnique = True

End Function




I'm going to use the direct comparison method:

Public Function AreEqual(ParamArray values() As Variant) As Boolean
    Dim i As Long, j As Long, N As Long
    Dim x As Double
    N = UBound(values) + 1
    For i = 1 To N - 1
        x = values(i - 1)
        For j = i + 1 To N
            If values(j - 1) <> x Then
                AreEqual = False
                Exit Function
            End If
        Next j
    Next i
    AreEqual = True
End Function


Used like

If AreEqual(num1,num2,num3,...) then
End If




As a slight deviation from the answer Collection

above by @ ja72, this function should be able to take any set of simple values ​​of any type and determine if they are identical or not. (An exception is the fourth test line for strings where the key is Collection

not case sensitive.) I use a hashing algorithm to add keys to Collection

to ensure uniqueness.

Option Explicit

Sub Test()
    Debug.Print AllValuesIdentical(14, 14, 14, 14, 14, 14, 14, 14, 14) 'true
    Debug.Print AllValuesIdentical(5, 5, 5, 5, 5, 3, 5, 5)             'false
    Debug.Print AllValuesIdentical("go", "go", "go", "go")             'true
    Debug.Print AllValuesIdentical("go", "go", "go", "GO")             'also true
    Debug.Print AllValuesIdentical(283.14, 283.14, 283.14)             'true
End Sub

Function AllValuesIdentical(ParamArray vals() As Variant) As Boolean
    Dim uniqueCheck As Collection
    Dim val As Variant
    Set uniqueCheck = New Collection
    On Error Resume Next
    For Each val In vals
        uniqueCheck.Add val, CStr(val)
    Next val
    If uniqueCheck.Count = 1 Then
        AllValuesIdentical = True
        AllValuesIdentical = False
    End If
    On Error GoTo 0
    Set uniqueCheck = Nothing
End Function




All Articles