A User-Define Function Can’t Change The Worksheet. Oh Yeah?

Category: VBA Weirdness | [Item URL]

VBA programmers know that they can create custom functions that can be used in worksheet formulas. A basic rule is:

A function used in a formula can return a value. It cannot make any changes to the workbook.

There is at least one exception to this rule. A VBA function used in a formula can change the text in a cell comment. The function below demonstrates.

Function ChangeComment(Rng1, Rng2)
   Rng1.Comment.Text Rng2.Text
End Function

If cell A1 contains a comment, the following formula will insert the contents of cell B1 into the comment for cell A1:

  =ChangeComment(A1,B1)

There may actually be a practical use for this. The function below returns the value of the cell it references, and also inserts the current date and time into the referenced cell's comment. This reflects the time at which the cell was last calculated.

Function TimeStamp(Rng1)
  TimeStamp = Rng1.Value
  Rng1.Comment.Text CStr(Now())
End Function

David Hager reports that the following Methods, when used in a custom function, can also make change to the workbook:

• Merge
• UnMerge
• AddComment
• ClearComments
• InsertIndent