VBA pass collection to collection by value, not by reference.
Results 1 to 9 of 9

Thread: VBA pass collection to collection by value, not by reference.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2016
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA pass collection to collection by value, not by reference.

    I am learning VBA and trying to master collections. I am using collections because the ability to remove items seems likes it gives an advantage over arrays when using 2D.


    I am looking to compare source 1 to source 2. I load both sets into collections.

    Sample Data

    source 1
    A B c
    3/10/12 ab 10
    3/10/12 ab 10
    5/10/12 ab 20
    5/10/12 ab 20


    Source 2
    a b c
    4/10/12 abc 20
    6/10/12 abc 40


    then I create a new collection to hold matched data, e.g. on the 3rd there is 20 in total, this matches the 4th in source 2

    Anyway this is the collection structure I was looking for
    '''''''''''''''''''
    ' matches
    ' xerotrans - collection of ranges
    ' banktrans - range
    xerotrans - collection of ranges
    ' banktrans - range
    ' etc
    '''''''''''''''''''


    this is my code to loop over...

    Code:
       Dim XerodateSortCol As Integer
       XerodateSortCol = 1
       
       Dim BankdateSortCol As Integer
       BankdateSortCol = 1
        
       'all the dates should be in order so we can create a new collection to store the values with the same date and then match to source 2
       
       Dim trans As Variant
       Dim curDate As Variant
       curDate = 0
       Dim DateCount As Integer
       DateCount = 0
       
       Dim Banki As Variant
       
       
       Dim ThisDateCollection As New Collection
       Dim ThisDateXeroTrans As New Collection
       
       
       Dim matches As New Collection
       '''''''''''''''''''
       '    matches
       '        key = date
       '           xerotrans - collection of ranges
       '           banktrans - range
       '
       '''''''''''''''''''
       For Each trans In XeroCreditCards
        
         'date is not set yet
         'set up a new date in matches......we are duplicating this but anyway, also located in when current date does not match previous
         'date
         
        If curDate = 0 Then
            curDate = trans.Columns(XerodateSortCol)
        End If
        
        'check here if the date is different from the last date we have, if yes, finish off the day and find the match.
        'Don't do if curDate = 0
        
        If curDate = trans.Columns(XerodateSortCol) Then
            
            ThisDateXeroTrans.Add trans
            DateCount = DateCount + trans.Columns(valueLeftCol)
            
        ElseIf curDate <> trans.Columns(XerodateSortCol) Then
            'add the transaction to the xero transactions
            ThisDateCollection.Add ThisDateXeroTrans, "XeroTransactions"
            Debug.Print ThisDateCollection("XeroTransactions").Count
            Debug.Print "new date"
            For Each Banki In BankCreditCards
                If Banki.Columns(valueRightCol) = DateCount Then
                    'we have a match, add to CCmatches
                    ThisDateCollection.Add Banki, "BankTransaction"
                End If
            Next Banki
            
            
            matches.Add ThisDateCollection
        
        
            'reset so we can do the next day
            DateCount = 0
            curDate = 0
            While ThisDateCollection.Count <> 0
                ThisDateCollection.Remove (ThisDateCollection.Count)
            Wend
        End If
        
       
       Next trans
       
       
       'we should have matched all here, output the transactions
       
       Debug.Print "matches length = " & matches(1).Count
    The matches.count is correct, but as I delete the data from the collection used to add the 2nd dimension to matches, that array is empty.

    How to add this collection to matches by value rather than by ref?

  2. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,457
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA pass collection to collection by value, not by reference.

    Welcome to the Forum!

    I can't immediately follow what your code is doing (and I assume all your variables are defined somewhere?), but one thing that does jump out is this structure:

    Code:
    curDate = 0
    
    For Each trans In XeroCreditCards
        
        If curDate = 0 Then curDate = trans.Columns(XerodateSortCol)
            
        If curDate = trans.Columns(XerodateSortCol) Then
            'Do stuff
                    
        Else
            'Do other stuff
            'But you'll never get here!
            curDate = 0
        End If
    
    Next trans
    Instead, don't you need:

    1. To compare curDate and trans.Columns(XerodateSortCol)

    2. if they're different, do "other stuff" and then set curDate = trans.Columns(XerodateSortCol)

  3. #3
    New Member
    Join Date
    Nov 2016
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA pass collection to collection by value, not by reference.

    Thanks.

    Sorry maybe I didn't explain it was well as I thought.

    The loop is probably not well designed, but not really the issue at the moment. It is specifically adding collections to collections during the loop. I can add a reference to the 2D collection, but as I need a collection for each date (data will vary), this is where i'm stuck.

    The specific code causing issues hopefully it is a bit clearer:

    Code:
     ElseIf curDate <> trans.Columns(1) Then
            'the date of the current iteration item is not the same as on the iteration --   add the collection we built on the previous iterations to the parent matches.
    
    
            'add the 3rd D collection to the 2d Collection
            ThisDateCollection.Add ThisDateXeroTrans, "XeroTransactions"
            
            
    
            'add the 2d collection to the parent collection.
            matches.Add ThisDateCollection
    
    
            'delete the data in ThisDateCollection so we can populate again
            
             'code to delete ThisDateCollection data
    
        
     End If

    Basically in a nutshell the above works, at the end I have a matches collection populated with collections.

    The issue is the 2D collection "ThisDateCollection" is blank because after assigning to the parent collection, i delete the data in "ThisDateCollection" if that makes sense?

    The reason I delete it is to start with a fresh blank collection for a new date. Now what I was expecting to happen is that by using
    Code:
    matches.add  ThisDateCollection
    is that the matches would be assigned by value rather than using a reference to ThisDateCollection. I.e. after assigning I could do whatever I wanted to the original collection without effecting the data assigned to matches.

    What I was hoping to have at the end of it is a collection of collections (the 2Dimension is each date found in source 1).

    Or alternatively, is there a way to create a collection using a variable name, e.g. Dim variablevalue as collection ?

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,122
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA pass collection to collection by value, not by reference.

    Is ThisDateCollection supposed to contain the results of the comparison between the other 2 collections?

    If it is, why/what are you deleting/removing from that collection?

    Shouldn't you just be adding to it?
    If posting code please use code tags.

  5. #5
    New Member
    Join Date
    Nov 2016
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA pass collection to collection by value, not by reference.

    You see i need each date to be a collection element.

    So you have matches as the 1st Dimension

    then collection for 1/10/12
    then collection for 2/10/12

    etc for each date found.

  6. #6
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,457
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA pass collection to collection by value, not by reference.

    I know this is meant to be a coding exercise for you in using collections, but I can't make a lot of sense of what you're doing, e.g.

    - Why you have made ThisDateCollection a collection, when it will always have two elements, i.e. a "Xero Transactions" and a "BankTransaction" element?

    - Why you add the entire accumulating ThisDateXeroTrans collection into the ThisDateCollection collection?

    Can you perhaps back up a step and tell us what it is that you're doing? For the sample Source1 and Source2 data, what is the desired result?

  7. #7
    New Member
    Join Date
    Nov 2016
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA pass collection to collection by value, not by reference.

    Why you have made ThisDateCollection a collection, when it will always have two elements, i.e. a "Xero Transactions" and a "BankTransaction" element?

    I thought it would be a good way to group the 2 together, i.e. matches(1) - collection of 2 items under one key in the parent collection matches. The first is a collection of the transactions on the 1st date found in source 1, the second is a range object with the matched transaction from source 2.

    Just to explain, the matching process is the simple matching of the total amount for that date in source 1 to an amount in source 2. I will need further logic to match unmatched items, so i need someway to store. The data is taken from a larger dataset containing irrelevant transactions to this process.

    I am open to suggestion, I thought to use collections as you can remove items easily compared to arrays. And obviously I want to understand collections.


    Why you add the entire accumulating ThisDateXeroTrans collection into the ThisDateCollection collection?

    There will be further code to delete the contents of ThisDateXeroTrans as well. Same situation as ThisDateCollection,on each iteration of the loop, I need to add a new ThisDateCollection for the date and then add a new ThisDateXeroTrans containing the transactions on that date from source 1.

    As mentioned the issue is regarding collection.add method, it adds by ByRef instead of ByVal. I might try using a function to accept byVal and return the collection.

  8. #8
    New Member
    Join Date
    Nov 2016
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA pass collection to collection by value, not by reference.

    actually this seems to work


    Public Function add_copy_of_col(ByVal col As Collection)


    Set add_copy_of_col = col


    End Function



    parent.add add_copy_of_col(child)

    set child = nothing

  9. #9
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,457
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA pass collection to collection by value, not by reference.

    Does this help illustrate:

    Code:
    Sub Test()
    
        Dim col1 As Collection, col2 As Collection
        
        Set col1 = New Collection
        Set col2 = New Collection
        
        col1.Add "a"
        col1.Add "b"
        col2.Add col1
        Set col1 = Nothing
        
        MsgBox col2(1)(2) ' = "b" !
    
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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