Macro to List All Formulas in Workbook

http://blog.contextures.com/archives/2012/09/27/list-all-formulas-in-workbook/

List All Formulas in Workbook

BY DEBRA DALGLEISH · SEPTEMBER 27, 2012





If you’re working on a complicated Excel file, or taking over a file that someone else built, it can be difficult to understand how it all fits together.

 

To get started, you can see where the formulas and constants are located, and colour code those cells.

 

View Formulas on the Worksheet

You can also view the formulas on a worksheet, by using the Ctrl + ` shortcut. And if you open another window in the workbook, you can view formulas and results at the same time.



Code to List Formulas

For more details on how the calculations work, you can use programming to create a list of all the formulas on each worksheet.

In the following sample code, a new sheet is created for each worksheet that contains formulas. The new sheet is named for the original sheet, with the prefix "F_".

In the formula list sheet, there is an ID column, that you can use to restore the list to its original order, after you’ve sorted by another column.

There are also columns with the worksheet name, the formula’s cell, the formula and the formula in R1C1 format.

 

Copy the following code to a regular module in your workbook.
Sub ListAllFormulas()
'print the formulas in the active workbook
Dim lRow As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim wsNew As Worksheet
Dim c As Range
Dim rngF As Range
Dim strNew As String
Dim strSh As String
On Error Resume Next
Application.DisplayAlerts = False

Set wb = ActiveWorkbook
strSh = "F_"

For Each ws In wb.Worksheets
  lRow = 2
  
  If Left(ws.Name, Len(strSh)) <> strSh Then
    Set rngF = Nothing
    On Error Resume Next
    Set rngF = ws.Cells.SpecialCells(xlCellTypeFormulas, 23)
    If Not rngF Is Nothing Then
      strNew = Left(strSh & ws.Name, 30)
      Worksheets(strNew).Delete
      Set wsNew = Worksheets.Add
      With wsNew
        .Name = strNew
        .Columns("A:E").NumberFormat = "@" 'text format
        .Range(.Cells(1, 1), .Cells(1, 5)).Value _
            = Array("ID", "Sheet", "Cell", "Formula", "Formula R1C1")
        For Each c In rngF
          .Range(.Cells(lRow, 1), .Cells(lRow, 5)).Value _
            = Array(lRow - 1, ws.Name, c.Address(0, 0), _
              c.Formula, c.FormulaR1C1)
          lRow = lRow + 1
        Next c
        .Rows(1).Font.Bold = True
        .Columns("A:E").EntireColumn.AutoFit
      End With 'wsNew
      Set wsNew = Nothing
    End If
  
  End If
Next ws
  
Application.DisplayAlerts = True

End Sub

Code to Remove Formula Sheets

In the List Formulas code, formula sheets are deleted, before creating a new formula sheet. However, if you want to delete the formula sheets without creating a new set, you can run the following code.
Sub ClearFormulaSheets()
'remove formula sheets created by
'ShowFormulas macro
Dim wb As Workbook
Dim ws As Worksheet
Dim strSh As String
On Error Resume Next
Application.DisplayAlerts = False

Set wb = ActiveWorkbook
strSh = "F_"

Set wb = ActiveWorkbook
  For Each ws In wb.Worksheets
    If Left(ws.Name, Len(strSh)) = strSh Then
      ws.Delete
    End If
  Next ws
  
Application.DisplayAlerts = True

End Sub