Creating a list of Excel formulas

[kansaskannan]

Is there a way to create a list of formulae used in a (Excel 2007)
worksheet?
I can paste range names, but would like to do the same for formulae.
Thank you for any suggestions.

 

[Gord Dibben]

One method...........CTRL + ` to switch to formula view.

Second method.................VBA to place all formulas on a new worksheet.

Sub ListFormulas()
'from John Walkenbach
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer
Dim ws As Worksheet
' Create a Range object for all formula cells
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)

' Exit if no formulas are found
If FormulaCells Is Nothing Then
MsgBox "No Formulas."
Exit Sub
End If

' Add a new worksheet
Application.ScreenUpdating = False
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name

' Set up the column headings
With FormulaSheet
Range("A1") = "Address"
Range("B1") = "Formula"
Range("C1") = "Value"
Range("A1:C1").Font.Bold = True
End With

' Process each formula
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

' Adjust column widths
FormulaSheet.Columns("A:C").Cells.WrapText = True ''AutoFit
Application.StatusBar = False
End Sub


Gord Dibben MS Excel MVP

 

[kansaskannan]

Thank you very much, for the courtesy and promptness of your response.

 

[Pete_UK]

Hey, that's a very nice way of feeding back.

Why can't everyone be so polite ? (rhetorical question !!)

Pete

 

[David McRitchie]

You are probably looking for a summary, but you can use Ctrl+~ (or whatever is to
left of the "1" number row key) to show formulas, and you can install "GetFormula"
as described in http://www.mvps.org/dmcritchie/excel/formula.htm to show the
formula used in another cell.

Would suggest also installing "GetFormula" into the right-click menu to help create
the formula as described in
http://www.mvps.org/dmcritchie/excel/rightclick.htm

Both can still be done in Excel 2007 and you can avoid referring to the ribbon
to use it if you implement both parts.

--
HTH,
David McRitchie
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

<kansaskannan wrote...