Populate VBA Collection with Custom Objects using Collection.Add

Question

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

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.

Dick Kusleika · Accepted Answer · 2010-07-11 13:12:43Z

8

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

answered Jul 11, 2010 at 13:12

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eric Over a year ago

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.

Mitch Wheat · Accepted Answer · 2010-07-11 03:01:46Z

1

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?

answered Jul 11, 2010 at 3:01

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

1 Comment

Eric Over a year ago

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.

Joe · Accepted Answer · 2019-10-21 18:37:19Z

0

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

answered Oct 21, 2019 at 18:37

Joe

1

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 18, 2020 at 23:43

ReturnVoid

1,1261 gold badge11 silver badges19 bronze badges

Collectives™ on Stack Overflow

Populate VBA Collection with Custom Objects using Collection.Add

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related