Take the 2-minute tour ×

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

share|improve this question
a list of possible errors i wish to ignore (skip over) #DIV/0! #NAME? #N/A #NULL! #NUM! #REF! #VALUE! –  TheCellarRoom Nov 22 '11 at 17:02

5 Answers 5

up vote 7 down vote accepted

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.

share|improve this answer
thank you very much, implemented this and it seems to have stopped the errors and allowed the process to continue. –  TheCellarRoom Nov 23 '11 at 8:46

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.

share|improve this answer

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
share|improve this answer
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... –  TheCellarRoom Nov 23 '11 at 8:48

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

enter image description here

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
        MsgBox "No Errors", vbInformation
    End If
End Sub
share|improve this answer

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

share|improve this answer
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 –  TheCellarRoom Nov 23 '11 at 8:47
Nope just once in the sub or function and it works throughout! –  Issun Nov 23 '11 at 8:57
I wouldn't recommend on error resume next as it will hide all errors, not just where a cell value is in error. –  Neil Barnwell Nov 23 '11 at 11:33
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. –  Issun Nov 23 '11 at 11:43

Your Answer


