Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Collection Add Items keep repeating
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Collection Add Items keep repeating
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. -- Kevin Vaughn "Kevin Vaughn" wrote: |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Collection Add Items keep repeating
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. -- Kevin Vaughn "Peter T" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wrapping a Collection Class | Excel Programming | |||
Need Class Collection advice | Excel Programming | |||
Collection Class problems | Excel Programming | |||
RaiseEvent from a class contained in a 2nd class collection? | Excel Programming | |||
For/Each iteration for collection class | Excel Programming |