How to handle error generated inside another workbook's Workbook_Open event?
How to handle error generated inside another workbook's Workbook_Open event?
I have two workbooks in the same folder: bkOpenErrorTest.xlsm and bkOpenErrorTest_dict.xlsm.
bkOpenErrorTest.xlsm
bkOpenErrorTest_dict.xlsm
bkOpenErrorTest_dict.xlsm has the following code in its ThisWorkbook module:
bkOpenErrorTest_dict.xlsm
ThisWorkbook
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:
bkOpenErrorTest.xlsm
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.
Break on Unhandled Errors
testOpen()
bkOpenErrorTest_dict.xlsm
testOpen()
workbook_open()
Workbook_Open
@urdearboy thanks, that's a possibility, but i would like to trap errors selectively, so that e.g. if the workbook has trouble opening due to corruption, we would handle that error differently (e.g. by skipping that workbook in the loop)
– sigil
Aug 31 at 22:09
If that is the specific error you are trying to overcome either use the dictionary shorthand method that overwrites duplicates or more generally wrap potential errors in On Error Resume Next and On Error GoTo 0.
– Jeeped
Aug 31 at 22:25
2 Answers
2
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
His problem is not the error with the dictionary...
– Pm Duda
Aug 31 at 22:34
... and when he comes to the realization that that is simply never going to happen, then he might consider these options.
– Jeeped
Aug 31 at 22:40
Shouldn't you then in your answer inform him, that what he want's is never going to work?
– Pm Duda
Aug 31 at 22:42
Perhaps I will after I find time to set up multiple external workbooks and test every scenario and possible solution I can think of. For now, the fact that the spawned workbook is not in the same thread as the calling sub procedure is the reason that the calling sub procedure's error handling is not covering the error thrown in the new workbook.
– Jeeped
Aug 31 at 22:51
@sigil - It crashes because you have events turned on. Turn them off to avoid the events, then change the code once you have a valid reference.
– Comintern
Aug 31 at 23:14
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:
Workbook_Open
'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.
Workbook_Open
As I mentioned in my comment on Jeeped's answer, modifying the code in the target workbook is impractical because of the volume of workbooks that I need to address.
– sigil
Aug 31 at 23:08
Then this answer should explain why what you want to do is impossible. ;-)
– Comintern
Aug 31 at 23:09
@sigil Note also that you don't have to save the code you emit into the workbooks via the linked answer - you just have to call it.
– Comintern
Aug 31 at 23:10
if the program crashes when the workbook opens, then the VBA-modifying code cannot be inserted in order to be run (as the program that would insert the code is no longer running).
– sigil
Sep 1 at 0:02
@sigil Look at the code in the answer again. If you disable the events the
Workbook_Open event won't run, so it can't crash.– Comintern
Sep 1 at 0:05
Workbook_Open
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
You can try disabling events - unless you need the
Workbook_Openevent to run– urdearboy
Aug 31 at 22:05