Formulas List

[Earl]

How do I print a list of all formulas contained in a worksheet?

 

[Myrna Larson]

Tools/Options, and on one of the tabs there's a box to display formulas. Click
that, do your printing, then change the setting back. The shortcut key for
this is CTRL+` (the single back-quote to the left of the 1 key on the upper
row of the keyboard).

 

[Harlan Grove]

How do I print a list of all formulas contained in a worksheet?

Using a macro would be one way. Here's one that prints just formulas in both
A1 and R1C1 formats from the active workbook to text files.


Sub fl()
Dim fd As Variant, ws AS Worksheet, c As Range

fd = Freefile
Open ActiveWorkbook.Fullname & ".fl.txt" For Output As #fd
On Error GoTo CleanUp

For Each ws In ActiveWorkbook.Worksheets
Print #fd, ws.Name

For Each c In ws.UsedRange
If c.HasFormula Then
Print #fd, c.Address(0,0)
Print #fd, "", c.Formula
Print #fd, "", c.FormulaR1C1
Print #fd, ""
End If
Next c

Print #fd, "------------"
Print #fd, ""
Next ws

CleanUp:
Close #fd
End Sub

 

[Jack Sons]

Harlan,

I found the resulting text file "somewhere", in just a Excel sub directory.
I could not find any reason why it was saved in that particular directory.
Would anyhow never have expected the "txt" file to be saved in an Excel
directory rather than in a Word directory.

This is not convenient, could you please revise the code in a way that the
resulting file will be a map on screen, leaving it to me wether I like to
save it somewhere or not.

Jack Sons
The Netherlands

 

[Gord Dibben]

Earl

See John Walkenbach's site for his ListFormulas macro.

http://j-walk.com/ss/excel/tips/tip37.htm

"Most users have discovered that Excel has an option that lets you display
formulas directly in their cells: Choose Tools Options, click the View tab,
and select the Formulas checkbox. However, Excel doesn't provide a way to
generate a concise list of all formulas in a worksheet. The VBA macro below
inserts a new worksheet, then creates a list of all formulas, cell addresses
and their current values."

Gord Dibben Excel MVP

 

[Dave Peterson]

Harlan's line of code:

Open ActiveWorkbook.Fullname & ".fl.txt" For Output As #fd

used the same folder that contained the excel file.

If you hadn't saved the file, it would save to the current directory of the
activedrive. (Probably the Default file location if you haven't been changing
things.)

You could put it pretty much anywhere you wanted:

Open "C:\my documents\" & activeworkbook.name & ".fl.txt" For Output As #fd

(notice that .fullname was replaces with .name)

JE McGimpsey does something equivalent using a worksheet:
http://mcgimpsey.com/excel/listfunctions.html

 

[Jack Sons]

Dave,

What I want is the file with the formulas just on screen, not yet saved at
all. Probably I want to look at it and most likely I do not want to save it
anyhow.
Is that possible?

Jack.

 

[Dave Peterson]

Could it be done? Yeah, I bet so. But I wouldn't do it that way.

How about an alternative--just use the windows temp folder and you can clean it
up when you're doing regularly scheduled housekeeping:

Option Explicit
Dim myTextFileName As String
Sub runBoth()
Call fl
If Dir(myTextFileName) <> "" Then
Shell "start " & myTextFileName
'Application.Wait Now + TimeSerial(0, 0, 2)
'Kill myTextFileName
End If
End Sub
Sub fl()
Dim fd As Variant, ws As Worksheet, c As Range

myTextFileName = Environ("temp") & "\" & ActiveWorkbook.Name & ".fl.txt"

fd = FreeFile
Open myTextFileName For Output As #fd
On Error GoTo CleanUp

For Each ws In ActiveWorkbook.Worksheets
Print #fd, ws.Name

For Each c In ws.UsedRange
If c.HasFormula Then
Print #fd, c.Address(0, 0)
Print #fd, "", c.Formula
Print #fd, "", c.FormulaR1C1
Print #fd, ""
End If
Next c

Print #fd, "------------"
Print #fd, ""
Next ws

CleanUp:
Close #fd
End Sub

If you want to save the output, you can save it from notepad (or whatever
program you have associated with .txt files.)

I came back and added this:

Application.Wait Now + TimeSerial(0, 0, 2)
Kill myTextFileName

(and commented them out.)

in Win98 and using Notepad, I could delete that text file while it was open in
notepad.

I'm not sure if that'll work in all versions of windows or text editors. (I'm
kind of surprised that it worked at all.)