The Comments collection

Recall that a collection is a group of like objects. Every worksheet has a Comments collection, which consists of all Comment objects on the worksheet. If the worksheet has no comments, this collection is empty.

For example, the following code refers to the first comment on Sheetl of the active workbook:

Worksheets("Sheet1").Comments(1)

The following statement displays the text contained in the first comment on

Sheetl:

MsgBox Worksheets("Sheet1").Comments(1).Text

Unlike most objects, a Comment object does not have a Name property. Therefore, to refer to a specific comment, you must either use an index number or use the Comment property of a Range object to return a specific comment. (Keep reading, and this will make sense.) The Comments collection is also an object and has its own set of properties and methods. For example, the following example shows the total number of comments:

MsgBox ActiveSheet.Comments.Count

The Comments collection here has a Count property that stores the number of Comment objects in the active worksheet. The next example shows the address of the cell that has the first comment:

MsgBox ActiveSheet.Comments(l).Parent.Address

Here, Comments(l) returns the first Comment object in the Comments collection. The Parent property of the Comment object returns its container, which is a Range object. The message box displays the Address property of the Range. The net effect is that the statement displays the address of the cell that contains the first comment.

You can also loop through all the comments on a sheet by using the For Each-Next construct. (This is explained in Chapter 8.) Here's an example that displays a separate message box for each comment on the active worksheet:

For Each cmt in ActiveSheet.Comments

MsgBox cmt.Text Next cmt

If you'd rather not deal with a series of message boxes, use this procedure to print the comments to the Intermediate window in the VBE:

For Each cmt in ActiveSheet.Comments

Debug.Print cmt.Text Next cmt

0 0

Post a comment