How to handle error generated inside another workbook's Workbook_Open event?

user_619177 0
Viewed 243

I have two workbooks in the same folder: bkOpenErrorTest.xlsm and bkOpenErrorTest_dict.xlsm.

bkOpenErrorTest_dict.xlsm has the following code in its ThisWorkbook module:

Private Sub workbook_open()

Dim dict As Dictionary

Set dict = New Dictionary
dict.Add 0, 0
dict.Add 0, 0

End Sub

When this workbook is opened by double-clicking the filename, it throws the expected unhandled error:

This key is already associated with an element of this collection

bkOpenErrorTest.xlsm has the following code in Module1:

Sub testOpen()

Dim bk As Workbook

On Error GoTo errHandler

Workbooks.Open ThisWorkbook.Path & "\bkOpenErrorTest_dict.xlsm"

Exit Sub

errHandler:
Debug.Print "reached error handler"

End Sub

When error trapping is set to Break on Unhandled Errors, and I run testOpen(), the unhandled error is still raised when bkOpenErrorTest_dict.xlsm opens. Why isn't the error caught by testOpen()'s error handler? And how can I handle this error? I have an application where I'd like to cycle through many workbooks in a folder that have buggy code like this in their workbook_open() event, and I can't iterate through them if the program crashes on an unhandled error like this.

exception-handling vba excel
2 Answers

The reason that the error is not being handled is that the two processes are not in the same thread. If you were calling a 'helper' sub procedure from a main sub procedure, you remain in the same thread and errors thrown in the 'helper' are caught by error control in the main. This is akin to why an error in a procedure launched by Application.Run will not have thrown errors handled by the error control in the procedure that launched it.

To gain any measure of control over what happens in the newly opened workbook's Workbook_Open, you need to control things on the Application instance level. The following halts execution of the Workbook_Open event procedure; if it isn't necessary to process the code, then this could be your solution.

Application.EnableEvents = False
Set bk = Workbooks.Open(ThisWorkbook.Path & "\bkOpenErrorTest_dict.xlsb")
Application.EnableEvents = True

If the Dictionary populating is the specific error you are trying to overcome use the dictionary shorthand method that overwrites duplicates.

Dim dict As Dictionary

Set dict = New Dictionary
dict.Item(0) = 0
dict.Item(0) = 1
'dict.count = 1 with key as 0 and item as 1

More generally you can wrap potential errors in On Error Resume Next and On Error GoTo 0.

Dim dict As Dictionary

Set dict = New Dictionary
On Error Resume Next
dict.Add 0, 0
dict.Add 0, 1
On Error GoTo 0
'dict.count = 1 with key as 0 and item as 0
Admin1

The error is unhandled because the newly opened Workbook is running inside of what is basically an asychronous process - Workbook_Open is an event handler, so it is not being called from your code. It is being invoked as a callback function from inside whatever external Excel process is opening the document. You can demonstrate the same behavior with any event handler:

'In Sheet1
Sub Example()
    On Error GoTo Handler
    Sheet1.Cells(1, 1).Value = "Foo"
    Exit Sub

Handler:
    Debug.Print "Handled"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 1 Then
        Err.Raise 6
    End If
End Sub

If you need to bulk process the files, your only (easy) option is going to be disabling events before the call to open:

Sub testOpen()
    Dim bk As Workbook

    On Error GoTo errHandler

    Application.EnableEvents = False
    Set bk = Workbooks.Open ThisWorkbook.Path & "\bkOpenErrorTest_dict.xlsm"
    Application.EnableEvents = True

    Exit Sub

errHandler:
    Debug.Print "reached error handler"
End Sub

If for some reason it is vitally important that the buggy Workbook_Open runs, then you can use the solution Tim Williams outlines here. Just create a public wrapper function in the target workbook, then call that from within the context of your own error handler.

user_40888520
Related