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
Instead
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)
Thank!
source to share
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
source to share
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
source to share
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
Else
AllValuesIdentical = False
End If
On Error GoTo 0
Set uniqueCheck = Nothing
End Function
source to share