How do i determine if an error is in any cell in t

Question

Q: How do i determine if an error is in any cell in the entire workbook with Excel VBA?

Normally errors will be divide by 0 or #value errors, but this list is not exhaustive (or is it? - i don't know if more exist)

Is there a way to determine if a cell contains an error then to skip over further processing in my script without spitting out a debug/warning/error message.

something such like

          if value in current.Workbook.cell is error then go to <jump>
           OR
          if value in old.Workbook.cell is error then go to <jump>

where jump is a marker at the end of an if statmenet but within a loop.

the script compares values between two workbooks and updates the current workbook with colours to show difference.

I have no VBA experience at all. but i get the gist of the script i have been given.

thank you kindly.

Answer 1

Given your initial question was how do you detect errors in any cell of the workbook with VBA then you should be looking for a very efficient approach - looking at each cell is very expensive time wise!

Two options for this are:

1. Use Excel's SpecialCells to shortcut the process
2. use my Mappit! addin which is configured to report on spreadsheet errors

For SpecialCells please see the code below. This takes advantage of the ready-made collection of errors that exist as formulas

Please that that constants also have an errors collection, so if you have copied then run a paste special as value on a formula error then you would need to use Set rng1 = ws.Cells.SpecialCells(xlConstants, xlErrors) to detect these

You can use also detect SpecialCells manually by

• Select all cells in the area of interest
• Press F5
• Click Special
• select 'Errors' under 'Formulas' (or 'Constants')

Please be aware that prior to xl2010 there is a limit of 8192 areas that SpecialCells can handle

Sub ErrorList()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim strOut As String
    For Each ws In ActiveWorkbook.Sheets
        Set rng1 = Nothing
        On Error Resume Next
        Set rng1 = ws.Cells.SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rng1 Is Nothing Then strOut = strOut & (ws.Name & " has " & rng1.Cells.Count & " errors" & vbNewLine)
    Next ws
    If Len(strOut) > 0 Then
        MsgBox "Error List:" & vbNewLine & strOut
    Else
        MsgBox "No Errors", vbInformation
    End If
End Sub

Answer 2

You can use the IsError() function from VBA as well as as a formula in a worksheet.

See http://vbadud.blogspot.com/2007/04/using-vbas-iserror-function.html for an example.

Answer 3

There's another way to do handle this: add On Error Resume Next into your code (usually just put it before the loop).

If a cell is an error, it'll just skip it and move to the next element in the loop :)

Answer 4

Here's an snippet of code that records in the Immediate Window, the worksheet name, cell address and formula, where there is a spreadsheet formula error...

Dim ws As Worksheet, r As Range

For Each ws In Worksheets
    For Each r In ws.UsedRange
        If IsError(r.Value) Then
            Debug.Print r.Parent.Name, r.Address, r.Formula
        End If
    Next
Next

Answer 5

You can skip cells with errors by using the VarType function. For example:

If VarType(ActiveCell.Value) <> vbError Then
    ' do something
End If

The VarType function is also very useful to validate the data type. For example if your code expects a date value but sometimes encounters text, you can use this function to vet the data and handle exceptions gracefully.