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 Comments New Comment command to enter a cell comment. In the sections that follow, you get a feel for working with objects.
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.
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.
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. |
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.
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.
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. |
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
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. |
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:
Use the Comment object's Shape property to return the Shape object that's contained in the comment.
Use the Shape object's Fill property to return a FillFormat object.
Use the FillFormat object's ForeColor property to return a ColorFormat object.
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. |
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.
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 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. |