can an object contain a collection?

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

dim wbs as workbooks, wb as workbook
set wbs = application.workbooks
for each wb in wbs
   ' do something with wb
next wb

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

dim h as house, f as floor, r as room
for each f in house.floors
   for each r in f.rooms
      ' do something with r
   next r
next f

set r = h.floors(0).rooms(0)

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.

it is by reference so

dim ObjectB as MyClass
dim ObjectA as new MyOtherClass

set ObjectB.C = ObjectA

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)

Originally Posted by 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

dim floors() as floor

or let it hold anything

dim floors() as variant

likewise you can state the size upfront

dim floors(100) as floor

or figure it out later

dim home as house, neighbor as house
redim neighbor.floors(5)

dim LastFloor as long, FloorCount as long
LastFloor  = 0 'ubound() is as big as the array is, but LastFloor is how full it is
FloorCount = 2
redim home.floors(FloorCount) as floor

dim OnFloor as long
for OnFloor = LBound(neighbor.floors) to Ubound(neighbor.floors)

   set home.floors(LastFloor) = new floor
   LastFloor = LastFloor + 1
   if LastFloor > UBound(home.floors) then
      ReDim Preserve home.floors(LastFloor + 100)
   end if
next OnFloor

dim i as long, aFloor as floor
for i = 0 to LastFloor - 1
   set aFloor = home.floors(i)
   DoSomethingWith aFloor
next i

or do what I was incorrectly remembering as an Array object

dim floors as variant, f1 as new floor, f2 as new floor, f3 as new floor

set floors = Array(f1, f2, f3)

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

dim floors as new Collection, f1 as new floor, f2 as new floor, f3 as new floor
floors.add f1
floors.add f2
floors.add f3

dim aFloor as floor
for each aFloor in floors
   DoSomethingWith aFloor
next aFloor

there are a few other options

dim floors as variant, f1 as new floor, f2 as new floor, f3 as new floor
dim aFloor as floor

set floors = CreateObject("Scripting.Dictionary")
floors.add f1, 0
floors.add f2, 0
floors.add f3, 0
for each aFloor in floors
   DoSomethingWith aFloor
next aFloor

set floors = CreateObject("System.Collections.SortedList")
floors.add f1, 0
floors.add f2, 0
floors.add f3, 0
dim i as long
for i = 0 to floors.Count - 1
   set aFloor = floors.GetKey(i)
   DoSomethingWith aFloor
next i

'... pick your favorite COM/.NET component

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.