Excel

List all formulas in a workbook on a separate worksheet

Ease of Use

Easy

Version tested with

97, 2000, 2003 

Submitted by:

Glaswegian

Description:

This code creates a list all formulas in a workbook. A new worksheet is added and the formulas, including the sheet name and cell address, are listed in Columns A, B and C. 

Discussion:

It can be difficult to keep track of all the formulas within a large workbook. This code will create a list of all the formulas, along with the sheet name and cell address. This can be useful if someone needs to review the formulas used or if you just need to find one particular formula or a set of formulas. A new worksheet is created, named by the user via an Input Box, and headings added. The new sheet is then excluded from the loop. UsedRange and SpecialCells are used to reduce the need to search every cell on each sheet. Once a cell with a formula is found, the code removes the equals sign (=) from the formula to allow the details to be placed in the new sheet without a calculation taking place. The sheet name and cell address are then added as well. 

Code:

instructions for use

			

Option Explicit Sub ListAllFormulas() Dim sht As Worksheet Dim shtName Dim myRng As Range Dim newRng As Range Dim c As Range ReTry: shtName = Application.InputBox("Choose a name for the new sheet to list all formulas.", "New Sheet Name") 'the user decides the new sheet name If shtName = False Then Exit Sub 'exit if user clicks Cancel On Error Resume Next Set sht = Sheets(shtName) 'check if the sheet exists If Not sht Is Nothing Then 'if so, send message and return to input box MsgBox "This sheet already exists" Err.Clear 'clear error Set sht = Nothing 'reset sht for next test GoTo ReTry 'loop to input box End If Worksheets.Add.Move after:=Worksheets(Worksheets.Count) 'adds a new sheet at the end Application.ScreenUpdating = False With ActiveSheet 'the new sheet is automatically the activesheet .Range("A1").Value = "Formula" 'puts a heading in cell A1 .Range("B1").Value = "Sheet Name" 'puts a heading in cell B1 .Range("C1").Value = "Cell Address" 'puts a heading in cell C1 .Name = shtName 'names the new sheet from InputBox End With For Each sht In ActiveWorkbook.Worksheets 'loop through the sheets in the workbook If sht.Name <> shtName Then 'exclude the sheet just created Set myRng = sht.UsedRange 'limit the search to the UsedRange On Error Resume Next 'in case there are no formulas Set newRng = myRng.SpecialCells(xlCellTypeFormulas) 'use SpecialCells to reduce looping further For Each c In newRng 'loop through the SpecialCells only Sheets(shtName).Range("A65536").End(xlUp).Offset(1, 0).Value = Mid(c.Formula, 2, (Len(c.Formula))) 'places the formula minus the '=' sign in column A Sheets(shtName).Range("B65536").End(xlUp).Offset(1, 0).Value = sht.Name 'places the sheet name containing the formula in column B Sheets(shtName).Range("C65536").End(xlUp).Offset(1, 0).Value = Application.WorksheetFunction.Substitute(c.Address, "$", "") 'places the cell address, minus the "$" signs, containing the formula in column C Next c End If Next sht Sheets(shtName).Activate 'make the new sheet the activesheet ActiveSheet.Columns("A:C").AutoFit 'autofit the data Application.ScreenUpdating = True End Sub

How to use:

  1. Press Alt+F11 from Excel to open the VB Editor.
  2. Click on the Project(Workbook) name in the left pane.
  3. Click on ?Insert? on the Menu Bar
  4. Select ?Module? from the list. The new Module will open.
  5. Paste the code into the right pane of the Module.
  6. Click on Alt+F11 to return to Excel
 

Test the code:

  1. Can be run in any workbook or from your Personal.xls file.
  2. Press Alt+F8 and select the macro name from the list or from the menu bar, click
  3. Tools > Macro > Macros and select the macro name from the list.
  4. Or create a button and add the code to the button.
 

Sample File:

Sample Formulas.zip 15.22KB 

Approved by mdmackillop


This entry has been viewed 338 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express