Collection.add updating all items in collection?

[immdav]

I am working though the Creating Classes, records and collections chapter in Bill's book VBA and macros for excel.

I placed the following code in my non class module.
I have my locals windows open and can see that when a new record is added to colEmployees all the items in the collection are up to that record?
the data is in a sheet named "EmpHrs" headers in row 1

Can anyone help me out with this?

Sub EmpCollection()

Dim colEmployees As New Collection
Dim recEmployee As New clsEmployee
Dim lrEmpHr As Double
Dim arrEmp As Variant
Dim I As Integer
Dim strKey As String


lrEmpHr = shEmpHrs.Cells(Rows.Count, 1).End(xlUp).Row

arrEmp = shEmpHrs.Range(Cells(2, 1), Cells(lrEmpHr, 4))

For I = 1 To UBound(arrEmp)

 With recEmployee
    .EmpName = arrEmp(I, 1)
    .EmpID = arrEmp(I, 2)
    .EmpRate = arrEmp(I, 3)
    .EmpWeeklyHrs = arrEmp(I, 4)
    strKey = .EmpID
    colEmployees.Add recEmployee, Key:=strKey
    End With
Debug.Print arrEmp(I, 1), arrEmp(I, 2), arrEmp(I, 3), arrEmp(I, 4)
Debug.Print colEmployees(1).EmpRate

Next I

MsgBox "Number of Emloyees: " & colEmployees.Count & vbCrLf & "Employee Name: " & colEmployees.Item("101").EmpName
MsgBox "Jill's pay: $" & colEmployees("101").EmpWeeklyPay

Set recEmployee = Nothing

End Sub
**********************

My class module

Public EmpName As String
Public EmpID As String
Public EmpRate As Double


Private NormalHrs As Double
Private OverHrs As Double

Property Let EmpWeeklyHrs(Hrs As Double)
NormalHrs = WorksheetFunction.Min(40, Hrs)
OverHrs = WorksheetFunction.Max(0, Hrs - 40)
End Property

Property Get empweelyhrs() As Double
EmpWeeklyHrs = NormalHrs + OverHrs
End Property

Property Get EmpNormalhrs() As Double
EmpNormalhrs = NormalHrs
End Property
Property Get EmpOverHrs() As Double
EmpOverHrs = OverHrs
End Property

Public Function EmpWeeklyPay() As Double
EmpWeeklyPay = (EmpNormalhrs * EmpRate) + (EmpOverHrs * EmpRate * 1.5)


End Function

Debug window


Jack 100 10 40
10
Jill 101 20 10
20
monkey 102 30 35
30
see 103 40 45
40

 

[Domenic]

I've taken a quick look. First, don't use the keyword "New" to declare recEmployee. Instead, declare it like this...

Dim recEmployee As clsEmployee

Then create a new instance of recEmployee for each record like this...

[COLOR=darkblue]For[/COLOR] I = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](arrEmp)
    [COLOR=darkblue]Set[/COLOR] recEmployee = [COLOR=darkblue]New[/COLOR] clsEmployee
    [COLOR=darkblue]With[/COLOR] recEmployee
        .EmpName = arrEmp(I, 1)
        .EmpID = arrEmp(I, 2)
        .EmpRate = arrEmp(I, 3)
        .EmpWeeklyHrs = arrEmp(I, 4)
        strKey = .EmpID
        colEmployees.Add recEmployee, Key:=strKey
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    Debug.Print arrEmp(I, 1), arrEmp(I, 2), arrEmp(I, 3), arrEmp(I, 4)
    Debug.Print colEmployees(I).EmpRate
[COLOR=darkblue]Next[/COLOR] I

Hope this helps!

 

[immdav]

Domenic,

Thanks, this works.
I am trying to figure out why it works
Is this is what happening in my first bit of code?
When it adds item (2) it sets item (1) to the current recEmployee because it was add as recemployee and is the same instance of recEmployee?

and in your code
A new instance of recEmployee is created so the first item(1) is not pointing (for lack of a better term) to the new instance of recEmployee when Item two is created.


In other words:
A item in a collection will always look to the instance of the classobject that was used to add it. It dose not stay with the value that created it but keeps looking at that class object,unless that instance is destroyed?

Did I get that right?

David Hansen

 

[Domenic]

When you used the keyword "New" to declare recEmployee, only one instance of the object was ever created. It automatically created an instance of recEmployee when it was first referenced in the following line...

With recEmployee

In subsequent iterations, the same instance was being referenced, not a new one. And so the new data was being assigned to the same instance. And, that same instance was being added to the collection again, and again.

Without the use of "New" to declare recEmployee, a new instance can be created for each record...

Set recEmployee = New clsEmployee

So new data can be assigned to each new instance, and each new instance added to the collection.