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

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

Barhorst

a list of possible errors i wish to ignore (skip over) #DIV/0! #NAME? #N/A #NULL! #NUM! #REF! #VALUE! – Barhorst

Answer 1

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.

Fakery

thank you very much, implemented this and it seems to have stopped the errors and allowed the process to continue. – Barhorst

Answer 2

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

Aleuromancy

wasn't quite what i was after, but thank you, it is useful to know. Now if i could get the error information from my VB into a php script... – Barhorst

Answer 3

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

Juggler

Answer 4

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.

Whipstall

Answer 5

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 :)

Fibre

thank you for this. i assume this needs to be before each loop or if statement rather than at the start of a document. i've added this in and it has helped. thank you – Barhorst

Nope just once in the sub or function and it works throughout! – Fibre

I wouldn't recommend on error resume next as it will hide all errors, not just where a cell value is in error. – Whipstall

For a more compicated procedure I'd agree but if all you are doing is copying cells then you can be pretty sure what errors might occur. I'd agree best method is being specific on errors but on error resume next is still one of several valid solutions IMO. – Fibre

Answer 6

To check if Worksheet has any errors you can use this function:

Function ContainsError(sheet)
On Error GoTo ErrorHandler
r = sheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
ContainsError = True
Exit Function
ErrorHandler:
ContainsError = False
End Function

So if you want to check entire Workbook you could write another function that uses the previous one like this:

Function ContainsErrorWB(book)
For Each ws In book.Worksheets
   If ContainsError(ws) Then
      ContainsErrorWB = True
      Exit Function
   End If
Next
ContainsErrorWB = False
End Function

And you can use it like this:

If ContainsErrorWB(ThisWorkbook) Then
   'Do something
Else
   'Do something else
End If

Just remember to include both of them in your module.

Kenward