VBA Creating a Collection in a Class Module

[drj30026abanba]

Hi this is my first time posting on this forum so I hope I am putting this in the right place. I am currently reading MrExcel's "VBA and Macros: Microsoft Excel 2013" and I am having trouble understanding a section in chapter 9 called "Creating a Collection in a Class Module".

Earlier in the chapter a custom object called clsEmployee was created. A class module is then inserted and renamed clsEmployees. Then a private collection is declared in the class module

Private AllEmployees As New Collection

After that, a few methods are created. Here is one of them

Public Sub Add(recEmployee As clsEmployee)

AllEmployees.Add recEmployee, recEmployee.EmpID

End Sub

Later in this section, a procedure in a standard module is written to use this collection. It is instantiated like this

Dim colEmployees as New clsEmployees

and later on the add method is used:

colEmployees.Add recemployee

I don't understand why clsEmployees is the name being used to instantiate colEmployees. That is the name of the class module just created. Is the name of the class module always used to instantiate? Why isn't AllEmployees declared Public and used since it is how all the methods are defined?

I don't get why AllEmployees is Private. Also, I don't understand how clsEmployees is related to AllEmployees. AllEmployees looks like a dummy variable but I don't understand how VBA knows that.

I guess my main question is this: When creating a collection with a class module, how is the name of the class module related to the private declaration of a collection?

 

[RoryA]

It's not. The collection name can be anything you like as can the class name.

 

[StephenCrump]

Welcome to the Forum.

It's probably worth noting first that you can use a collection to store multiple instances of a class. So you could, for example, do something like this:

Dim colEmployees As Collection
Dim Employee As clsEmployee

Set colEmployees = New Collection
Set Employee = New clsEmployee

'Set properties for Employee, eg .Name, .EmpID perhaps
colEmployees.Add Employee, Key:=Employee.Empid

Instead, here another Class called clsEmployees has been created. AllEmployees is a collection within each instance of clsEmployees. The approach should make for more robust coding, e.g. it better controls what can be put into the employees collection.

 

[drj30026abanba]

Thanks for the replies!

StephenCrump, the code you wrote is actually exactly what the book says to do to create a collection in a standard module. That makes sense to me but I don't understand what the book is doing when they created the collection in a class module. Is the AllEmployees collection a default property of the class clsEmployees because it's the only thing declared? Is that why even though the methods are defined with AllEmployee they are able to be used by clsEmployees objects without reference to AllEmployee?

 

[RoryA]

The AllEmployees collection is private and cannot be directly accessed from outside the clsEmployees class.

The clsEmployees class has its own Public Add method declared which adds the clsEmployee objects to the AllEmployees collection, and it is that Add method that is called from outside the class.

 

[drj30026abanba]

Thanks for the explanation RoryA! I didn't understand how that public add method worked until you explained it. Thanks!