Populate VBA Collection with Custom Objects using Collection.Add

I am trying to add a custom object (Transaction) through the sampleCollection.Add from within a For loop.

The code works 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(objTr开发者_开发百科ans)
    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

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

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. I copy/pasted what was listed there.

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

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?

The simpler version would've been to set you n=0 to SampleCollection.Count as the indexes of an array starts with 0 and your n is starting with 1...

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

Can't populate VBA Collection with Custom Objects using Collection.add

code is outputting "Description 2" twice

In basic simple terms, it is always better to do:

    Dim FooCollection As Collection
    Set FooCollection = New Collection
    
    Dim FooClass As classFoo
    Set FooClass = New classFoo
    FooCollection.Add FooClass

And not:

    Dim FooCollection As New Collection
    etc

As the latter will cause very subtle & not so apparent issues, & may not generate any errors, as I recently discovered.