Identify functions used in Workbook

[MTBer]

I'm trying to identify the Excel functions used within a workbook, is there an easy way to list them out on a worksheet, or am I left to manually checking each cell. Any ideas on how I can achieve this with VBA?

cheers,
Paul

 

[Richard Schollar]

Try using the following code (I think I originally got this from someone from this board, but I'm afraid I don't remember who):

Sub ListFormulas()
    
'Lists formula's in a worksheet
'Creates new worksheet, lists Address,Link & value
        
    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
    
    On Error GoTo 0
    
'   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

This will list all the formulas used in your sheet, from which you can identify individual functions.

Best regards

Richard