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

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.