Adding Objects to a Custom Collection

After you've declared the Collection object, you can insert new items into the collection by using the Add method. The objects with which you populate your collection do not have to be of the same data type. The Add method looks like the following:

object.Add item, key, before, after

You are only required to specify object and item. object is the collection name. This is the same name that was used in the declaration of the Collection object. item is the object that you want to add to the collection.

Although other arguments are optional, they are quite useful. It's important to understand that the items in a collection are automatically assigned numbers from 1 on. However, they can also be assigned a unique key value. Instead of accessing a specific item with an index (1, 2, 3, and so on), you can assign a key for that object at the time an object is added to a collection. For instance, if you are creating a collection of custom sheets, you could use a sheet name as a key. To identify an individual in a collection of students or employees, you could use Social Security numbers as a key.

If you want to specify the position of the object in the collection, you should use either the before or after argument (do not use both). The before argument is the object before which the new object is added, and the after argument is the object after which the new object is added.

The GetComments procedure shown on the following page declares the custom collection object called colNotes. The procedure prompts for an author's full name and then loops through all the worksheets in the active workbook to locate this author's comments. Only comments entered by the specified author are added to the custom collection. The procedure assigns a key to the first comment and then adds the remaining comments to the collection by placing them before the comment that was added last (notice the use of the before argument). If the collection includes at least one comment, the procedure displays a message box with the text of the comment that was identified with the special key argument. Notice how the key argument is used in referencing an item in a collection. The procedure then prints the text of all the comments included in the collection to the Immediate window. Text functions (Mid and Len) are used to get only the text of the comment without the author's name. Next, the total number of comments in a workbook and the total number of comments in the custom collection are returned by the Count property.

Before you try out the GetComments procedure, set up the workbook file as follows:

1. Open a new workbook and save it as Chap11.xls.

2. Right-click any cell in Sheet1 and choose Insert Comment from the shortcut menu. Type any text you want. Click outside the comment frame to exit the comment edit mode. Use the same technique to enter two comments in Sheet2. Type different text for each comment. Add a new sheet (Sheet4) to the workbook, and add a comment. You should now have four comments in three worksheets.

3. Choose Tools | Options and click the General tab. The User name text box should display your name. Delete your name and enter Joan Smith, and click OK. Now, enter one comment anywhere on Sheet2 and one comment anywhere on Sheet4. These comments should be automatically stamped with Joan Smith's name. When you're done entering the comments text, return to the Options dialog box and change the entry in the User Name text box on the General tab back to your name.

4. Switch to the Visual Basic Editor, and rename the VBA project ObjColClass.

5. Add a new module to the current project, and rename it MyCollection.

6. Enter the GetComments procedure, as shown below:

Sub GetComments()

Dim sht As Worksheet

Dim colNotes As New Collection

Dim myNote As Comment

Dim I As Integer

Dim t As Integer

Dim fullName As String fullName = InputBox("Enter author's full name:")

For Each sht In ThisWorkbook.Worksheets sht.Select

I = ActiveSheet.Comments.Count

For Each myNote In ActiveSheet.Comments If myNote.Author = fullName Then MsgBox myNote.Text If colNotes.Count = 0 Then colNotes.Add Item:=myNote, key:="first"

Else colNotes.Add Item:=myNote, Before:=1 End If End If


If colNotes.Count <> 0 Then MsgBox colNotes("first").Text MsgBox "Total comments in workbook: " & t & Chr(13) & _ "Total comments in collection:" & colNotes.Count Debug.Print "Comments by " & fullName For Each myNote In colNotes

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


End Sub

7. Run the GetComments procedure and check its results.

0 0

Post a comment