<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Automate Finding Cell Errors with the 'Find Cell Errors' Utility

Published on
17,161 Points
6,161 Views
10 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick
By Jerry Paladino

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:
Cell 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.

No Errors Found

 

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!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.

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

Open in new window


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!
---------------------
10
7 Comments
LVL 50

Expert Comment

by:Dave
Jerry - bravo!
0
LVL 16

Author Comment

by:Jerry Paladino
Thanks Dave!
0
LVL 93

Expert Comment

by:Patrick Matthews
Jerry,

Very nice debut article!

Patrick
0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

LVL 16

Author Comment

by:Jerry Paladino
Patrick,

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
0
LVL 58

Expert Comment

by:harfang
Jerry,

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°)
0
 

Administrative Comment

by:Patrick Matthews
ProdOps,

After review, your article has been selected for Editors' Choice, EE's highest honor for articles.

Thank you for sharing this terrific piece with us!

matthewspatrick
Page Editor
0
LVL 16

Author Comment

by:Jerry Paladino
Wow!  Thank you Patrick.   I really appreciate being selected.  Writing this article was very educational for me personally and I hope others have found it useful as well.

Thanks,
Jerry  
0

Featured Post

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month