Page 342 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Sub Test()
Dim MyPlants As Plants
DimpAsPlant
DimiAsLong
Set MyPlants = New Plants
MyPlants.SampleData
Fori=1To MyPlants.Count
Set p = MyPlants.Item(i)
MsgBox p.Name

Next i
Chapter 14
Set p = Nothing
Set MyPlants = Nothing
End Sub

Next it uses a For Next loop to iterate through each item in the collection. The object variable
p is set to the current item from the collection, and the Name property is displayed in a mes­
sage box.
Notice that the first item in the collection begins with 1, and the number of items in the col­
lection is retrieved from the collection’s Count property.

Where Do I Get My Data?

Classes are an ideal way to hold data from an external source. By holding the data in a col­
lection class, you can allow your program to access the data independently of how the data
is physically stored.
This way if you change the way the data is stored, you don’t have to change the way that the
data is accessed. By providing a method named LoadData, anyone using the class can load
the data from the data source. Then if you migrate the data from a worksheet to an Access
database, only the load method will change. The code accessing the collection class won’t
change, unless you change the parameters to the LoadData method.
Likewise, you could provide a standard method called SaveData, which would update the
data wherever it’s stored. With a little work, you could even make the method intelligent
enough so that it updates only the objects that were updated, instead of having to update
all the data whether it was updated by the user or not.

316
Part 4: Advanced VBA