CODEWITHSUNDEEP
excelvba
user894932
user894932

Reputation:

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

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.

Upvotes: 9

Views: 35133

Answers (6)

Chrysaloid
Chrysaloid

Reputation: 53

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.

Upvotes: 0

brettdj
brettdj

Reputation: 55692

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
    Else
        MsgBox "No Errors", vbInformation
    End If
End Sub

Upvotes: 4

Gaijinhunter
Gaijinhunter

Reputation: 14685

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

Upvotes: 0

SkipVought
SkipVought

Reputation: 134

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

Upvotes: 6

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

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.

Upvotes: 19

Neil Barnwell
Neil Barnwell

Reputation: 42165

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.

Upvotes: 2

Related Questions