ProfessionalExcel.com In the office, Excel is a way of life… not just a tool

21Jun/1110

Replace IFERROR() with IF(ISERROR())

This tip relates to the technique of trapping and handling errors in a formula. Prior to Excel 2007 (e.g. Excel 2003, 2002, 2000), this was accomplished using a combination of the IF and ISERROR functions to test for an error in a formula, e.g. =IF(ISERROR(MyFormula),ReturnSomethingElse,MyFormula). This meant having to write your formula twice (think of your longest formula!).

In Excel 2007, trapping and handling errors in a formula was made easier with the introduction of the IFERROR function, which removed the need to specify your formula twice, e.g. =IFERROR(MyFormula,ReturnSomethingElse).

With organisations upgrading to Office (Excel) 2007 at different times, compatibility can become an issue. Although installing the Microsoft Office Compatibility Pack will enable users of previous versions of Excel to open an Excel 2007 file, you can't rely on this being installed. Also, you may want to convert an Excel 2007 file to a previous version for people to use / modify; In Excel 2007, simply 'saving as' an older version (e.g. saving an 'xlsx' as an 'xls' file) will often bring up a load of compatibility warning messages, which aren't fixed automatically.

Through my need to develop in all versions of Excel, I developed a handy procedure (ReplaceIFERRORCellFormulas) that will convert a range of selected cells that contain IFERROR formulas to instead use a combination of the IF and ISERROR functions. There's also a procedure to check all Named Ranges (ReplaceIFERRORNamedRangeFormulas), in case you use them to store formulas.

modReplaceIFERROR.bas
modReplaceIFERROR.bas
modReplaceIFERROR.bas
Version: 1.0
7.5 KiB
1681 Downloads
Details...
Comments (10) Trackbacks (2)
  1. Dude,
    Great job.
    It was really a wonderful time saver.

  2. Thanks a lot for the “little” modules. Really useful.

    Cheers

    MM

  3. Thank you so much.

    I have a huge Excel sheet where there are a couple of hundred different formulas included in the function if(iserror(someformula),0,someformula).

    Do you know of a macro to remove all if(iserror()) functions and keeping the formulas intact?

    Thank you for your help and advice.

  4. Thank you sooo much!!!

  5. Man…. I owe you a beer honestly……… you are the best!!!!!!!

  6. Most Helpful. Huge time saver. Thank you for building & sharing!!

  7. Thanks a million. saved me loads of time!

  8. Just what I needed to make my Excel 2010 file work correctly in Open Office. Excellent time-saver. Thanks!

  9. This is great! Thanks a ton


Leave a comment