Tom’s Tutorials for Excel: Count Your Workbook’s Formulas

Tom’s Tutorials for Excel: Count Your Workbook’s Formulas

Here is a macro that goes sheet by sheet through your workbook, and shows you a Message Box with the count how many formulas (if any) are on each sheet. Substitute your sheet’s actual password with the sample “YourPassword” reference in the code below:

Sub CountFormulas()
Application.ScreenUpdating = False
Dim SFC#, TFC#, RI$, WS As Worksheet
SFC = 0: TFC = 0: RI = ""
For Each WS In Worksheets
'optional if your sheets are protected
'WS.Unprotect ("YourPassword")
On Error Resume Next
SFC = WS.Cells.SpecialCells(3).Count
If Err.Number <> 0 Then
Err.Clear
SFC = 0
End If
TFC = TFC + SFC
RI = RI & "Formula count in ''" & WS.Name & "'': " & _
Format(SFC, "#,##0") & vbCrLf
'optional reprotect your sheets
'WS.Protect ("YourPassword")
Next WS
Application.ScreenUpdating = True
MsgBox RI & vbCrLf & "Total formulas in " & _
ThisWorkbook.Name & ": " & _
Format(TFC, "#,##0"), , "Workbook formula count"
End Sub