The Comment Object: A Case Study


To help you better understand the properties and methods available for an object, I focus on a particular object: the Comment object. In Excel, you create a Comment object when you choose the Review image from book Comments image from book New Comment command to enter a cell comment. In the sections that follow, you get a feel for working with objects.

Viewing Help for the Comment object

One way to learn about a particular object is to look it up in the Help system. Figure 7-13 shows some Help topics for the Comment object. I found this Help screen by typing comment in the VBE Type a Question for Help box (to the right of the menu bar). Notice that the Help Table of Contents displays the properties and methods for this object.

image from book
Figure 7-13: The main help screen for the Comment object.

Properties of a Comment object

The Comment object has five properties. Table 7-1 contains a list of these properties, along with a brief description of each. If a property is read-only, your VBA code can read the property but cannot change it.

Table 7-1: PROPERTIES OF A COMMENT OBJECT
Open table as spreadsheet

Property

Read-Only

Description

Application

Yes

Returns an object that represents the application that created the comment (that is, Excel).

Author

Yes

Returns the name of the person who created the comment.

Parent

Yes

Returns the parent object for the comment. (It is always a Range object.)

Shape

Yes

Returns a Shape object that represents the shape attached to the comment.

Visible

No

Is True if the comment is visible.

image from book
Using the Help System

The easiest way to get specific help about a particular object, property, or method is to type the word in a code window and press F1. If there is any ambiguity about the word that you typed, you get a dialog box like the one shown in the following figure.

Unfortunately, the items listed in the dialog box are not always clear, so it may require some trial and error to locate the correct help topic. The dialog box in the figure appears when you type Comment and then press F1. In this case, although Comment is an object, it may behave like a property. Clicking the first item displays the help topic for the Comment object; clicking the second item displays the help topic for the Comment property.

image from book
image from book
 

Methods of a Comment object

Table 7-2 shows the methods that you can use with a Comment object. Again, these methods perform common operations that you may have performed manually with a comment at some point but you probably never thought of these operations as methods.

Table 7-2: METHODS OF A COMMENT OBJECT
Open table as spreadsheet

Method

Description

Delete

Deletes a comment.

Next

Returns a Comment object that represents the next comment in the worksheet.

Previous

Returns a Comment object that represents the previous comment in the worksheet.

Text

Returns or sets the text in a comment (takes three arguments).

Note  

You might be surprised to see that Text is a method rather than a property. This leads to an important point: The distinction between properties and methods isn't always clear-cut , and the object model isn't perfectly consistent. In fact, it's not really important that you distinguish between properties and methods. As long as you get the syntax correct, it doesn't matter whether a word in your code is a property or a method.

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. Comments appear in the collection based on their position in the worksheet: Left-to-right and then top-to-bottom.

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

 Worksheets("Sheet1").Comments(1) 

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

 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 (more frequently) use the Comment property of a Range object to return a specific comment.

The Comments collection is also an object and has its own set of properties and methods. For example, the Comments collection has a Count property that stores the number of items in the collection - which is the number of Comment objects in the active worksheet. The following statement displays the total number of comments on the active worksheet.

 MsgBox ActiveSheet.Comments.Count 

The next example shows the address of the cell that has the first comment:

 MsgBox ActiveSheet.Comments(1).Parent.Address 

