A User-Define Function Can’t Change The Worksheet. Oh Yeah?
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:
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:
Excel is a complex program, and has been around for a long time. Consequently, it has many obscure nooks and crannies to discover. Some of them are described here.
Keep in mind that some versions of Excel are odder than others. In other words, the things described here may not apply to all versions of Excel.
All Odd Stuff
Browse Oddities by Category
Seen Something Odd?
If you've discovered something weird about Excel, let me know.