VBA: How long does On Error Resume Next work?

vba try-catch onerror
Tawm picture Tawm · Jul 31, 2015 · Viewed 11.5k times · Source

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.

Answer

Pradeep Kumar picture Pradeep Kumar · Aug 1, 2015

SCOPE OF ON ERROR... STATEMENT

The effec5 of ON ERROR ... ends as soon as one of the following is encountered:

  1. Another ON ERROR .... (Maybe in the form of ON ERROR RESUME x or ON ERROR GOTO x)
  2. Exit Sub / Exit Function within the same sub/function where defined.
  3. End Sub / End Function of the sub/function where defined.

IS IT BAD TO USE ON ERROR RESUME NEXT?

Yes and No.

I would say don't use without knowing what the effect of this statement would be. Avoid if possible. Keep the scope short wherever not possible.

To nullify the effect of an ON ERROR RESUME NEXT statement, you can call ON ERROR GOTO 0

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More