Error handling in VBA - on error resume next

Question

I have the following code:

ErrNr = 0
For Rw = StRw To LsRw 'ToDo speed up with fromrow torow
    If Len(ThisWorkbook.Sheets(TsSh).Cells(Rw, TsCl)) = 0 Then
        ThisWorkbook.Sheets(TsSh).Cells(Rw, TsCl).Interior.ColorIndex = 46
        ErrNr = ErrNr + 1
    End If
Next

My problem is if there is an error on the page, my code is not running after that. I think the solution should be with:

On Error Resume Next
N = 1 / 0    ' cause an error
If Err.Number <> 0 Then 
    N = 1
End If

But I don't know how to use this code.

Answer 1

I generally try to avoid On Error Resume Next as it will try to continue no matter what the error (there are some cases where it's useful though).

The code below passes all errors out of the procedure where they can be handled on a case by case basis.

Sub test1()

    Dim n As Double

    On Error GoTo ERROR_HANDLER

        n = 1 / 0  ' cause an error

    On Error GoTo 0
    Exit Sub

ERROR_HANDLER:
    Select Case Err.Number
        Case 11 'Division by zero
            n = 1
            Err.Clear
            Resume Next
        Case 13 'Type mismatch

        Case Else
            'Unhandled errors.
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure test1."
            Err.Clear

    End Select

End Sub

Answer 2

I have interpreted your requirement as to how to handle common worksheet errors when looping through a range of cells and examining the values. If you attempt to look at a cell that contains an error (e.g. #N/A, #DIV/0!, #VALUE!, etc) you will get something like:

Runtime error '13':
Type mismatch.

These can be caught with VBA's IsError function.

Dim rw As Long

With ThisWorkbook.Sheets(TsSh)
    For rw = StRw To LsRw
        If IsError(.Cells(rw, 1)) Then
            .Cells(rw, 1).Interior.ColorIndex = 10
        ElseIf Not CBool(Len(.Cells(rw, 1).Value2)) Then
            .Cells(rw, 1).Interior.ColorIndex = 46
        End If
    Next rw
End With

        

In the above, I am catching the cells with an error and coloring them green. Note that I am examining them for errors before I check for a zero length.

Answer 3

It depends on what you want to do.

• On Error Resume Next will ignore the fact that the error occurred. This is a great way to get your code to execute to completion, but will just about guarantee that it won't do what you want.
• On Error Goto 0 is the default response. It will pop up the error message that VBA is generating
• On Error Goto <label> will cause your code to jump to a specified label when an error occurs and allows you to take an appropriate action based on the error code.

The last option, On Error Goto <label> is usually the most useful, and you'll want to do some digging on how to best use it for your application.

This site is where I got the details above from, and is usually the first results that comes from Googling for "excel vba on error". I've used that reference myself a number of times.