Page 340 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Private MyPlants As Collection
Private Sub Class_Initialize()

Set MyPlants = New Collection
End Sub

Private Sub Class_Terminate()
Set MyPlants = Nothing

End Sub
An object is added to the collection using the following code. The code assumes that the
Chapter 14
object being added has a Name property whose type is String. The routine begins by using
the On Error Resume Next statement to disable error trapping. Any errors will simply force
Visual Basic to execute the next statement. To detect that an error occurred, the Err object
is used.

Public Sub Add(Item As Plant)

DimiAsLong
DimsAsString
On Error Resume Next

i=0
s = Item.Name
MyPlants.Add Item, s
Do While Err.Number <> 0
i=i+1
Item.Name = s & “(“ & FormatNumber(i, 0) & “)"
Err.Clear
MyPlants.Add Item, Item.Name

Loop

End Sub
The counter i is initially set to zero, and the name of the new object is saved in the temporary
variable s. Then the Collection object’s Add method is used to try to add the new object to the
Collection object.
If an error occurs in the Add method, the counter i is incremented. A new Name for the
object is constructed by using the original name followed by an open parenthesis, the num­
ber from the counter i, and a close parenthesis. Then the routine attempts to add the new
object to the collection again. If the Add method fails again, the loop is repeated until the
name of the object is unique.

314
Part 4: Advanced VBA