I was watching a video a while back and some Excel experts were lamenting the lack of a documentation standard in Excel. They mentioned that the cell comments system could be used for documentation, but there was no way to centralise all the comments. Well, I have written a macro to do just that.

Macros can add functionality to Excel. Cell comments allow you to have a note on a cell. Press Shift+ F2 to create a cell comment.

If you are unsure how to use macros check out this post.

Cell comments are like post it notes for cells. They can also be used for instructions or information on the cell eg an input cell may have a comment explaining how to run a report that contains the entry required.

When you print out a sheet you can include a list of the cell comments. See Print Settings below.

But there is no way to centralise all the cell comments from all the sheets in a file. That’s what this macro does. It creates a report on the cell comments, plus the formulas and value and formatting.

The macro is shown below in an image. It is also included at the bottom of the post so you can copy the code. I won’t explain all the code lines, just sections of it.

Extra features

I am going to include this macro in a new Macro webinar session I am writing – the session’s version will include extra features like a hyperlink to the cell and a category option. The webinar session will include two other reporting macros that are useful for documentation, auditing and debugging.  The session will also involve creating an add-in for the three macros.

Explanation of the above code.

1. Create all the variables needed – you should always declare your variables.
2. I am using basic Error Handling – see the two labels at the bottom of the code – HandleExit and HandleError.
3. Turn things off that slow macros down.
4. Go through all the sheets to see if the report has been created in the past – sheet is named Comment_Listing. There is shorter code to do this, but I prefer this method. If it exists clear the contents and the formats.
5. If the sheet doesn’t exist then create it and name it.
6. Set up the headings for the sheet.
7. Go through all the sheets.
8. Go through all the comments in the sheet being examined. ws.Comments is a collection of all the sheet’s comments.
9. Capture all the details for each comment: the sheet name, the author of the comment; the cell reference; the formula; the cell result; the format and the comment without with author name.
10. Increment the lNextRow variable this controls the list being created on the Comment_Listing sheet.
11. Amend the column width and row height on the Comment_Listing sheet.
12. Reset the settings back to defaults
13. Set all the object variables to Nothing – this is best practice programming and reduces memory leakage.
14. Error handling message.

You can copy the code from the listing below and it is also in the file download link below.

note_examples

Sub List_Comments_Basic()
' This macro works on the active file and creates a new sheet called Comment_Listing
' or uses an existing sheet with that name - it creates a table of all the cell
' comments in the file and lists the cell, the formula, the result and the sheet name
 
Dim oC As Comment
Dim ws As Worksheet
Dim wsNotes As Worksheet
Dim lNextRow As Long
Dim strComment As String
 
On Error GoTo HandleError
 
With Application
   .ScreenUpdating = False
   .Calculation = xlCalculationManual
   .CutCopyMode = False
End With
 
'find if the Comment_Listing sheet already exists
For Each ws In Worksheets
   If ws.Name = "Comment_Listing" Then
      Set wsNotes = ws
         With wsNotes.Cells
            .ClearContents
            .ClearFormats
         End With
         Exit For
   End If
Next ws
 
If wsNotes Is Nothing Then
   Set wsNotes = Worksheets.Add
   wsNotes.Name = "Comment_Listing"
End If
 
'set listing headings
wsNotes.Range("A1:F1") = _
Array("Sheet Name", "Cell Address", "Author", "Comment", "Cell Formula", _
"Cell Result/Format")
wsNotes.Range("A1:F1").Font.Bold = True
 
lNextRow = 2
 
For Each ws In Worksheets 'go thru all sheets
 
   For Each oC In ws.Comments 'go thru all comments in the sheet
 
      wsNotes.Cells(lNextRow, 1) = ws.Name 'sheet name
      wsNotes.Cells(lNextRow, 2) = oC.Parent.Address 'cell address
      wsNotes.Cells(lNextRow, 3) = oC.Author 'author
      'the comment itself for column 4 is captured below
 
      wsNotes.Cells(lNextRow, 5) = _
         "'" & ws.Range(oC.Parent.Address).Formula 'Formula
 
      'capture the cell value + its format
      ws.Range(oC.Parent.Address).Copy
      wsNotes.Cells(lNextRow, 6).PasteSpecial Paste:=xlPasteValues 'Paste Formula result
      wsNotes.Cells(lNextRow, 6).PasteSpecial Paste:=xlPasteFormats 'Paste cell Format
 
      Application.CutCopyMode = False
 
      'remove author from comment
      strComment = Trim(Replace(oC.Text, oC.Author & ":" & vbLf, ""))
 
      wsNotes.Cells(lNextRow, 4) = strComment 'comment
 
      lNextRow = lNextRow + 1
 
   Next oC
 
Next ws
 
wsNotes.Columns.AutoFit
wsNotes.Rows.AutoFit
 
HandleExit:
 
With Application
   .ScreenUpdating = True
   .Calculation = xlCalculationAutomatic
   .CutCopyMode = False
End With
 
'set objects to nothing
Set oC = Nothing
Set ws = Nothing
Set wsNotes = Nothing
 
Exit Sub
 
HandleError:
 
MsgBox "Macro stopped - Error encountered", vbOKOnly + vbCritical, "ERROR"
GoTo HandleExit
 
End Sub