CODEWITHSUNDEEP
vba
Sean
Sean

Reputation: 31

How to check that multiple values are not equal in VBA?

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

An ideal code would be something along the lines of

If a <> b <> c Then

Rather than

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

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

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)

Thanks!

Upvotes: 3

Views: 8878

Answers (3)

PeterT
PeterT

Reputation: 8557

As a slight variation of the Collection answer given above by @ja72, this function should be able to take any set of simple values of any type and determine if they're all identical or not. (The exception to this being the fourth test line for Strings, in which the Collection key is not case-sensitive.) I'm taking advantage of the hashing algorithm for adding keys to a Collection to ensure unique-ness.

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
    Else
        AllValuesIdentical = False
    End If
    On Error GoTo 0
    Set uniqueCheck = Nothing
End Function

Upvotes: 0

John Alexiou
John Alexiou

Reputation: 29244

I am going to throw in 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

To be used as

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

Upvotes: 0

Comintern
Comintern

Reputation: 22195

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

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

Upvotes: 1

Related Questions