Here is code somebody else posted in this newsgroup for generating a list of
the formulas in a workbook.
Public Sub doFormula()
Dim bSaved As Boolean
Dim sWS As String
'Displays a list of formulae and named ranges used in
the workbook
Dim wks As Worksheet
Dim bHasFormula As Boolean
Dim i As Integer
Dim Cell As Range
'Switch off screen updating to improve performance
Application.ScreenUpdating = False
'UnprotectWS
With ActiveSheet
'Unprotect and clear the worksheet
.UsedRange.Delete xlShiftUp
bHasFormula = False
Range("A1").Value = "Worksheet"
Range("B1").Value = "Formula"
Range("C1").Value = "Range"
Range("D1").Value = "Protected"
Range("A1
1").Font.Bold = True
'Check if the workbook contains any formulae
For Each wks In ThisWorkbook.Worksheets
If wks.UsedRange.HasFormula = False Then
Else
bHasFormula = True
Exit For
End If
Next wks
If bHasFormula = False Then
'If false display message in cell C1
.Range("B2").Value = "This Workbook contains
no formulae."
i = 2
Else
'If true build the formulae list
i = 2
'Check each cell on each sheet for formulae
For Each wks In ThisWorkbook.Worksheets
For Each Cell In wks.UsedRange.Cells
If Cell.HasFormula = True Then
'If the cell has a formula record
the worksheet
'name, cell address and cell formula
.Cells(i, 1).Value = wks.Name
.Cells(i, 2).Value = "'" &
Cell.Formula
.Cells(i, 3).Value = Cell.Address
(RowAbsolute:=False, ColumnAbsolute:=False)
.Cells(i, 4).Value = Cell.Locked
'Increment the row counter
i = i + 1
End If
Next Cell
Next wks
End If
i = i + 2
'Check if the workbook contains Names
.Cells(i, 1).Value = "Name"
.Cells(i, 1).Font.Bold = True
.Cells(i, 2).Value = "Refers to"
.Cells(i, 2).Font.Bold = True
If ThisWorkbook.Names.Count > 0 Then
'If true display a list of Names
.Cells(i + 1, 1).ListNames
Else
.Cells(i + 1, 2).Value = "This Workbook
contains no named ranges. "
End If
'Format the list
With .Columns("A:A")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
End With
With .Columns("B:B")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.ColumnWidth = 70
.WrapText = True
.Cells.EntireRow.AutoFit
End With
Range("A1").Select
End With
'Switch screen updating back on
Application.ScreenUpdating = True
Exit Sub
End Sub
--
RMC,CPA
I have written a program using Excel 2000 and am trying to
get it copyrighted. The Copyright office wants the source
code. How do I get that? Is there a way to print the
formulas that I developed? Thanks
Steve
