如果我們要查看一個(gè)工作表中的所有公式，可以用VBA來(lái)實(shí)現(xiàn)。下面的VBA代碼可以在工作簿中插入一個(gè)新工作表，并在其中列出指定工作表中的所有公式、公式所在單元格及其值。使用方法是將VBA代碼放入標(biāo)準(zhǔn)模塊中，選擇一個(gè)工作表，然后執(zhí)行代碼。
Sub ListFormulas()
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer
‘創(chuàng)建Range對(duì)象
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)
‘沒(méi)有找到公式
If FormulaCells Is Nothing Then
MsgBox "當(dāng)前工作表中沒(méi)有公式！"
Exit Sub
End If
‘增加一個(gè)新工作表
Application.ScreenUpdating = False
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "“" & FormulaCells.Parent.Name & "”表中的公式"
‘列標(biāo)題
With FormulaSheet
Range("A1") = "公式所在單元格"
Range("B1") = "公式"
Range("C1") = "值"
Range("A1:C1").Font.Bold = True
End With
‘讀取公式，同時(shí)在狀態(tài)欄中顯示進(jìn)度。
Row = 2
For Each Cell In FormulaCells
Application.StatusBar = Format((Row – 1) / FormulaCells.Count, "0%")
With FormulaSheet
Cells(Row, 1) = Cell.Address _
(RowAbsolute:=False, ColumnAbsolute:=False)
Cells(Row, 2) = " " & Cell.Formula
Cells(Row, 3) = Cell.Value
Row = Row + 1
End With
Next Cell
‘調(diào)整列寬
FormulaSheet.Columns("A:C").AutoFit
Application.StatusBar = False
End Sub
