Automate Finding Cell Errors with the 'Find Cell Errors' Utility
Published:
Browse All Articles > Automate Finding Cell Errors with the 'Find Cell Errors' Utility
By Jerry Paladino
![Cell Error Report]()
When all errors in a workbook have been resolved, a final run of FindCellErrors will eliminate the 'Error Cells+' worksheet and display a message indicating no errors were found in the workbook.
![No Errors Found]()
A sample file is attached with examples of all seven cell error conditions including errors located in hidden rows, hidden columns and on hidden worksheets. Please download the Sample File and give it a try.
The attached sample file has a copy of the FindCellErrors module and a friendly button on the 'Start Here' worksheet to run it. If you think the Find Cell Errors Utility could be useful please read on, there are instructions in the article to make it available for all your workbooks.
Sample-Cell-Errors.xls
1) Download the example file if you simply want to test the FindCellErrors utility. When you open the file you will need to select the enable macros button or place the file in a Trusted Location if you are using xl2007/2010. The file will open to the 'Start Here' sheet. Press the large blue button on the screen and the routine will check all the worksheets in the workbook for cell based errors. Once it has completed its scan of the workbook it will create the 'Cell Errors+' worksheet with the Error report. The hyperlinks will take you to each error in the workbook even if they are located in hidden row or columns. The worksheet 'Sheet 3' is a hidden worksheet and the cell comment will ask you to unhide the sheet so the hyperlink will function properly.
2) If it looks interesting, let's try it on one of your workbooks. You can run it against another workbook by leaving the Sample file open and also opening your workbook. From your open workbook press Alt-F8 to display the Macro dialog. Select the FindCellError macro and press the Run button. It will scan all worksheets in your workbook and if it finds errors it will create the Error Report. If not, a message will be displayed indicating no errors were found and the number of worksheets and cells that were scanned.
3) If you think you might use FindCellErrors on a regular basis you can add it to your Personal.xls file and it will be available every time you open Excel.
Now that the FindCellErrors utility is part of your Personal.xls file you can run it on any open workbook by pressing Alt-F8 and selecting the FindCellErrors macro in the Macro Dialog box. You can even set up a keyboard shortcut from the Macro Dialog. Just press the Options… button and enter a letter in the box. The macro will run when you press Ctrl+the letter you selected. Be careful not to select a letter for a keyboard shortcut that you use regularly such as Ctrl-C or Ctrl-V.
4) An alternative to the keyboard shortcut is to assign the macro to a toolbar button in xl2003 or to an icon on the QuickAccess Toolbar in xl2007/2010.
In xl2007, right click on the QuickAccess Toolbar and choose 'Customize Quick Access Toolbar'. In the 'Choose commands from' dropdown choose 'Macros' and from the list select 'Personal.XLS!FindCellErrors'. Press the ADD>> button in the middle of the two boxes. Press the 'Modify' button at the bottom of the right pane if you want to change the icon and the text displayed. Then press OK to accept the change and return to Excel.
In xl2003, to add a button to a toolbar and assign it to the FindCellErrors macro see: Add a button, menu, or command
You can now check any active workbook by pressing the button on the toolbar that is assigned to FindCellErrors. The source code is listed below.
---------------------
If you found the article helpful please click on the "YES" button after the question below.
This utility has been helpful to me and I hope you find an opportunity to use it as well.
If you didn't or otherwise want to vote No, please leave a comment and give me an
opportunity to respond. It will give me a chance to improve this article and others I
would like to write.
Thank You for Stopping by to Read the Article!
---------------------
Introduction
In Excel there is no built-in method to generate a report of all error codes in a worksheet let alone an entire workbook. It is also difficult to check all formulas, especially when rows, columns and worksheets can be hidden. The 'Find Cell Errors' routine presented in this article creates an index of all errors found in a workbook and presents them on a new sheet similar to this Error Report:
The Find Cell Errors Report
The Summary section of the report includes:
The number of worksheets scanned
The number of cells scanned
The number of errors found
The elapsed time the scan took
An 'Error Index' with a brief description of the seven cell based error condition and their likely causes
The Error section of the report indicates:
The worksheet where the error occurs
The cell reference to the worksheet error with a hyperlink to the error cell
A 'Hidden' indicator is provided if the cell is located in a hidden row (R), hidden column (C), hidden row/column (R/C), or if the cell is located on a hidden worksheet. Since a hyperlink will not transfer to a hidden worksheet, a comment is embedded in the hyperlink cell indicating the worksheet must be unhidden for the link to function properly.
The specific error found
The formula causing the error.
The error report will be located on a new worksheet named 'Error Cells+' which is added as the last sheet in the workbook being checked. In an Excel 2007 system the error section of the report will be formatted as an Excel Table with filters enabled to isolate specific sheets or errors if the numbers of line items is excessive. In an Excel 2003 or earlier system the report will be a static list of rows with AutoFilter enabled.
When all errors in a workbook have been resolved, a final run of FindCellErrors will eliminate the 'Error Cells+' worksheet and display a message indicating no errors were found in the workbook.
Using The FindCellErrors Code
A sample file is attached with examples of all seven cell error conditions including errors located in hidden rows, hidden columns and on hidden worksheets. Please download the Sample File and give it a try.
The attached sample file has a copy of the FindCellErrors module and a friendly button on the 'Start Here' worksheet to run it. If you think the Find Cell Errors Utility could be useful please read on, there are instructions in the article to make it available for all your workbooks.
Sample-Cell-Errors.xls
1) Download the example file if you simply want to test the FindCellErrors utility. When you open the file you will need to select the enable macros button or place the file in a Trusted Location if you are using xl2007/2010. The file will open to the 'Start Here' sheet. Press the large blue button on the screen and the routine will check all the worksheets in the workbook for cell based errors. Once it has completed its scan of the workbook it will create the 'Cell Errors+' worksheet with the Error report. The hyperlinks will take you to each error in the workbook even if they are located in hidden row or columns. The worksheet 'Sheet 3' is a hidden worksheet and the cell comment will ask you to unhide the sheet so the hyperlink will function properly.
2) If it looks interesting, let's try it on one of your workbooks. You can run it against another workbook by leaving the Sample file open and also opening your workbook. From your open workbook press Alt-F8 to display the Macro dialog. Select the FindCellError macro and press the Run button. It will scan all worksheets in your workbook and if it finds errors it will create the Error Report. If not, a message will be displayed indicating no errors were found and the number of worksheets and cells that were scanned.
3) If you think you might use FindCellErrors on a regular basis you can add it to your Personal.xls file and it will be available every time you open Excel.
From any workbook, press Alt + F11 to open the Visual Basic Editor (VBE)
Find and select the VBA Project - PERSONAL.XLS(B) in the left pane (Project Explorer). If you do not see the Project Explorer press Ctrl-R to display it. If you do not have a Personal.xls listed please follow the link below on "How do I create a Personal.xls(b) or Add-in"
From the menus, choose Insert-Module
Copy the code from the code box below and paste it into the right hand pane of the VBE
Save the Personal.xls file from the menu: File-Save or press the Save icon on the toolbar
Close the Visual Basic Editor and return to Excel
For more information on creating a Personal Macro Workbook, please see: How do I create a Personal.XLS(B) or Add-in
Now that the FindCellErrors utility is part of your Personal.xls file you can run it on any open workbook by pressing Alt-F8 and selecting the FindCellErrors macro in the Macro Dialog box. You can even set up a keyboard shortcut from the Macro Dialog. Just press the Options… button and enter a letter in the box. The macro will run when you press Ctrl+the letter you selected. Be careful not to select a letter for a keyboard shortcut that you use regularly such as Ctrl-C or Ctrl-V.
4) An alternative to the keyboard shortcut is to assign the macro to a toolbar button in xl2003 or to an icon on the QuickAccess Toolbar in xl2007/2010.
In xl2007, right click on the QuickAccess Toolbar and choose 'Customize Quick Access Toolbar'. In the 'Choose commands from' dropdown choose 'Macros' and from the list select 'Personal.XLS!FindCellErro
In xl2003, to add a button to a toolbar and assign it to the FindCellErrors macro see: Add a button, menu, or command
You can now check any active workbook by pressing the button on the toolbar that is assigned to FindCellErrors. The source code is listed below.
Option Explicit
Sub FindCellErrors()
'-----------------------------------------------------------------
' FindCellErrors
' Written by Jerry Paladino (ProdOps)
' 26-July-2010
'
' Searches the used range of all worksheets in the active workbook
' and creates a report worksheet named "Error Cells+" if it finds
' any cells with an Excel error condition. If no errors are found
' a dialog box is displayed indicating no error conditions found.
'-----------------------------------------------------------------
Dim wb As Workbook
Dim ws As Worksheet
Dim rngTest As Range, rngErrors As Range, rngCell As Range
Dim Start As Long, Finish As Long, errCnt As Long, cntr As Long
Dim shtcells As Long, ArryUBound As Long, wsCnt As Long
Dim wbCellsTot As Long, HyperCounter As Long
Dim NumArry As Variant
Dim StrArry() As String
Dim PauseTime, PauseTimeStart, PauseTimeStop As String, hddn As String
Dim myMsg As String, strComment As String, ErrRptMsg As String
Dim Response As String, Title As String, myErrMsg As String
On Error GoTo Problem
Start = Timer
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
.StatusBar = False
End With
Set wb = ActiveWorkbook
Set rngTest = Nothing
Set rngErrors = Nothing
' Delete the Error Report sheet if it exists in the workbook
If IsError(Evaluate("'Error Cells+'!A1")) = False Then
PauseTimeStart = Timer
ErrRptMsg = vbCrLf & "The Existing 'Error Cells+' Worksheet Will be Deleted" & vbCrLf & vbCrLf
ErrRptMsg = ErrRptMsg & "Press OK to Continue" & vbCrLf & vbCrLf
ErrRptMsg = ErrRptMsg & "To Save it, Press CANCEL and Rename the Worksheet " & vbCrLf
ErrRptMsg = ErrRptMsg & "Before Running the 'Find Cell Errors' Utility Again" & vbCrLf & vbCrLf
Title = "Deleting Existing Error Report"
Response = MsgBox(ErrRptMsg, 49, Title)
If Response = 2 Then ' User pressed CANCEL
GoTo Normal_Exit
Else
PauseTimeStop = Timer
PauseTime = PauseTimeStop - PauseTimeStart
Sheets("Error Cells+").Delete
End If
End If
' Determine the number of worksheets and cells in the workbook
Application.StatusBar = "Determining the number of cells used in this workbook..."
For Each ws In ActiveWorkbook.Worksheets
shtcells = 0
shtcells = ws.UsedRange.Cells.Count
wbCellsTot = wbCellsTot + shtcells
wsCnt = wsCnt + 1
Next
ReDim StrArry(1 To wbCellsTot, 1 To 1)
ReDim NumArry(1 To wbCellsTot, 1 To 4)
' Loop through the used range of all worksheets in the workbook and if a
' cell error is found then load the Worksheet Name, Cell Address,
' Cell Value and Cell Formula into the error array.
errCnt = 0
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Checking Cells in Worksheet " & ws.Name & " for Cell Errors"
If ws.Visible <> -1 Then hddn = "Worksheet"
Set rngErrors = Nothing
Set rngTest = Nothing
Set rngErrors = ws.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
Set rngTest = ws.UsedRange.SpecialCells(xlCellTypeConstants, xlErrors)
If rngTest Is Nothing And rngErrors Is Nothing Then GoTo Next_WS
If Not rngTest Is Nothing Then
If rngErrors Is Nothing Then
Set rngErrors = rngTest
Else
Set rngErrors = Union(rngErrors, rngTest)
End If
End If
For Each rngCell In rngErrors
If IsError(rngCell.Value) Then 'Redundant but needed if SpecialCells is graeter than 8,000
If hddn <> "Worksheet" Then
If rngCell.EntireColumn.Hidden = True And rngCell.EntireRow.Hidden = True Then
hddn = "R/C"
ElseIf rngCell.EntireRow.Hidden = True Then
hddn = "R"
ElseIf rngCell.EntireColumn.Hidden = True Then
hddn = "C"
End If
End If
errCnt = errCnt + 1
StrArry(errCnt, 1) = ws.Name
NumArry(errCnt, 1) = rngCell.Address
NumArry(errCnt, 2) = hddn
NumArry(errCnt, 3) = rngCell.Value
NumArry(errCnt, 4) = "'" & rngCell.Formula
If hddn <> "Worksheet" Then hddn = ""
End If
Next
Next_WS:
hddn = ""
Next
' If no errors found in the workbook. Display the "No Errors" dialog and exit sub
If errCnt = 0 Then
Finish = Timer - PauseTime
Application.StatusBar = "No Errors Found in Workbook " & ActiveWorkbook.Name
myMsg = vbCrLf & "No Cell Errors in Workbook:" & vbCrLf & vbCrLf
myMsg = myMsg & " '" & ActiveWorkbook.Name & "'" & vbCrLf & vbCrLf
myMsg = myMsg & Format(wbCellsTot, "#,#") & " Cells Reviewed in " & Format(wsCnt, "#,#") & " Worksheets"
myMsg = myMsg & vbCrLf & vbCrLf & " Elapsed Time = " & Format(Finish - Start, "0.0000") & " Secs"
MsgBox myMsg, vbInformation, "Check Workbook For Cell Errors"
'Clean up the application settings & Exit
GoTo Normal_Exit
End If
' If errors were found, add a new worksheet after the last worksheet
' in the workbook and write the error array contents beginning in A2
Application.StatusBar = "Populating and Formatting the 'Error Cells+' Worksheet"
Set ws = Worksheets.Add(After:=Sheets(Sheets.Count))
ws.Name = "Error Cells+"
ActiveWindow.DisplayGridlines = False
Range("A1:E1").Value = Array("Worksheet", "Cell", "Hidden", "Error", "Formula")
[A2].Resize(errCnt, 1) = StrArry
[B2].Resize(errCnt, 4) = NumArry
If Val(Application.Version) >= 12 Then 'Excel version 2007 or greater
ActiveSheet.ListObjects.Add(xlSrcRange, , , xlYes).Name = "Tbl_CellErrors"
Columns("C:C").HorizontalAlignment = xlCenter
With Range("E:E")
.ColumnWidth = 100
.WrapText = True
End With
Else ' Versions earlier that Excel 2007
Columns("C:C").HorizontalAlignment = xlCenter
With Range("A1:E1")
.Font.Bold = True
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.ColorIndex = 37
End With
With Columns("E:E")
.ColumnWidth = 100
.WrapText = True
End With
With Range("A1").CurrentRegion
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With
Range("A1").AutoFilter
End If
' Convert the error cell addresses to Hyperlinks
Application.StatusBar = "Generating Hyperlinks to the Cell Errors Found in the Workbook"
If errCnt > 1000 Then
HyperCounter = 1000
Else
HyperCounter = errCnt
End If
Set rngCell = [B2]
For cntr = 1 To HyperCounter
ActiveSheet.Hyperlinks.Add Anchor:=rngCell, Address:="", SubAddress:= _
"'" & rngCell.Offset(0, -1) & "'!" & rngCell.Value, TextToDisplay:=rngCell.Value
rngCell.Font.ColorIndex = 32
If rngCell.Offset(0, 1) = "Worksheet" Then
strComment = "Unhide the '" & rngCell.Offset(0, -1) & "' worksheet for the hyperlink to function"
rngCell.AddComment Chr(10) & strComment
End If
Set rngCell = rngCell.Offset(1, 0) 'Drop down one cell
Next cntr
'Add Heading and Date/Time
Application.StatusBar = "Formatting the Error Report Worksheet"
Rows("1:12").Insert Shift:=xlDown
[A1] = "Cell Errors in Workbook - '" & ActiveWorkbook.Name & "'"
[A2] = Format(Date, "short date") & " @ " & Format(Time, "short time")
With Range("A1:E2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Size = 14
.Merge True
End With
'Add Footer
With ActiveSheet.PageSetup
.LeftFooter = "&8&F-(&A)"
.CenterFooter = "&8Page &P of &N"
.RightFooter = "&8Printed on &D @ &T"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.CenterHorizontally = True
.Orientation = xlPortrait
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
'Add stats, error index values and generic causes
[A4] = "Summary:"
[A5] = "#Worksheets"
[B5] = Format(wsCnt, "#,#")
[A7] = "#Used Cells"
[B7] = Format(wbCellsTot, "#,#")
[A9] = "#Errors Found"
[B9] = Format(errCnt, "#,#")
[A11] = "Timer (Secs)"
[D4] = "Error Index:"
Range("D4:E4").Merge
Rows("4:4").Font.Bold = True
Range("D5:E5").Value = Array("#DIV/0!", "occurs when a number is divided by zero (0)")
Range("D6:E6").Value = Array("#N/A", "occurs when a value is not available to a function or formula")
Range("D7:E8").Value = Array("#NAME?", "occurs when Microsoft Excel doesn't recognize text in a formula")
Range("D8:E8").Value = Array("#NULL!", "occurs when an intersection of two areas do not intersect")
Range("D9:E9").Value = Array("#NUM!", "occurs with invalid numeric values in a formula or function")
Range("D10:E10").Value = Array("#REF!", "occurs when a cell reference is not valid")
Range("D11:E11").Value = Array("#VALUE!", "occurs when the wrong type of argument or operand is used")
With Range("D5:E11, A5:B5, A7:B7, A9:B9,A11:B11")
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With
' Write the Elapsed Time, fit columns
[B11] = Format(Timer - Start - PauseTime, "#.####")
Cells.EntireColumn.AutoFit
If HyperCounter = 1000 Then MsgBox "The Hyperlink Process was Stopped after the First 1,000 Errors to Reduce User Wait Time"
[A13].Select
Normal_Exit:
'Clean up the application settings & Exit
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.StatusBar = False
End With
On Error GoTo 0
Exit Sub
Problem:
If Err.Number = 1004 Then Resume Next ' No Error or Constant Special Cells were found
myErrMsg = vbCrLf & "An Unexpected Error has Occurred in Procedure 'FindCellErrors' " & vbCrLf & vbCrLf
myErrMsg = myErrMsg & " Error #" & Err.Number & " (" & Err.Description & ")" & vbCrLf & vbCrLf
myErrMsg = myErrMsg & "The Program is Terminating Without Completing the Reporting Process "
MsgBox myErrMsg, vbCritical, "'Find Cell Errors' Utility - System Error Occurred"
Resume Normal_Exit
End Sub
Spreadsheet Auditing Tools
FindCellErrors is a simple single use utility that was interesting to write and is very useful for finding cell based formula errors. However, there are a number of professionally built spreadsheet auditing suites on the market that perform a variety of complex functions for auditing Excel workbook models. An excellent example of these tools is the Mappit! Add-in written by Dave Brett which can be downloaded from the Experts Exchange Article located here. Within Dave's article are links to a number of other sites and auditing tools for your reference.---------------------
If you found the article helpful please click on the "YES" button after the question below.
This utility has been helpful to me and I hope you find an opportunity to use it as well.
If you didn't or otherwise want to vote No, please leave a comment and give me an
opportunity to respond. It will give me a chance to improve this article and others I
would like to write.
Thank You for Stopping by to Read the Article!
---------------------
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (6)
Author
Commented:Commented:
Very nice debut article!
Patrick
Author
Commented:Thank you. Some credit has to go to Markus(harfang) who was the EE page editor that worked with me. The first submission could have been published but Markus made several excellent suggestions for improvement and helped make it a much better article than the original.
Jerry
Commented:
Thanks for the acknowledgement, but your really did all the work yourself, including rewriting entire portions of the code to improve it. It was a pleasure working with you, for your communicative enthusiasm, your willingness to go through several editorial cycles, and your candour in light of constructive criticism.
Great job! I wish plenty of success to this article and to you as an author.
Cheers!
Markus — (°v°)
Author
Commented:Thanks,
Jerry
View More