Monday, February 25, 2013

Excel Comments, Part 1

Here's the problem I was trying to solve: Adding to existing comments.

My usual way of dealing with this sort of this is to find the text string then modify it.  Looking at the object model I found range.Comment.Text. Expecting consistency (silly me) I tried to manipulate Comment.Text like any other string.  When that didn't work, I drove around the object model a few times and found range.Comment.Shape.DrawingObject.Text.

range.Comment.Shape.DrawingObject.Text was something that could be manipulated like other  strings.  I was on my way!

While developing the modules for grabbing a new comment and adding it to the existing comment, the light went on: Comment.Text is a property, it's a method, and methods have parameters!  There must be a way to use this method to do what I need.  Here's my knowledge on the Comment.Text method
  • Comment.Text works only when a range.Comment exist....or in VBA-speak range.Comment Is Not Nothing.  That makes the lead-in to using .Comment.Text "If range.Comment Is Nothing Then .AddComment."
  • Text:  This parameter is the in-and-out gate.  It is the only 1 of the three parameters used when using range.Comment.Text to show the comment.
    • Read what's in the comment:  x = .Comment.Text.  This is useful when putting Comments into cells or grabbing a user's comments for processing instructions, or displaying them through MsgBox or UserForms
    • Add to the existing comment: . Comment.text = x.  This sounds simple, but the other two parameters get it to do what you need.
  • Start: As implied above, Start has no effect when specified while using .Comment.Text to see what;'s in range.Comment.Shape.DrawingObject.Text. Surprisingly, specifying it didn't generate an error when executing "MsgBox Prompt:=range.Comment.Text(Start:=5)".  The whole of the Comment was displayed.

    Start establishes where the new text will begin.  If not specified, the new comment will Start at the first character.  Therefore, if you want to append the new comment, then Start:=Len(range.Comment.Text).
  • Overwrite.  Do NOT believe MSDN notes on this one.  (They're not perfect, neither am I.)  The Default is True and the only setting that doesn't error-out is "False".  Instead of belaboring here, that will be a different post.

Code and more to follow!