Question:
Collection / memory Problems--
I consider myself a fairly good excel programmer, but I
am having strange problems with Collecion members and
memory that might force me to use arrays instead. I know
I cant be the only one w/ this issue.
Consider a simple Class called XClass
Consider a 2-D array of Collection of Classes, called
Portfolio(x,y)
I add a Class to one of the portfolios:
Portfolio(1,1).Add = XClass
And later on in the code, I add it to another class
Portfolio(5,1).Add XClass
Now, consider If a change a variable from within an
element in the Collection of Portfolio(5,1) which for
syntax reasons, I call PortfolioXY
PortfolioXY(i).Thing = PortfolioXY(i).Thing + 1
This is done after the The Temp. variable XClass has been
set to nothing and set to something else many times over.
Problem: The original Portfolio(1,1) is actually taking
the value of the Portfolio(5,1)
Somehow, they are referencing the same memory location !
But, This behavior is NOT consistent - it only happens
sometimes!!
I fear I have pushed VBA Collections beyond the limit ?
Answer:
I don't think that your problem is actually with Collections. I think it is most
likely the way that you are declaring and instantiating your XClass object.
In general, you'll want to declare a variable of type XClass, but NOT use the
New keyword. Then, create a new instance of the class with Set/New, and push
that instance into the Collection. E.g.,
Dim Obj As XClass
'
' lots of code
'
Set Obj = New XClass
'
' set various properties of Obj, e.g.,
'
Obj.Prop1 = 123
Obj.Prop2 = "abc"
'
' add Obj to the collection
'
Coll.Add Obj
Don't use Dim As New. Use Set = New, and the add that instance to the
collection.
