Avoid duplicate values in Collection

Question

I have following values, and I want to add these to a collection. If the values are already in the collection, a message should show "this is already added in your collection".

Dim OrdLines As New Collection

OrdLines.Add (111,this is first item)

OrdLines.Add (222,this is second item)

OrdLines.Add (333,this is third item)

OrdLines.Add (444,this is fourth item)

How do I avoid duplicate values in a collection?

Answer 1

To avoid duplicates without any prompts use this method.

Code

Sub Sample()
    Dim col As New Collection
    Dim itm

    On Error Resume Next
    col.Add 111, Cstr(111)
    col.Add 222, Cstr(222)
    col.Add 111, Cstr(111)
    col.Add 111, Cstr(111)
    col.Add 333, Cstr(333)
    col.Add 111, Cstr(111)
    col.Add 444, Cstr(444)
    col.Add 555, Cstr(555)
    On Error GoTo 0

    For Each itm In col
        Debug.Print itm
    Next
End Sub

ScreenShot

Explanation

A collection is an ordered set of items that you can refer to as a unit. The syntax is

col.Add item, key, before, after

A collection cannot have the same key twice so what we are doing is creating a key using the item that we are adding. This will ensure that we will not get duplicates. The On Error Resume Next is just telling the code to ignore the error we get when we try to add a duplicate and simply move on to the next item to add. The CHR(34) is nothing but " so the above statement can also be written as

col.Add 111, """" & 111 & """"

Suggested Read

The Visual Basic Collection Object

HTH