Excel 2003: Print or Export the Formulas Used in Each Cell
I had a spreadsheet in Excel 2003. (I suspect the approach used here would also work in other versions of Excel, but I have not tried it.) I wanted to print out the formulas used in each cell. I did a couple of searches and wound up in a thread where they were advising me to use a macro for this purpose. The steps I used to set up the macro were similar to those that I had used for another Excel macro:
- Close all Excel files other than the one you're working on.
- Go into Tools > Macro > Visual Basic Editor > Insert > Module.
- Copy and paste macro text (see below) into the window.
- Go to File > Close and return to Microsoft Excel.
- In this case, I used the macro by going into Tools > Macro > Macros and running the ListFormulas macro.
Sub ListFormulas()
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer
' 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").AutoFit
Application.StatusBar = False
End Sub (Note that the format of this blog may wrap some lines. Copying and pasting may yield better results than retyping.) The author named in that code, John Walkenbach, provided this code and also offered a Power Utility Pak ($40) that contained this and other tools. I had installed a couple of freeware utility collections -- ASAP Utilities and Morefunc -- and I hardly ever used them. I checked the list of tools in ASAP Utilities, just in case, but didn't find anything quite along these lines. A quick glance revealed no list of Morefunc utilities.When I ran the macro (step 5, above), it seemed to hang my machine. I was using a fairly large spreadsheet -- I probably should have tried it on something smaller -- but instead I went to bed. I didn't know how long it took, but it worked. When I awoke, it had created a new worksheet (i.e., a new tab at the bottom of the spreadsheet), with three columns: Address (e.g., F2), Formula (e.g., =C2), and Value (e.g., 17).
1 comments:
Thanks for this, it did exactly what I needed!
John
Post a Comment