9

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.

Pradeep Kumar
  • 6,836
  • 4
  • 21
  • 47
Tawm
  • 535
  • 3
  • 12
  • 25
  • 1
    Using `On Error...` is not "considered poor coding". Only using it *badly* is. There's no `Try...Catch` in VBA. – Tim Williams Jul 31 '15 at 18:57
  • @TimWilliams does VBA not follow all Visual Basic rules? – Tawm Jul 31 '15 at 18:59
  • VBA is basicaly VB6. It is not VB.NET – MatthewD Jul 31 '15 at 19:05
  • 1
    While it isn't exactly the same thing `On Error Resume Next` is roughly the equivalent to `try`if used in a disciplined manner: followed a short time later by `If Err.Number > 0 Then` ...(code corresponding to `catch`) .... `End If` .... `On Error Goto 0`. It isn't pretty, but in some ways the ugliness of it forces you to write more robust code. – John Coleman Jul 31 '15 at 19:16

4 Answers4

11

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

underscore_d
  • 6,309
  • 3
  • 38
  • 64
Pradeep Kumar
  • 6,836
  • 4
  • 21
  • 47
7

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
MatthewD
  • 6,719
  • 5
  • 22
  • 41
  • So, in order to ignore a single line, would i do something like: `On Error resume Next` `error code` `On Error Goto 0` wow that's ugly but you get the idea – Tawm Jul 31 '15 at 19:01
  • Yes On Error resume Next - then the line of code - then On Error Goto 0. But head the warnings. You should not get into a habit of using this. If you are getting a particular error. Post you code here at stack overflow and the community can help you figure out how to code for it. – MatthewD Jul 31 '15 at 19:04
  • If the program were bigger, I'd figure out how to fix the error. This could probably be written in ~30 lines, so I feel like adding 3-10 lines to fix it is a waste of time. Thank you though! :) – Tawm Jul 31 '15 at 19:07
  • 1
    Sometimes `On Error Resume Next [...] On Error GoTo 0` is unavoidable. For example, you cannot check if a line causes an error or else fill in something different. An example of that would be: you want to loop through all sheets in the workbook and add a number to the named range `testSum` in the specific worksheet. Now, you might not have this range in every sheet, so you have to put `On Error Resume Next [...] On Error GoTo 0` around it. – Spurious Aug 24 '16 at 11:40
0

You don't always need a bunch of code to handle an error, but you really should do something with it. Maybe just have your code change the cells.font.color property to vbRed. Simply doing On Error Resume Next (a line of code that might error) On Error Goto 0 is terribly poor form.

And like others have pointed out, On Error Goto Label is essentially VBA's version of Try ... Catch, and I use it frequently

Community
  • 1
  • 1
Tim
  • 2,701
  • 3
  • 26
  • 47
0

To answer your question "How long does On Error Resume Next work?"

The answer is: until the next definition of On error ...

So if you define an On error resume next, it will skip every error until you define a On error goto 0 or On error goto label

Maxime Porté
  • 1,034
  • 8
  • 13