can an object contain a collection?

[whburling]

Hi,

My question is, "Can an object contain a collection?"

The context that I am attempting to program:

(1) I have a house. I would like the house to be a collection of Floor Objects
(2) I would like a Floor Object to contain the property, ID (means to determine which floor) and a collection of Room Objects

My understanding is that a collection is merely an object and that object properties can be objects as well as values. Hence I am concluding i will be able to define a Floor object to include a collection.

I need to make sure my understandings are correct.

In addition, to the above understanding, I need to grasp if the object A, that is a property of another object B, is passed by reference or by value. If Object A is passed by reference, then I am assuming any changes made to object A while in Object B, will be made to the Original Object A. If Object A is passed by value, then I am assuming any changes made to A while in Object B will not be passed back to the Original obj

Please correct my thoughts if in error. Thank you

[Gregor y]

Typically in Excel VBA a collection is a special type of object that works well with the For Each statement. It is not required, but very handy to stick with the naming convention of it being the plural version of the type of object that it holds.

For example you have an application object which has a workbooks collection of workbook objects so you could do

Please Login or Register  to view this content.

Carrying this over to your house example you'd likely want to have a house object with a floors collection object that would hold floor objects that would each have a rooms collection object that would hold room objects.

so that you could do

Please Login or Register  to view this content.

Objects are handled by reference; however ObjectA.ObjectB is not a pass of ObjectB to ObjectA it is a property/member reference on ObjectA.
A pass is what you do when you hand a variable over to a function/sub/method as a parameter.

[whburling]

Thank you for your help. Much appreciated.

I was able to follow you until the very end.

lets say i have an object B. It has a property C. lets say the default value of property C is a null object. I now want to define Property C.
so, independently of object B, I create an object A and set ObjectB.propertyC to objectA:

ObjectB.C = ObjectA

does this defining of ObjectB.C mean that the address of ObjectA gets deposited into the ObjectB.C property?
or is a copy of ObjectA get placed in the memory location of ObjectB.C property?

I am trying to understand how changes are propagated. I am assuming that in the first example, any changes to ObjectA are made to the original ObjectA
in the second instance, I am assuming any changes to ObjectA persist within ObjectB but not outside of ObjectB.

Am I understanding this issue at all? I apologize for not knowing this information.

[Gregor y]

it is by reference so

Please Login or Register  to view this content.

Assigns ObjectA to the property C of ObjectB. While Excel VBA tries its very hardest to hide the actual pointer work behind the sceens by just calling the class-objects "objects", it basically behaves as you'd expect a pointer referenced object to behave. So that any change to ObjectB.C.prop would be a change to ObjectA.prop. (because C and ObjectA point to the same place in memory)

[Gregor y]

Most of the time however you do not need a real collection object in an Excel VBA project because you can basically do the same with the Array object. The difference being that a collection when built properly will only accept one type of object to hold vs an array which doesn't care what it holds.

[whburling]

Gregory

YOU are awesome. Awesome. Damn....i am jumping with happiness. I can't begin to convey how grateful I am that you listened to me. Made one hell of an attempt to understand me.
and wrote an incredibly clear explanation. Thank you....Thank you....Thank you.

[whburling]

i am so glad you mentioned an array object. I suspect I should be using an array object more frequently. An interesting thing about an array (I am guessing as i don't really know) is that i must know something about the structure of the array in order to extract the data. when i make my feeble attempts to write code, i try so so hard to write it so that anyone using the code needs to know the minimum to extract data. I bury the details within the object (is the correct word, "imbed")

[Gregor y]

The difference being that a collection when built properly will only accept one type of object to hold vs an array which doesn't care what it holds.

I guess I need to walk this back a bit, it has been a while since I've had my hands in VBA code.

Arrays in VBA come in many varieties.

you can explicitly give it an object type

Please Login or Register  to view this content.

or let it hold anything

Please Login or Register  to view this content.

likewise you can state the size upfront

Please Login or Register  to view this content.

or figure it out later

Please Login or Register  to view this content.

or do what I was incorrectly remembering as an Array object

Please Login or Register  to view this content.

All of these solutions however require you to keep track of: how full the array is, if it needs to be resized, and what you put in which index. (although in this context it makes the most sense to me to have floor(0) be the crawlspace up to floor(4) the attic)

oddly enough the generic collection object is maybe the best fit for this situation, since you wouldn't have to do so much to keep track of its size

Please Login or Register  to view this content.

there are a few other options

Please Login or Register  to view this content.

However if you can avoid it I'd probably have you steer away from a truly custom collection class.

I'm not sure what you mean by bury/embed the structure in the code but your custom class objects should show up in the "Object Browser(2)" if you search for them. Keep in mind though that VBA code typically sticks with the workbook unless you share it with others and they put in their PERSONAL.XLS, which is why it's usually built to do a task specific to the project that the workbook is associated with.