Collection object Variable Scope?

savbill · #1 (**permalink**) 07-07-08, 23:32

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?

Code:

' 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

Exit Function

fDuplicate_Error:
fDuplicate = True

End Function




' Create a Test collection
' and check for duplicate key
Sub CheckCollection()

    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
    Debug.Print cFruit.Count
   retVal = fDuplicate(cFruit, "Grape")
    'Test it
    ' Count after Testing for Duplicate
    ' Collection is added to even though calling an outside function
    Debug.Print cFruit.Count
    Debug.Print retVal

End Sub

Thanks

MikeTheBike · #2 (**permalink**) 07-08-08, 09:21

Hi

The collection object on my machine does not have a 'Name' property (just an Item and a Key)??

If I ignore this then it errors OK and does not add any item to the collection if the item exists !!

However I wouldn't use this the determine it an item exists, I would loop though the collection and check the exiting item names first ?

MTB

ps. I've never used the Collection object before.

savbill · #3 (**permalink**) 07-08-08, 12:39

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.

Code:

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)
    End If

    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
            Exit For
        End If        ' automatically, remove the first
    Next        ' member on each iteration.
    
Exit Function

ListError:
    fPartExists = True

End Function

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.

Thanks for the ideas.

MikeTheBike · #4 (**permalink**) 07-09-08, 05:18

Hi

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

Code:

Sub TsetCollection()
    Dim cFruit As New Collection
    With cFruit
        .Add "Grape", "Grape1"
        .Add "Grape", "Grape2"
        .Add "Apple", "Apple"
        .Add "Pear", "Pear"
        .Add "Peach", "Peach"      
    End With

    MsgBox fPartExists(cFruit, "GrapeX")
    MsgBox fPartExists(cFruit, "APPLE")
End Sub

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
    Else
        fPartExists = False
    End If
End Function

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!?).

What do you think?

MTB

savbill · #5 (**permalink**) 07-09-08, 22:52

That works great Mike.

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.

Thanks for the ideas

savbill · #6 (**permalink**) 07-16-08, 12:41

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech