CODEWITHSUNDEEP
vbaclassobjectcollectionscall
Zapata
Zapata

Reputation: 133

call an object of a collection vba

I have a collection of employees (object) and each employee has his/her own properties(attributes) such as ID, Age, etc. I have defined a class module (named clsemployee) as below:

Public ID As Integer Public Age As Integer . .

And I added the properties of ID, age and etc. to the collection as below in a module:

Public Sub employee_collection() ' this collection saves all of the employees records

Dim employee As Collection
Set employee = New Collection
Dim n As Integer
Dim i As Integer
Dim E1 As Variant
Dim j As Integer
n = 528

Dim a, b As String
For i = 3 To n
a = "A" + CStr(i) ' to get the values from the excel sheet
b = "B" + CStr(i)

Set E1 = New clsEmployee
E1.ID = Sheets("A").Range(a).Value ' save the valus of each employee in the collection
E1.Age = Sheets("A").Range(b).Value
employee.Add E1

Next i
End Sub

I do not know how to call this collection in my other modules (sub). Should I call it by value or call by reference? I do not want to repeat defining this employee in each and every sub that I have.

Upvotes: 0

Views: 1552

Answers (1)

OpiesDad
OpiesDad

Reputation: 3435

To expand upon what cyboashu said:

Global employee as Collection

Public Sub employee_collection()

     Set employee = New Collection
     ....'rest of code here

End Sub

Public Sub use_collection()

    Debug.print employee.count

End Sub

Note that the Global declaration needs to be in a module, also as stated by cyboashu.

Run the employee_collection code 1 time when you want to populate the collection with the employees. Then, you can simply use the collection in any further procedures as it has already been filled.

Note that it is possible for the Global variables to be reset. See here for a good explanation of this.

Upvotes: 1

Related Questions