CODEWITHSUNDEEP
excelclassvbaobjectcollections
Steven
Steven

Reputation: 781

VBA Object data being over written in collection

In the below loop I add a Class Object to a collection within another class object, which itself is in a collection.

    Dim opportunity As New ClmOpportunity

    opportunity.name = name

    owners.item(overallOwner).addOpportunity opportunity

    MsgBox opportunity.name

Next i

MsgBox owners("John Smith").opportunities(1).name

The first message box presents the correct opportunity name, but the second message box gets set to the last opportunity that was added, even though John Smith is first in the collection.

So if I have two owners, John Smith with opportunity 1 and Mary Lou with opportunity 2 the out put from the second message box will be opportunity 2 for both records.

But the first message will be opportunity 1 and 2 as expected.

This is the code from the Owner class module:

Public name As Variant
Public opportunities As New collection

Public Function addOpportunity(opp As ClmOpportunity)

    Dim OppID As String
    OppID = opportunities.count + 1

    opp.ID = OppID
    opportunities.Add opp, OppID

End Function

Upvotes: 1

Views: 1796

Answers (3)

xll
xll

Reputation: 3109

You are always adding the same ClmOpportunity object, because Dim as New instantiates new object only once even if used inside a cycle.

Correct way of creating new object inside of cycle is:

For ...

  Dim opportunity As ClmOpportunity
  Set opportunity = New ClmOpportunity

Next

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166196

You're definitely not adding multiple copies of the "same" opportunity object? Hard to tell without the full loop. If you check all the items in the collection do they all have the same name?

This code shows the same behavior if you comment out the marked line...

Sub Tester()

    Dim col As New Collection         
    Dim o As clsTest     'has just a "name" property


    Set o = New clsTest
    o.name = "obj1"
    col.Add o, "key1"

    'compare debug output with the next line
    '  commented/uncommented
    Set o = New clsTest
    o.name = "obj2"
    col.Add o, "key2"

    Debug.Print col(1).name, col(2).name

End Sub

Upvotes: 2

Steven
Steven

Reputation: 781

So the solution to this was to instantiate the opportunity outside of the loop then reinitialise each time like this:

Set opportunity = New ClmOpportunity

Upvotes: 3

Related Questions