VBA error handler: On Error Resume does not work in the handler

Question

In Excel VBA, I open a Word app and a doc inside. I want to make sure the doc and Word are closed at the end, even if there was an error.

I created an error handler with On Error GoTo ErrorHandler, that works. But there objDoc.Close fails when the doc was not opened (Runtime error 424), and the Sub is aborted before objWord.Quit although I called On Error Resume Next.

Why does On Error Resume Next not work there?

(In VBA options, Error Trapping is set to "Break on Unhandled Errors".)

Sub test()
    On Error GoTo ErrorHandler
    ' Open doc in Word
    Set objWord = CreateObject("Word.Application")
    objWord.Visible = False
    Set objDoc = objWord.Documents.Open("not a valid doc.docx")

    ....

    ' Save and exit
    objDoc.Save
    objDoc.Close
    objWord.Quit
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & vbLf & Err.Description
    On Error Resume Next
    ' Exit
    objDoc.Close SaveChanges:=False
    objWord.Quit
    On Error GoTo 0
End Sub

`On Error` statements aren't supposed to be executed in an error state... — Mathieu Guindon, Feb 07 '19 at 15:28
The generally used pattern for stuff like this is to `Resume` to a label where you do your cleanup - like where `' Save and exit` is. — Comintern, Feb 07 '19 at 15:30
See [Good Patterns For VBA Error Handling](https://stackoverflow.com/q/1038006/4088852) - it's worth a read. — Comintern, Feb 07 '19 at 15:37

score 5 · Accepted Answer · answered Feb 07 '19 at 15:36

Here's how I would do it, like @Comintern says

Sub test()
    Dim objWord As Object
    Dim objDoc As Object

    On Error GoTo ErrorHandler
    ' Open doc in Word
    Set objWord = CreateObject("Word.Application")
    objWord.Visible = False
    Set objDoc = objWord.Documents.Open("not a valid doc.docx")




ErrExit:
    On Error Resume Next
    objDoc.Save
    objDoc.Close
    objWord.Quit
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & vbLf & Err.Description
    Resume ErrExit
End Sub

That Resume statement gets you out of the error state and allows you to reset an error trap.

Upvoted; I would only add that the variables should be explicitly declared, and the double-dot referencing of `objWord.Documents` is leaking the implicit collection object, which means OP probably has a bunch of WINWORD.EXE ghost processes in their task manager (see my deleted answer) — Mathieu Guindon, Feb 07 '19 at 15:41

VBA error handler: On Error Resume does not work in the handler

1 Answers1