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.
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.
- Create all the variables needed – you should always declare your variables.
- I am using basic Error Handling – see the two labels at the bottom of the code – HandleExit and HandleError.
- Turn things off that slow macros down.
- 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.
- If the sheet doesn’t exist then create it and name it.
- Set up the headings for the sheet.
- Go through all the sheets.
- Go through all the comments in the sheet being examined. ws.Comments is a collection of all the sheet’s comments.
- 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.
- Increment the lNextRow variable this controls the list being created on the Comment_Listing sheet.
- Amend the column width and row height on the Comment_Listing sheet.
- Reset the settings back to defaults
- Set all the object variables to Nothing – this is best practice programming and reduces memory leakage.
- Error handling message.
You can copy the code from the listing below and it is also in the file download link below.
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