set variable to 0 if a function generates an error

I have the following code that runs a simple function. What I'd like to have it do is, if function generates an error, set qual_1 to 0. Right now, it sets qual_1 to equal 0 no matter whether TF1 generates an error or not. Where's my mistake here?

Please Login or Register  to view this content.

PS: TF1 can generate many types of errors, including TF1 = var1 / 0, TF1 = var1 / "AA", etc. so I'd like the error catcher to be able to detect any type of them and set qual_1 to 0.

In your example, qual_1 does actually equal 1 on the If line. The problem is that the Err: line is then setting it to 0. That line is going to be run whether you have an error or not; there's nothing special about it. All your On Error line is doing is telling the code which line to skip to if there is an error.

There's a multitude of different ways and approaches to handling this, here's just one:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Originally Posted by walruseggman

In your example, qual_1 does actually equal 1 on the If line. The problem is that the Err: line is then setting it to 0. That line is going to be run whether you have an error or not; there's nothing special about it. All your On Error line is doing is telling the code which line to skip to if there is an error.

Ok, thanks for the example. I've tweaked your code a bit:

Please Login or Register  to view this content.

I've never used GoTo or OnError, so just want to understand the logic a bit more, so line by line, let me know if I follow this correctly:
If there's an error on line 2, then skip to line 4 (Err), if no error on line 2, assign 1 or 0 to qual_1 based on TF1() then skip to line 5 (NoErr) to avoid running line 4. Is that right?

Also, am I correct in assuming that the evaluation of TF1 happens only once here (as opposed to stnkynts code above which does it 2 times: first evaluates it to see if there is an error in TF1 and then evaluates it again to assign 1 or 0 to qual_1)?

Thanks

Your error logic is sound, but not really best practice. I see the logic: if there is an error gets assigned 0 on line 4, then gets assigned itself zero again on line 5, so everything is fine.

The reason I say it's not best practice is because what if, in the future, your NoErr is going to involve more than one line of code, manipulating things you don't want changed if their is an error? You'd have to restructure your code to skip it anyway. Might as well practice that style now.

Also, am I correct in assuming that the evaluation of TF1 happens only once here

Correct. And I'll let @stnkynts speak to his own code.

Mine only tests for an error once and avoids the bad practice of On Error or Goto (excluding the On Error Resume Next for the single line in your function which in this case is ok). The second if statement is of your own design and I assumed what based upon the calculation of the formula. In other words it works as follows: 1) Check to see if formula errors. 2) If yes then enter 0, if no then do math of formula and enter value based upon result.

Ok, thank you both for contributing. I can see advantages to both with what I'll be doing in the near future. A couple of follow up questions just so I can understand how the On Error logic works:

walruseggman:

Please Login or Register  to view this content.

Does On Error detect errors only on the next line line of code right after it? Or does it catch errors anywhere in between it and line with Err: on it? So if there was 5 more lines of code after If TF1(var1) = 1 Then qual_1 = 1 Else qual_1 = 0, but before Err: would it catch them and jump to Err?

stnkynts:

Please Login or Register  to view this content.

What does Resume Next do? Does it simply skip only the very next line of code after "On Error Resume Next" line if there's an error in it, and starts evaluating the next line? Or does it skip any line of code below "Resume Next" statement if that particular line has an error in it?

..........

Does On Error detect errors only on the next line line of code right after it? Or does it catch errors anywhere in between it and line with Err: on it? So if there was 5 more lines of code after If TF1(var1) = 1 Then qual_1 = 1 Else qual_1 = 0, but before Err: would it catch them and jump to Err?

Upon detecting an error it would "jump" to the specified point skipping all lines in between.

What does Resume Next do? Does it simply skip only the very next line of code after "On Error Resume Next" line if there's an error in it, and starts evaluating the next line? Or does it skip any line of code below "Resume Next" statement if that particular line has an error in it?

Rather than erroring out the code simply goes to the next line but in this case is captured as an error in the initial subroutine.

Ok, got it! Thanks for the clarifications.

walruseggman,

Having trouble with code based on yours but with one more function inserted.

Please Login or Register  to view this content.

Putting in TF1 = var1 / 0 while TF2 = var2 / 1 and visa versa works fine, but dividing both var1 and var2 by 0 is what pops a box stating "runtime error 11: divide by 0". Any idea why?

You can't use on error goto within an error handler.

See http://www.cpearson.com/excel/errorhandling.htm

Either work out the logic like I did in the other thread or the better idea of catching them with iserror(). As Stnkynts said above that is better practice.

As per my (tidy) comment - using resume next to manage the errors could easily give you a piece of code that future coders will be unable to understand (bad - unless you want to do that!).