Tuesday, February 26, 2013

Excel Comments, Part 2

It was mentioned in Excel Comments, Part 1, that MSDN seems wrong on this topic.  Following is the text of what I submitted to MSDN (with a few edits) regarding the Overwrite parameter.

Multiple articles describe the default for the Overwrite parameter of the range.Comments.Text method as"False".  This does not seem to be a true statement.

Assume the Comment at rngC is "123456789"

  • Executing  "rngC.Comment.Text Text:="ABC", Start:=4" results in Comment of "123ABC"
  • Executing "rngC.Comment.Text Text:="ABC", Start:=4, Overwrite:=False" results in Comment of "123ABC456789"

Furthermore, executing "rngC.Comment.Text Text:="ABC", Start:=4, Overwrite:=True" results in an error,  (Run-time error '5': Invalid procedure call or argument.)  Noticing that "Overwrite" is a variant, tried the following but encountered the same error.

Dim ow as variant
rngC.Comment.Text T:="ABC", Start:=4, Overwrite:=ow"

While this last point may sound trivial, the result is longer and therefore more difficult to maintain code.  It is not uncommon for preceding code to establish how a parameter should be set.  For example, if the preceding code included a Checkbox in a UserForm for overwriting the current Comment, then rather than simply passing the boolean value of the checkbox through Overwrite, the VBA module (sub or Function) would need to have an if-then-else, with the Then and Else section having the same code except for the "Overwrite:=" parameter.

*** Desired Code
rngC.Comment.Text T:=frm1.tbxComment, Start:=iif(frm1.ckbxAppend,len(rngC.Comment.text) + 1,0), Overwrite:=frm1.ckbxOverwrite

*** Code needed since cannot set Overwrite parameter to default
If ckboxfrm1.ckbxOverwrite Then
   rngC.Comment.Text T:=frm1.tbxComment, Start:=iif(frm1.ckbxAppend,len(rngC.Comment.text) + 1,0)
   rngC.Comment.Text T:=frm1.tbxComment, Start:=iif(frm1.ckbxAppend,len(rngC.Comment.text) + 1,0), Overwrite:=False
End If