Why does programmatically closing a workbook break OnTime?

[johnywhy]

(also posted to excelbanter.com and excelforum.com)

Why does programmatically closing a workbook break OnTime?

This code runs fine:

' ThisWorkbook:

Dim WithEvents oApp As Application


Private Sub Workbook_Open()
          Set oApp = Application
End Sub


Private Sub oApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
          Application.OnTime Now + TimeValue("00:00:05"), "MySub"
End Sub


' Module 1:

Sub MySub()
          Debug.Print "hello"
End Sub

Manually close another workbook to fire oApp_WorkbookBeforeClose. MySub executes, as expected.

But, if instead of manually closing another book, you close another book programmatically, OnTime never runs. oApp_WorkbookBeforeClose does execute, so the OnTime is getting registered. But it never runs.

' Immediate pane
OtherWorkbook.Close

oApp_WorkbookBeforeClose runs. But MySub never runs.

Why doesn't OnTime execute if registered by a Workbook_BeforeClose event? No code is running in the book that's closing. OnTime works no problem with other events (eg Workbook_Open). Somehow, closing a workbook breaks OnTime.

 

[JustoSapiente]

La definición global que pretende realizar de «oApp» mediante «Dim WithEvents», según puedo apreciar en la ayuda de Microsoft no es válida: esa opción para definir solo es válida en módulos de clase.

 

[Fluff]

Hi & welcome to MrExcel.
Please post to this board in English only. Thanks