Class Collection Add Items keep repeating
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
"Kevin Vaughn" wrote in message
...
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.
--
Kevin Vaughn
|