views:

36

answers:

2

I don't understand why the Debug.Print n & " - " & objTrans2.DESC line at the bottom of this code is outputting "Description 2" twice. I want it to output "Description 1" and "Description 2".

Is there something wrong with how I am trying to add a custom object (Transaction) through the sampleCollection.Add from within the For loop? The code works wonderfully if I add strings to the collection instead of objects.

Public Function PopCollection()
    Dim sampleCollection As New Collection
    Dim objTrans As New Transaction
    Dim objTrans2 As New Transaction


    '********** SETUP ARRAY FOR LOOP *************
    Dim arrA(0 To 1) As String
    arrA(0) = "Description 1"
    arrA(1) = "Description 2"


    '********** POPULATE COLLECTION *************
    For n = 0 To 1
        objTrans.DESC = arrA(n)
        Call sampleCollection.Add(objTrans)
    Next n


    '********** ITERATE THROUGH COLLECTION *************
    For n = 1 To sampleCollection.Count
        Set objTrans2 = sampleCollection.Item(n)
        Debug.Print n & " - " & objTrans2.DESC
    Next n

End Function

Any help for the newbee is appreciated!

~~~~~~~~~~~~~~~~~~~~~~~

In response to Mitch's response, here is the information in the Transaction class:

Public PTXN As Integer
Public ACCTID As Integer
Public CHECKNUM As String
Public DESC As String
Public STATUS As String
Public TRANSACTIONDATE As String
Public SPLIT_DESC As String
Public SPLIT_AMT As Single
Public SPLIT_CATEGORY As Integer

I only added the property declarations to the VB editor in Excel...so I copy/pasted what was listed there.

A: 

A slightly modified form (without the Transaction class) works as intended for me. I believe there is an error is your Transaction class. Can you post the code for it?

Mitch Wheat
Mitch, I added some more code to my description above. Maybe you can tell me what you used in place of my Transaction class? Maybe I can try to get your code to work on my end.
Eric
+1  A: 

You need to make a new instance of objTrans. What you're doing is setting DESC to Description 1, adding to the collection, then changing DESC to Description 2 (not making a new objTrans instance), and adding that same instance to the collection a second time. Here's how I would do it.

Public Function PopCollection()

    Dim sampleCollection As Collection
    Dim objTrans As Transaction
    Dim arrA As Variant
    Dim n As Long

    arrA = Array("Description 1", "Description 2")
    Set sampleCollection = New Collection

    For n = LBound(arrA) To UBound(arrA)
        Set objTrans = New Transaction
        objTrans.DESC = arrA(n)
        sampleCollection.Add objTrans
    Next n

    For n = 1 To sampleCollection.Count
        Set objTrans = sampleCollection.Item(n)
        Debug.Print n & " - " & objTrans.DESC
    Next n

End Function
Dick Kusleika
Dick, that worked!!! Thanks! I spent hours trying to figure that out. Thanks for the explanation and for the cleaned up code...I like seeing how other people would update my code.
Eric