Adding Collections to Collections

[rkaczano]

I am trying wrap my head around when it would make sense to have a custom object and its collection be part of an over-arching parent collection.

I am working with the example from this link:

Custom Collection Class – Daily Dose of Excel

dailydoseofexcel.com

I have 2 custom objects: Employee and Contractor. And Employee is part of a custom collection Staff and Contractor is part of a custom Collection ThirdPartyLabour

Below is code that I can use to populate the two custom collections with instances of their objects. I pull in data from two ranges called DataTable1 and DataTable2 and populate the customer object properties. The sub populates and prints the results to the immediate window.

Test 1: return all Employees
Bob Smith New York
Jack Brown Las Vegas
Larry Anderson Houston

Test 1: return all Third Party Labour
**** Santos Seattle
Barry Bringham Portland
Ronald Best Vancouver

Let's say I wanted to created an overarching Collection called Division, and say I wanted this to be part of an over-arching Collection called Company.

How do I add these collections to those two over-arching collections?

'************************

Sub test()
    Dim stf As Staff, empl As Employee
    Dim ThrdPrtLab As ThirdPartyLabour, cntr As Contractor
    Dim rng1 As Range
    Dim rng2 As Range
    
    Set stf = New Staff
    Set ThrdPrtLab = New ThirdPartyLabour
    
    
    Set rng1 = Range("DataTable1")
    Set rng2 = Range("DataTable2")
    
    stf.FillFromSheet rng1
    ThrdPrtLab.FillFromSheet rng2
 
    Debug.Print "Test 1: return all Employees"
    For Each empl In stf
        Debug.Print empl.FirstName; vbTab; empl.LastName; vbTab; empl.City
    Next
    
    Debug.Print "Test 1: return all Third Party Labour"
    For Each cntr In ThrdPrtLab
        Debug.Print cntr.FirstName; vbTab; cntr.LastName; vbTab; cntr.City
    Next
    
End Sub

'**************************

Class Module Code
'***********************
In my Employee Class module I have:
Option Explicit

Public FirstName As String
Public LastName As String
Public City As String

'**************************
In my Contractor Class Module I have:
'****************************

Option Explicit
 
[CODE=vba]Public FirstName As String
Public LastName As String
Public City As String

'*************************
In my Staff Collection Class I have:
'************************
Option Explicit
Private objStaff As Collection

Private Sub Class_Initialize()
Set objStaff = New Collection
End Sub

Private Sub Class_Terminate()
Set objStaff = Nothing
End Sub

Public Property Get NewEnum() As IUnknown
Set NewEnum = objStaff.[_NewEnum]
End Property

Public Sub Add(obj As Employee)
objStaff.Add obj
End Sub

Public Sub Remove(Index As Variant)
objStaff.Remove Index
End Sub

Public Property Get Item(Index As Variant) As Employee
Set Item = objStaff.Item(Index)
End Property

Property Get Count() As Long
Count = objStaff.Count
End Property

Public Sub Clear()
Set objStaff = New Collection
End Sub
Public Sub FillFromSheet(rng As Range)
Const cFirstRow = 2, cFirstNameCol = 1, cLastNameCol = 2, cCityCol = 3
Dim i As Long, obj As Employee
With rng
For i = cFirstRow To .Cells(Rows.Count, 1).End(xlUp).Row
Set obj = New Employee
obj.FirstName = .Cells(i, cFirstNameCol)
obj.LastName = .Cells(i, cLastNameCol)
obj.City = .Cells(i, cCityCol)

Me.Add obj
Next
End With
End Sub[/CODE]
'**********************
In my ThirdPartyLabour Collection I have
'***********************

Option Explicit
Private objThirdPartyLabour As Collection
 
Private Sub Class_Initialize()
    Set objThirdPartyLabour = New Collection
End Sub
 
Private Sub Class_Terminate()
    Set objThirdPartyLabour = Nothing
End Sub
 
Public Property Get NewEnum() As IUnknown
    Set NewEnum = objThirdPartyLabour.[_NewEnum]
End Property
 
Public Sub Add(obj As Contractor)
    objThirdPartyLabour.Add obj
End Sub
 
Public Sub Remove(Index As Variant)
    objThirdPartyLabour.Remove Index
End Sub
 
Public Property Get Item(Index As Variant) As Contractor
    Set Item = objThirdPartyLabour.Item(Index)
End Property
 
Property Get Count() As Long
    Count = objThirdPartyLabour.Count
End Property
 
Public Sub Clear()
    Set objThirdPartyLabour = New Collection
End Sub
Public Sub FillFromSheet(rng As Range)
    Const cFirstRow = 2, cFirstNameCol = 1, cLastNameCol = 2, cCityCol = 3
    Dim i As Long, obj As Contractor
    With rng
        For i = cFirstRow To .Cells(Rows.Count, 1).End(xlUp).Row
            Set obj = New Contractor
            obj.FirstName = .Cells(i, cFirstNameCol)
            obj.LastName = .Cells(i, cLastNameCol)
            obj.City = .Cells(i, cCityCol)
 
            Me.Add obj
        Next
    End With
End Sub

'**********************