vbatry-catchonerror

VBA: How long does On Error Resume Next work?

I'm reading up on how to use On Error Resume Next and I'm trying to figure out how long that line will apply to the program. On the Microsoft site, I found this sentence: "An On Error Resume Next statement becomes inactive when another procedure is called." What exactly does this mean? What is considered to be a procedure?

I ask because I'm using the line in my program, but I don't want it to Resume Next all the runtime errors which occur, just the obvious one on the next line.

Code:

Dim zRange As Range

Call FilterTableFor(fieldNameColumn, Array("baseunitprice", "burden", "MTLBURRATE", "PurPoint", "Vendornum"))

On Error Resume Next
Set zRange = commentsColumnRange.SpecialCells(xlCellTypeVisible)
zRange.Formula = "target"

Call FilterTableFor(fieldNameColumn)

I've also found (and known for a while) that On Error or GoTo lines are considered poor coding. Is there a Try-Catch which I can use for a line like this?

I'm thinking something like this:

Dim zRange As Range

Call FilterTableFor(fieldNameColumn, Array("baseunitprice", "burden", "MTLBURRATE", "PurPoint", "Vendornum"))

Try
Set zRange = commentsColumnRange.SpecialCells(xlCellTypeVisible)
zRange.Formula = "target"
Catch()

Call FilterTableFor(fieldNameColumn)

Where I don't even do anything with it, as I don't feel a need to.

Thanks for your time.

Solution

  • You only want to use "On Error Resume Next" when

    1. You know why the error occurs.

    2. You know that it will not affect other parts of the code.

    3. You use "On Error Goto 0" immediately after the code where the error occurs.

    Having said that, you should almost NEVER use it. You should figure out why the error occurs and code to handle it.

    What the website is saying is that once your are out of the sub or function that called it the resume next will no longer be in affect and your errors will raise as they should.

    A better alternative is to use goto in this fashion. But some people frown on this almost as much.

    sub SomeSub()
    On Error Goto TestFailed

    'Some code

    'Some code

    'Some code

Exit sub

TestFailed:
    'Some code here to alert you to and/or handle the fallout of the error.
End sub