Here, Comments(1) 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. (Looping 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 Immediate window in the VBE:

 For Each cmt in ActiveSheet.Comments     Debug.Print cmt.Text Next cmt 

About the Comment property

In this section, I've been discussing the Comment object. If you dig through the Help system, you'll find that a Range object has a property named Comment . If the cell contains a comment, the Comment property returns a Comment object. For example, the following statement refers to the Comment object in cell A1:

 Range("A1").Comment 

If this were the first comment on the sheet, you could refer to the same Comment object as follows :

 ActiveSheet.Comments(1) 

To display the comment in cell A1 in a message box, use a statement like this:

 MsgBox Range("A1").Comment.Text 

If cell A1 does not contain a comment, this statement generates an error.

Note  

The fact that a property can return an object is a very important concept - a difficult one to grasp, perhaps, but critical to mastering VBA.

Objects within a Comment object

Working with properties is confusing at first because some properties actually return objects. Suppose that you want to determine the background color of a particular comment on Sheet1 . If you look through the list of properties for a Comment object, you won't find anything that relates to color. Rather, you must do this:

  1. Use the Comment object's Shape property to return the Shape object that's contained in the comment.

  2. Use the Shape object's Fill property to return a FillFormat object.

  3. Use the FillFormat object's ForeColor property to return a ColorFormat object.

  4. Use the ColorFormat object's RGB property to get the color value.

Put another way, getting at the interior color for a Comment object involves accessing other objects contained in the Comment object. Here's a look at the object hierarchy that's involved:

 Application (Excel)      Workbook object       Worksheet object        Comment object         Shape object          FillFormat object           ColorFormat object 

I'll be the first to admit it: This can get very confusing! But, as an example of the elegance of VBA, the code to change the color of a comment can be written with a single statement:

 Worksheets("Sheet1").Comments(1).Shape.Fill.ForeColor _     .RGB = RGB(0, 255, 0) 

Or, if you use the SchemeColor property (which ranges from to 80 ), the code is:

 Worksheets("Sheet1").Comments(1).Shape.Fill.ForeColor _     .SchemeColor = 12 

This type of referencing is certainly not intuitive at first, but it will eventually make sense. Fortunately, recording your actions in Excel almost always yields some insights regarding the hierarchy of the objects involved.

By the way, to change the color of the text in a comment, you need to access the Comment object's TextFrame object, which contains the Characters object, which contains the Font object. Then you have access to the Font object's Color or ColorIndex properties. Here's an example that sets ColorIndex property to 5 :

 Worksheets("Sheet1").Comments(1) _   .Shape.TextFrame.Characters.Font.ColorIndex = 5 
CROSS-REFERENCE  

Refer to Chapter 30 for more information on colors.

Determining whether a cell has a comment

The following statement displays the comment in cell A1 of the active sheet:

 MsgBox Range("A1").Comment.Text 

If cell A1 does not have a comment, executing this statement generates a cryptic error message: Object variable or With block variable not set .

To determine whether a particular cell has a comment, you can write code to check whether the Comment object is Nothing . (Yes, Nothing is a valid keyword.) The following statement displays True if cell A1 does not have a comment:

 MsgBox Range("A1").Comment Is Nothing 

Note that I use the Is keyword and not an equal sign.

You can take this one step further and write a statement that displays the cell comment only if the cell actually has a comment (and does not generate an error if the cell lacks a comment). The statement that follows accomplishes this task:

 If Not Range("A1").Comment Is Nothing Then _   MsgBox Range("A1").Comment.Text 

Notice that I used the Not keyword, which negates the True value that's returned if the cell has no comment. The statement, in essence, uses a double-negative to test a condition: If the comment is not nothing, then display it. If this is confusing, think about it for a while and it will make sense.

Adding a new Comment object

You may have noticed that the list of methods for the Comment object doesn't include a method to add a new comment. This is because the AddComment method belongs to the Range object. The following statement adds a comment (an empty comment) to cell A1 on the active worksheet:

 Range("A1").AddComment 

If you consult the Help system, you discover that the AddComment method takes an argument that represents the text for the comment. Therefore, you can add a comment and then add text to the comment with a single statement, like this:

 Range("A1").AddComment "Formula developed by JW." 
Note  

The AddComment method generates an error if the cell already contains a comment. To avoid the error, your code can check whether the cell has a comment before adding one.

CD-ROM  

If you'd like to see these Comment object properties and methods in action, check out the example workbook on the companion CD-ROM. This workbook, named image from book  comment object.xlsm , contains several examples that manipulate Comment objects with VBA code. You probably won't understand all the code, but you will get a feel for how you can use VBA to manipulate an object.




Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net