Array question (Excel 2003 SR2)

Topic

New Reply

I have an array of x elements, & I need to establish whether or not all elements are different from each other.

What is the most efficient way of doing this using VBA ?

Usual thanks for your expert help

Nick

Reply | Quote

Replies

For not too large arrays, you can use this function:

Function AllDifferent(arr) As Boolean
Dim i As Integer
Dim j As Integer
For i = LBound(arr) To UBound(arr) – 1
For j = i + 1 To UBound(arr)
If arr(i) = arr(j) Then
AllDifferent = False
Exit Function
End If
Next j
Next i
AllDifferent = True
End Function

Here is an example of its use:

Sub Test()
Dim a, b
a = Array(1, 3, 4, 6)
b = Array(“a”, “c”, “a”, “d”)
Debug.Print AllDifferent(a)
Debug.Print AllDifferent(
End Sub

For large arrays, the function will be very slow.

Reply | Quote

Hans

Thanks for that quick answer. I’m not expecting the arrays to be too large – probably 20/25 elements, so that should work fine.

Nick

Reply | Quote

You could also use something like this, which might be quicker with larger arrays as there’s only one loop:

Function AllDifferent(varData) As Boolean
   Dim lngindex As Long, coldata As New Collection
   AllDifferent = True
   On Error Resume Next
   For lngindex = LBound(varData) To UBound(varData)
      coldata.Add varData(lngindex), CStr(varData(lngindex))
      If Err.Number  0 Then
         AllDifferent = False
         Exit Function
      End If
   Next lngindex
End Function

Reply | Quote

Nice one – that should be more efficient for large arrays.

Reply | Quote

Rory

An excellent bit of coding – thanks very much for that

Nick

Reply | Quote