UDF Untrapped Error in Excel 97

This only happens within Excel 97. The problem has been corrected in Excel 2000 onwards.
When running code it might stop unexpectedly without any obvious cause. No error messages will be displayed and no warning given that an error is about to occur or has occurred.


The instructions on how to recreate the error are shown below. These are repeated in the example's VBA module.
  1. Ensure the immediate window is open so you can see the output from Debug.Print.
    If it is not open press Ctrl+G
  2. Run the sub "UdfUntrappedErrorDemo".
    Notice you get two messages in the immediate window and that the
    cell with range name "DateAndTime" has had its contents updated.
  3. Go to the worksheet. Change the argument to the light green cell from:
    This will cause the formula to report a "#VALUE!" result.
  4. Rerun the sub "UdfUntrappedErrorDemo".
    Notice that you only get the first message.
    This is because Excel encounters the error, when running its
    formula recalculation engine, and stops.
  5. Uncomment the error trap in the REVNUM function, below.
  6. Rerun the sub "UdfUntrappedErrorDemo".
    You now get both messages.
    The trap tells Excel how to cope with the error.

Last updated: 01-Mar-2011 20:51