Viewing Formulas

[Orgelizer]

I am trying to troubleshoot a multi-page spreadsheet that someone else
messed up, and I'd like to view the formulas -- in their entirety -- to
do that. Using cntr + ` (grave accent) shows formulas in cells, but if
the formulas in the cells happen to be smaller than the cell width,
they can't be completely seen.

Lotus and Quattro had an option of printing out the formulas
separately, as in:

A1: This is a test
A2: +b4/e6
A3: @if($c$12<>"HELLO",4,"HI")
etc.

Is there anyway to get that with Excel? I'm using 2003.

Thanks in advance for your replies.

 

[Gord Dibben]

John Walkenbach's macro will give you the formulas, addresses and current values
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