0

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.

Zapata
  • 133
  • 1
  • 5
  • 20

1 Answers1

1

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.

Community
  • 1
  • 1
OpiesDad
  • 3,385
  • 2
  • 16
  • 31
  • It gives me the run-time error '424'" Object required . It seems that the collection is not defined yet. Have you tried the code? – Zapata Jul 19 '16 at 21:02
  • I have tried this (although I removed the rest of your code). Did you get rid of the "Dim employee as Collection" line within the employee_collection subroutine? – OpiesDad Jul 19 '16 at 21:05
  • It is fine with the employee.count but I am looking for properties such as employee.ID and employee.Age. – Zapata Jul 19 '16 at 22:03
  • OK I see it can be called with Debug.Print employee.Item(100).Age. – Zapata Jul 19 '16 at 22:08
  • Debug.Print merely outputs the value to the immediate window. I just used it to show how the collection could be accessed. – OpiesDad Jul 19 '16 at 22:09
  • So, for instance, if you wanted to put the value in a cell, you would use `Sheets("mySheet").Range("A1") = employee.Item(100).Age`. – OpiesDad Jul 19 '16 at 22:12
  • do I need to call the employee_collection whenever I want to use employee? How can I do so? – Zapata Jul 19 '16 at 22:25
  • I am adding E1.ID here in this module and I want to be able to add more properties later on. But the code does not remember the E.1 even if I call the class. When I am defining a new property ( E.1. first name) I do not want to redefine the old properties (E.1.ID) – Zapata Jul 20 '16 at 18:26
  • You need to ask a new question and include all of the relevant code. You are putting objects of type `clsEmployee` into your collection `employee`. If you extract the object from the collection and then change it, it might not update the item in the collection. See here: https://social.msdn.microsoft.com/Forums/en-US/cd0ea68e-e76d-4aaf-948c-ac47e9b22d0f/how-do-i-change-the-value-of-a-collection-item?forum=isvvba for an example of how to do this properly. – OpiesDad Jul 20 '16 at 19:11