Capturing Formulas in Excel

Unfortunately, it is easy to delete a formula. Of course, there is always Undo but if the file has been closed getting the formula back is difficult unless…..

One technique to “save” the formula is to copy cell formulas to a cell Note or Comment.

Here is an automated way to do it.

Macro solution

The macro below works with the current range selection. It saves each cell’s formula to a cell comment(note). It won’t overwrite any existing notes. If no note exists it creates a new note. The formula is appended to the bottom of any existing notes.

Sub AddFormulaToNote()

    'adds the selected cell's formula to a cell note

    Dim c

    Dim str As String

    Dim strComment  As String

On Error Resume Next

If TypeName(Selection) <> "Range" Then Exit Sub

For Each c In Selection

    str = ""

    strComment = ""

    If c.HasFormula Then

        str = c.Comment.Text                    'this may generate an error but it is ignored

        strComment = c.CommentThreaded.Text     'this may generate an error but it is ignored

        If str <> "" Then

            c.Comment.Text Text:=str & Chr(10) & "Cell Formula:" & Chr(10) & c.Formula

        ElseIf strComment <> "" Then

            c.CommentThreaded.Text Text:=strComment & Chr(10) & "Cell Formula:" & Chr(10) & c.Formula

        Else

            c.AddComment

            c.Comment.Text Text:="Cell Formula:" & Chr(10) & c.Formula

        End If

    End If

Next c

End Sub

Notes vs Comments

Notes in Excel now used to be called comments. Comments now are threaded comments. So VBA calls Notes a Comment to be backward compatible. Comments now in VBA are called “CommentThreaded”

The code first checks the selection is a range. If it isn’t the macro stops.

Then it goes through each cell in the selection.

It sets both string variables to blank.

If there is a formula it then checks to see if there is an existing cell note or comment. Setting the variables to the note or comment text could generate an error. It is ignored.

If a comment or a note exists the formula is appended to it.

Note the Chr(10) command is a line break.

If there isn’t a note or a comment a new note is create and the formula inserted.