I've made a class module and I'm tring to add my objects to a collection, but I can't seem to get the code right. I've set up my class module like below, and in my sub I want to loop through a list of order numbers and add one of my class module records to my collection. The problem is that only one record gets stored, multiple times

Thanks for any hel
Ro

'Class Module "Record" Cod
Public OrderNumber as lon

Function OrderDate() As Dat
OrderDate = FetchOrderDate(OrderNumber
End Functio

Function DeliveryDate() As Dat
DeliveryDate = FetchDeliveryDate(OrderNumber
End Functio
'End of Class Module "Record" Cod

'Module Cod
Sub StoreRecord
Dim MyCollection as New Collectio
Dim MyRecord as Recor
For Each Cell in Selectio
MyRecord.OrdernNmber = Cell.Valu
MyCollection.Add MyRecor
Nex

'now I test whether it worked by getting it to tell me the order numbers store
For Each Record in MyCollectio
MsgBox Record.OrderNumbe
Nex

End Sub

I think you are missing a line in your loop:

Dim MyRecord as Record
For Each Cell in Selection
Set MyRecord = New Record ' << missing line
MyRecord.OrdernNmber = Cell.Value
MyCollection.Add MyRecord
Next

--

Rob wrote in message ...
I've made a class module and I'm tring to add my objects to a collection, but I can't seem to get the code right. I've set up my class module like below, and in my sub I want to loop through a list of order numbers and add one of my class module records to my collection. The problem is that only one record gets stored, multiple times.

Thanks for any help
Rob

'Class Module "Record" Code
Public OrderNumber as long

Function OrderDate() As Date
OrderDate = FetchOrderDate(OrderNumber)
End Function

Function DeliveryDate() As Date
DeliveryDate = FetchDeliveryDate(OrderNumber)
End Function
'End of Class Module "Record" Code

'Module Code
Sub StoreRecords
Dim MyCollection as New Collection
Dim MyRecord as Record
For Each Cell in Selection
MyRecord.OrdernNmber = Cell.Value
MyCollection.Add MyRecord
Next

'now I test whether it worked by getting it to tell me the order numbers stored
For Each Record in MyCollection
MsgBox Record.OrderNumber
Next

End Sub