OnError has stopped working and you dont know why?

By bruce mcpherson

VBA has no try, catch construct so you have to use On Error instead. The "Break on All Errors" option in excel persists between Excel sessions and will cause workbooks that were previously working to fail for no obvious reason.

One day all of your workbooks start crashing out with the same kind of error. You have been using OnError to handle exceptions, and as an integral part of your code. One typical use might be to see if something is already in a collection, so you might have a function that looks something like this.

Public Function Exists(myKey As String) As myObject
     On Error GoTo handle
     Set Exists = myCollection(myKey)
     Exit Function
     Set Exists = Nothing
End Function

You want to use the error to detect that myKey is not already in the collection, and the On Error statement ensures that you handle it, but now it crashes out when it was working perfectly before.

There is an option in Excel "Error Trapping" under VBE/Tools/Options/General. Check that you haven't set it to "Break on All Errors" as opposed to "Break on unhandled errors" at some point. The option persists between workbooks and can cause a lot of headscratching.

OnError has stopped working and you dont know why?  (687 Views)