列出工作簿中的所有公式

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 help understand the file setup, use the following macros to list all formulas in workbook.

如果您正在处理复杂的Excel文件，或者要接管别人构建的文件，可能很难理解它们如何组合在一起。 为了帮助理解文件设置，请使用以下宏列出工作簿中的所有公式。

Excel has formula arrow, to show dependent cells, but that can get confusing, if there are lots of formula cells.

Excel具有公式箭头，以显示相关单元格，但如果有很多公式单元格，则可能会造成混淆。

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.

您也可以使用Ctrl +`快捷键在工作表上查看公式。 并且，如果您在工作簿中打开另一个窗口，则可以同时查看公式和结果 。

列出公式的代码 (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_".

在下面的示例代码中，为每个包含公式的工作表创建一个新表。 新工作表以原始工作表命名，前缀为“ 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.

在公式列表工作表中，有一个ID列，在对另一列进行排序后，可用于将列表恢复为原始顺序。

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

也有带有工作表名称，公式的单元格，公式和R1C1格式的公式的列。

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

下载样本文件 (Download the Sample File)

To download the sample file, please visit the Sample Files page on the Contextures website. In the UserForms, VBA, Add-Ins section, look for UF0019 – Formula Info List.

要下载示例文件，请访问Contextures网站上的“示例文件”页面。 在“ 用户窗体，VBA，加载项”部分中 ，查找“ UF0019 –公式信息列表” 。

The file is zipped, and in Excel 2007 / 2010 format. Enable macros if you want to test the code.

该文件已压缩，格式为Excel 2007/2010。 如果要测试代码，请启用宏。

翻译自: https://contexturesblog.com/archives/2012/09/27/list-all-formulas-in-workbook/