#Excel Worksheet UDF that Adds a Comment to Any Cell by David Hager

4 Replies

There was a lot of interest in my post on modifying a shape with a worksheet UDF.

https://dhexcel1.wordpress.com/2017/04/19/excel-modifying-shapes-from-an-udf-in-a-worksheet-cell-by-david-hager/

The original idea was posted in 2007. I seem to remember, though, that the use of a UDF to modify cells occurred before that time. The initial discovery was that a UDF could add a cell comment to ANY cell. I can’t find the original reference, but this technique was last documented at:

http://www.listendata.com/2013/04/excel-udf-dependent-cell-comment.html

I have modified the the UDF shown in that article to add a timestamp feature.

Function AddComment(rng As Range, str As String) As String

If Not rng.Comment Is Nothing Then rng.Comment.Delete

TimeStamp = Date & ” ” & Time

If Len(str) Then rng.AddComment.Text str & ” ” & TimeStamp

rng.Comment.Visible = True

End Function

In the example workbook, I entered the AddComment function in cell D6, but the range argument can point to any cell. In fact “range formulas” can also be used.

The INDEX, OFFSET and INDIRECT Excel functions all return ranges, so any formulas built with these functions can be used in a UDF where a range argument is required. The following example uses the INDEX function.

=AddComment(INDEX(NumRange,MATCH(MAX(NumRange),NumRange,0)),”MAX value in NumRange”)

where NumRange is defined as =OFFSET(A$1,,,COUNTA($A:$A),) ‘auto-expanding range

In this example, the formula INDEX(NumRange,MATCH(MAX(NumRange),NumRange,0)) returns the range of the cell containing the max value of NumRange, and as such it can be used in the first argument of the UDF. So, as numbers are added to column A as shown in the figure

addcomment1

the function will add a timestamped comment to any cell in that range that is the max value.

Obviously, there are numerous and more complex examples that can be built using this technique. I hope that you will find this useful in your projects.

The example file can be downloaded here.

AddComment