Adding Collections to Collections

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
77
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:

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?

'************************
VBA Code:
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

VBA Code:
Public FirstName As String
Public LastName As String
Public City As String
'**************************
In my Contractor Class Module I have:
'****************************
VBA Code:
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
'***********************
VBA Code:
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
'**********************
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Watch MrExcel Video

Forum statistics

Threads
1,129,364
Messages
5,635,839
Members
416,885
Latest member
hs11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top