Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    533

    Unanswered: Collection object Variable Scope?

    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
    ~

    Bill

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    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.
    ~

    Bill

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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
    Last edited by MikeTheBike; 07-09-08 at 06:50.

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    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
    ~

    Bill

  6. #6
    Join Date
    Feb 2004
    Posts
    533
    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.
    ~

    Bill

Posting Permissions

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