Class Collection Add Items keep repeating

[Guest]

I tried searching google groups, but no luck. I am attempting to use classes
for the first time. To help me get started, I basically copied something out
of a vba book, but changed certain things for my purposes.

I am debugging my program and have added a watch to my collection variable
(at least as far as I can tell from following the example in the book.) The
problem is that all of the items turn out to be the same. As I step through
the program, I see that when it adds the first item to the collection,
everything is ok. But as soon as it reads the next item (from a variant
array that was populated from a spreadsheet,) I can see (through the watch
window) that the first item gets replaced with the 2nd item. And when the
2nd item is added (from the class add routine) it is the same. So by the
time the program finishes, all of the items contain the information from the
last item read. Below are my classes, and code in my standard module:

CStore
Public sID As String
Public sDescription As String
Public sZip As String
Public sZone As String
Public sDistrict As String
Public sTrainer As String
Private sZoneDistrict As String


Property Let ZoneDistrict(sMyZone As String, sMyDistrict As String)
sZoneDistrict = sMyZone & " " & sMyDistrict
End Property
(It doesn't really do much at this point.)

CStores
Option Explicit
Private AllStores As New Collection
Public Property Get Items() As Collection
Set Items = AllStores
End Property
Public Property Get Item(myItem As Variant) As CStore
Set Item = AllStores(myItem)
End Property
Public Sub Remove(myItem As Variant)
AllStores.Remove (myItem)
End Sub
Public Sub Add(recStore As CStore)
AllStores.Add recStore, recStore.sID
End Sub
Public Property Get Count() As Long
Count = AllStores.Count
End Property

Standard module MStores
Sub StoreAddCollection()
Dim colStores As New CStores
Dim recStore As New CStore
Dim wsStore As Worksheet
Dim lFinalRow As Long
Dim i As Integer
Dim vaStore As Variant
Set wsStore = Workbooks("Stores and DMs").Worksheets("Stores")
lFinalRow = wsStore.Cells(Rows.Count, 1).End(xlUp).Row
With wsStore
vaStore = .Range(.Cells(2, 1), .Cells(lFinalRow, 5))
End With
For i = 1 To UBound(vaStore)
With recStore
.sID = vaStore(i, 1)
.sDescription = vaStore(i, 2)
.sZone = vaStore(i, 3)
.sDistrict = vaStore(i, 4)
.sZip = vaStore(i, 5)
colStores.Add recStore
End With
Next i
End Sub

As soon as it gets to the .sID = line, the first item changes to the 2nd. A
small sample of my spreadsheet data:
184 Chula Vista South 11 1 Closed?
559 Mission Gorge 11 1 92120

I thought maybe the problem was that the variables in CStore were public,
but when I tried changing them to private, the program wouldn't run at all.
If I can progress through this first attempt, I will hopefully be able to
change them to private, but I need to get through this.

Can anyone tell me what I am doing wrong that is causing the items in
colStores to change on each iteration? If so, thanks in advance.

 

[Peter T]

Hi Keivin,

You were adding the same instance of Cstore to the collection in Cstores in
each loop

Try changing the code in the normal module as follows

'Standard module MStores

'at module level or (maybe Public) so can
' use in other proc's after doing StoreAddCollection
Dim colStores As CStores


Sub StoreAddCollection()
Dim recStore As CStore
Dim wsStore As Worksheet
Dim lFinalRow As Long
Dim i As Integer
Dim vaStore As Variant

'only for testing, put in some values

'Set wsStore = Workbooks("Stores and DMs").Worksheets("Stores")
Set wsStore = ActiveSheet


lFinalRow = wsStore.Cells(Rows.Count, 1).End(xlUp).Row
With wsStore
vaStore = .Range(.Cells(2, 1), .Cells(lFinalRow, 5))
End With

Set colStores = New CStores

For i = 1 To UBound(vaStore)

'a brand new instance, later to add to the
'collection in colStores
Set recStore = New CStore

With recStore
.sID = vaStore(i, 1)
.sDescription = vaStore(i, 2)
.sZone = vaStore(i, 3)
.sDistrict = vaStore(i, 4)
.sZip = vaStore(i, 5)
colStores.Add recStore
End With
Next i
End Sub

Sub test()
For i = 1 To colStores.Items.Count
Debug.Print colStores.Items(i).sDescription
Next
Stop
' press alt-v s
' expand and look at CStores
End Sub

Not sure about one or two other things in the class modules.

Regards,
Peter T

 

[Guest]

Apparently the answer is to set the recStore variable to nothing:
right after the line:
colStores.Add recStore
I put this line and it seems to have done the trick.
Set recStore = Nothing

I guess I should studied help more before I posted.

 

[Guest]

Thanks!

I incorporated your suggestions and they appear to have worked. I have been
reading about classes for quite a while now (though as I stated this is my
first time trying to use them,) and I know that eventually, at least some of
the books recommend DIMming only the class variable (as opposed to Dimming a
new instance of the class variable) and doing a Set var as new whatever, but
the book that I copied this out of has Dim colEmployees as New clsEmployees
and Dim recEmployee as clsEmployee. Frankly, I don't know why they didn't
have the same problem I was having. BUt I went to their website and couldn't
find anything about this.