[SOLVED] VBA On Error not catching errors

VBA On Error not catching errors

Posting this here, in case it can help others. I was seeing some strange behavior where:

Sub somesub()
On Error GoTo Handler
    Dim x
    x = 1/0
    MsgBox("Done")
    Exit Sub
Handler:
    MsgBox("Error was handled")
End Sub

Was not catching the error.

There is a related question on SO already, at: Excel VBA Not Properly Breaking on Error, but it is asking about how to mitigate side effects of making one choice or another in the Error Trapping settings. This is asking why the error handling is not working at all.

Solution

It turned out that in the VBA editor, under

Tools>>Options>>General>>Error Trapping

I had "Break on All Errors" selected, which overrides any instruction in the code to GoTo or Resume on the error. Changing the selection to "Break on Unhandled Errors" resolved my issue.

Source for answer: Five tips for handling errors in VBA