Determining whether a cell has a comment

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

MsgBox Range("Al").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("Al").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("Al").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.

0 0

Post a comment