Removing Objects from a Custom Collection

Removing an item from a custom collection is as easy as adding an item. To remove an object, use the Remove method in the following format:

object.Remove item object is the name of the custom collection that contains the object you want to remove. item is the object you want to remove from the collection.

To demonstrate the process of removing an item from a collection, let's modify the GetComments procedure that you prepared in the preceding section. At the end of this procedure, we'll display the contents of the items that are currently in the colNotes collection one by one and ask the user whether the item should be removed from this collection.

1. Add the following declaration lines to the declaration section of the GetComments procedure:

Dim response Dim myld As Integer

The first statement declares the variable called response. You will use this variable to store the result of the MsgBox function. The second statement declares the variable myld to store the index number of the collection object.

2. Locate the following statement in the GetComments procedure: For Each myNote In colNotes

Precede the above statement with the following: myId = 1

3. Locate the following statement in the GetComments procedure:

Debug.Print Mid(myNote.Text, Len(myNote.Author) + 2, _ Len(myNote.Text))

Enter the following block of instructions below that statement:

response = MsgBox("Remove this comment?" & Chr(13) _

& Chr(13) & myNote.Text, vbYesNo + vbQuestion) If response = 6 Then colNotes.Remove Index:=myId

4. Enter the following statements at the end of the procedure:

Debug.Print "The following comments remain in the collection:" For Each myNote in colNotes

Debug.Print Mid(myNote.Text, Len(myNote.Author) + 2, _ Len(myNote.Text))


5. Run the GetComments procedure, and remove one of the comments displayed in the message box.

The revised GetComments procedure, GetComments2, can be found in Chap11.xls on the companion CD-ROM. This procedure removes the specified comments from the custom collection. It does not delete the comments from the worksheets.

Tip 11-1: Reindexing Collections

Collections are reindexed automatically when an object is removed. Therefore, to remove all objects from a custom collection, you can use 1 for the Index argument, as in the following example:

Do While myCollection.Count >0 myCollection.Remove Index:=1


0 0

Post a comment