Alex Rivera | Logout

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

Asked 2011-11-22T17:00:06.100
11

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.

excel vba
Edit
Report

2 Answers

20

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.

answered 2011-11-22T19:18:13.097
7

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
answered 2011-11-22T21:39:22.983

Your Answer