Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array question (Excel 2003 SR2)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Array question (Excel 2003 SR2)

    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([img]/forums/images/smilies/cool.gif[/img]
    End Sub

    For large arrays, the function will be very slow.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array question (Excel 2003 SR2)

    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

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Array question (Excel 2003 SR2)

    You could also use something like this, which might be quicker with larger arrays as there's only one loop:
    <pre>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
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Array question (Excel 2003 SR2)

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

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array question (Excel 2003 SR2)

    Rory

    An excellent bit of coding - thanks very much for that

    Nick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •