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

[ambrosedheffernan]

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[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]3/10/12[/TD]
[TD]ab[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/10/12[/TD]
[TD]ab[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]5/10/12[/TD]
[TD]ab[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]5/10/12[/TD]
[TD]ab[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]


Source 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]4/10/12[/TD]
[TD]abc[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]6/10/12[/TD]
[TD]abc[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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...

   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?

 

[StephenCrump]

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:

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
        '[COLOR=#ff0000][B]But you'll never get here![/B][/COLOR]
        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)

 

[ambrosedheffernan]

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:

 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

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 ?

 

[Norie]

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?

 

[ambrosedheffernan]

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.

 

[StephenCrump]

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?

 

[ambrosedheffernan]

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.

 

[ambrosedheffernan]

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

 

[StephenCrump]

Does this help illustrate:

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