best practice on error handling formulas in VBA

[WasWodge]

It is literally just a best practice question really.
Is it better practice/more efficient when filling a formula (normally in my case a lookup) down a column in VBA to put any error handing in the formula something like

Please Login or Register  to view this content.

which is what I normally do, or is it better to write any error handling as part of the VBA routine i.e. an "On Error" piece of coding?

Or is it personal preference either way?

Any insight will be gratefully received (and yes in an ideal world I would love all the info at work not to trigger an error in the first place but sometimes it should)

[Colin Legg]

Hi,

If you're putting a formula into a range, and you want errors as a result of that formula to be translated into another type such as the string "Not Found", then I think it's very logical to handle the error in the formula as you have done. These are quite different to VBA run time errors, which is what you would use error handling for.

[shg]

I'm not sure what the value is of evaluating the lookup twice just to change #N/A to "not found".

[WasWodge]

Sorry Shg, it wasn't a good example to use and I do get your point, but it wasn't / isn't part of any real code. I was just trying to establish the fundamentals of when to use the error handling in the formula and when in the coding itself.

Thanks Colin for your pointers...much appreciated. I take it then that there is no obvious drawback to having it in the formula (that is at least when you are not doing unnecessary double evaluates)

[Whizbang]

I would say it has to do with your audience. As a developer, I would want to see the errors on the sheet as they are calculated. This way I can work on reducing or removing the source of the error, rather than just hiding it. If it is a report for users who have little to no Excel skills "Not Found" is so much more informative than "#N/A".

If it is a question of performance, then go for the path of least amount of calculations. If it is for presentation, calculation time doesn't matter (as much). Of course, IFERROR() solves the issue of double evaluation, as long as all users have 2007 or higher.

[Colin Legg]

Hi Paul,

Sorry, but I don't fully understand the question. VBA error handling is quite different; what VBA error handling approach do you propose which gives an identical finish to the code you posted in #1? I'd be happy to compare it for you?

[shg]

IMO, formulas (not VBA) return errors for one of four reasons:

1. They are bad formulas and need to be corrected.

2. They highlight deficiencies or missing data, and the result should be allowed to propagate to cue the user.

3. They reflect unused cases or irrelevant conditions for the particular case at hand.

4. They are used intentionally, e.g., to keep data from plotting.

Only in case 3 (IMO) should you suppress the output using ISERROR or IFERROR.

[WasWodge]

Colin, in this case I didn't make myself clear in my last posting. I was referring in general that I wasn't doing anything wrong in using the error handling in formula when using VBA and not to the specific formula in post #1. I am happy now in my own mind that I am not doing anything stupid. Thanks for the guidance.

Whizbang, makes sense. Cheers

Shg, mainly everything I use the error handling in formula does fall into

3. They reflect unused cases or irrelevant conditions for the particular case at hand.

For instance I have a lot of dealings with things like shelf life but some lines do not have an official shelf life. Depending which category manager they come under then they are either marked as a blank, a 0 or some put in the text N/A. These are then downloaded to a text file and converted to excel and so from our point of view they fall under irrelevant conditions or unused cases when doing most monitoring work.

Thanks to everyone for their input. Much clearer in my own mind now. Question solved