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.
(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.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
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