I would like to use a collection object to check for duplicate entries. This works fine to find an existing item in the collection, but when a value is not found in the collection the collection "Add" method adds the item to the collection which then alters the original collection in the calling sub increasing the number of items in the original collection. I would like the function to just return 'True' or 'False' without adding anything to the collection.
Does anyone know of a way to do this without having the item added and returned to the original collection object?
' Function tests for duplicate key in collection
' should not Modify the original collection, but it does???
Function fDuplicate(ByVal oCol As Collection, strVal)
fDuplicate = False
Set myClass = oCol
On Error GoTo fDuplicate_Error
myClass.Add Item:=TheName, Key:=strVal
fDuplicate = True
' Create a Test collection
' and check for duplicate key
Dim cFruit As Collection
Dim vItm As Variant
Set cFruit = New Collection
'fill the collection
cFruit.Add "Mango", "Mango"
cFruit.Add "Apple", "Apple"
cFruit.Add "Peach", "Peach"
cFruit.Add "Kiwi", "Kiwi"
cFruit.Add "Lime", "Lime"
' Count Prior to testing for Duplicate
retVal = fDuplicate(cFruit, "Grape")
' Count after Testing for Duplicate
' Collection is added to even though calling an outside function
Hey Mike, The Collection object is really handy for checking matches. The parameters are "Item" and "Key". ("TheName" was used as a Null place holder for the Item as I only require storing the Key values for this process) If you try to add a duplicate key with the Add method it produces an error. This makes it easy to check large lists in a collection for duplicates.
Here's what I came up with. For checking values against a previously created collection I used the same value for the 'Item' and the 'Key' when testing for a duplicate value. If this doesn't produce an Error (Duplicate False), the value is added and the following loop checks the collection values to find the matching just added item and removes it. From testing I found the item was usually added to the end of the collection so using a highest to lowest (Step -1) loop usually catches the addition on the first itteration. Using a reverse loop in this case improves the speed.
Function fPartExists(ByVal oCol As Collection, ListVal)
Dim i As Integer ' Variants to hold information.
Dim TheName As String
fPartExists = False
If ListVal <> "" Then
On Error GoTo ListError
TheName = ListVal
' Add the named object to the collection.
oCol.Add Item:=TheName, Key:=CStr(ListVal)
For i = oCol.Count To 1 Step -1 ' Remove name from the collection.
If oCol.Item(i) = TheName Then
oCol.Remove i ' Since collections are reindexed
End If ' automatically, remove the first
Next ' member on each iteration.
fPartExists = True
I thought the "ByVal" condition would limit the scope of the variable to the function? It doesn't. So this seems to be the best option.
I have had a look at the Collection object and I think using the 'Key' property may be a better bet for this purpose as this is the property that causes the error (it will not allow the same key for more that one 'Item' in a given collection - the Item can be any, repeated, object).
So perhaps this would be better
Dim cFruit As New Collection
.Add "Grape", "Grape1"
.Add "Grape", "Grape2"
.Add "Apple", "Apple"
.Add "Pear", "Pear"
.Add "Peach", "Peach"
MsgBox fPartExists(cFruit, "GrapeX")
MsgBox fPartExists(cFruit, "APPLE")
Function fPartExists(ByVal oCol As Collection, ByVal ListKeyVal As String)
Dim Dummy As String
On Error Resume Next
Dummy = oCol.Item(ListKeyVal)
If Err.Number = 0 Then
fPartExists = True
fPartExists = False
As note the position of the items may change as the oject are added/removed, but the Key will always be the same for a given Item (I think!?).
I hadn't considered using the Collection item property to check the keys. This way, since an item / key is not being Added when checking existing keys, it does not alter the collection. Nice! Also there still is no requirement to specify an item for each key and neither is the item needed to test for the existing key.
I'll use this, its one less hoop to jump through in checking the collection. Funny I havn't seen an example of checking a collection this way, until now. I guess using collections like this is not a well known tactic. I'm using this to build and check collections from 4-5 files each with more than 1000 records. It is very fast since building and checking the collections does not require multiple loops to check for duplication.
I found this site with an Example of checking matches between two lists of data Insane Excel
The Example Workbook demonstrates the time used for different methods of checking for Matches. Fast data matching
The Collection Method example is very similar to what MikeTheBike came up with.