VBA – Make a list of the formula errors in an Excel

Errors in Excel are different – from “Division by Zero” (#Div/0) and #Name to #REF! and #Num.  There are lots of ways to summarize these into some kind of report and in the current article I will show the VBA way how to do it.

First, in order to have the same results, we should be getting the same errors. The code below generates 12 errors on every worksheet of the workbook, with some offset, thus the errors are on different cells:

Sub PopulateErrors()
    
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        With wks
            Dim myCell As Range
            For Each myCell In wks.Range("A1:A5").Offset(wks.Index)
                myCell.Formula = "=5 / 0"
            Next myCell
    
            For Each myCell In .Range("B1:B6").Offset(wks.Index)
                myCell.Formula = "=formulathatdoesnotexist()"
            Next myCell
    
            For Each myCell In .Range("A2:C2").Offset(wks.Index)
                myCell.Formula = "=6+""vitosh"""
            Next myCell
            
        End With
    Next

End Sub

If you want to have a summary of the type of errors per worksheet, this is what is quite easily possible with VBA:

For the details, the picture in the immediate window is a bit different:

The VBA for the summary looks like this:

Sub ErrorDataSummary()

    Dim myCell As Range
    Dim myDict As Object

    Set myDict = CreateObject("Scripting.Dictionary")
    
    Dim wks As Worksheet
    Dim errorName As String
    
    For Each wks In ThisWorkbook.Worksheets
        For Each myCell In wks.UsedRange
            If IsError(myCell) Then
                errorName = myCell.Text & " in " & wks.Name
                If myDict.exists(errorName) Then
                    myDict(errorName) = myDict(errorName) + 1
                Else
                    myDict(errorName) = 1
                End If
            End If
        Next myCell
    Next

    Dim myKey As Variant
    For Each myKey In myDict.keys
        Debug.Print myKey; myDict(myKey)
    Next

End Sub

And the VBA for the details is this one:

Sub ErrorDataDetails()

    Dim myCell As Range
    Dim myDict As Object

    Set myDict = CreateObject("Scripting.Dictionary")
    
    Dim wks As Worksheet
    Dim errorName As String
    Dim errorAddress As String
    For Each wks In ThisWorkbook.Worksheets
        For Each myCell In wks.UsedRange
            If IsError(myCell) Then
                errorName = myCell.Text & " in " & wks.Name
                errorAddress = myCell.Address
                Debug.Print errorName & " -> " & errorAddress
            End If
        Next myCell
    Next

End Sub

Cheers! 🙂