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.

 

[johnywhy]

Edit:
OnTime works no problem with other events (eg programmatically opening a workbook).

also posted to:

Why does programmatically closing a workbook break OnTime?

(also posted to mrexcel.com and StackOverflow) Why does programmatically closing a workbook break OnTime? This code runs fine: ThisWorkbook: Dim WithEvents oApp As Application

www.excelforum.com

 

[johnywhy]

Edit:
"Why doesn't OnTime execute if registered by a Workbook_BeforeClose event programmatically closing a workbook?"

 

[johnnyL]

If you are positive that the 'oApp_WorkbookBeforeClose' is being executed, all I can guess is to increase the time from 5 seconds to like 59 seconds. If that works then reduce the time back down gradually until you find a lower working value. I doubt this solution will work, but ?

How are you sure that the 'oApp_WorkbookBeforeClose' is being executed?

 

[Jaafar Tribak]

I have tested this and like you say, OnTime no longer works if the workbook is programmatically closed.

This is weird !!

I have managed to cheat my way by using the following workaround :

1- In a Standard Module:

Option Explicit

#If Win64 Then
    Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    
    Dim hwnd As LongPtr
#Else
    Private Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Private Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
    
    Dim hwnd As Long
#End If


Public Sub CloseWorkbookProgrammatically()
    '\ change workbook name as required
        OtherWorkbook.Close
End Sub

Public Sub OnTimeProc()
    Call KillTimer(hwnd, 0)
    MsgBox "hello world"
End Sub

Public Sub SaveAppHandle(ByVal App As Application)
    hwnd = App.hwnd
End Sub

#If Win64 Then
    Public Sub SetOntimeProc(ByVal RunWhen As Variant, ByRef FuncAddr As LongLong)
#Else
    Public Sub SetOntimeProc(ByVal RunWhen As Variant, ByRef FuncAddr As Long)
#End If

    Call SetTimer(hwnd, 0, RunWhen * 1000, FuncAddr)
End Sub

2- In the ThisWorkbook Module :

Option Explicit

Private WithEvents oApp As Application

Private Sub Workbook_Open()
    Call SaveAppHandle(Application)
    Set oApp = Application
End Sub

Private Sub oApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    '\Run ontime procedure after 2 seconds from now
        Call SetOntimeProc(2, AddressOf OnTimeProc)
End Sub

 

[johnywhy]

How are you sure that the 'oApp_WorkbookBeforeClose' is being executed?

Stepping thru code. Thx for idea to experiment with the delay time, but i agree it seems doubtful that would help.

This is weird !!

i know, right!?

I have managed to cheat my way by using the following workaround

Wow, i applaud your thorough code. Good job!

i'd love to know why this is happening. Maybe there's some simple fix, like a judiciously placed DoEvents or something.

Someone on Stack Overflow suggested this solution:

Instead of using the .Close method, eg OtherWorkbook.Close, create a CloseWorkbook method:

Sub CloseWorkbook (Wb As Workbook)
     Application.OnTime Now, "Workbook_AfterClose"
     Wb.Close
End Sub

This OnTime event works, for books that need to be closed programmatically.

An OnTime of Now will wait until Wb is fully closed (including saving). This achieves a Workbook_AfterClose event, which was my goal in the first place.

 

[johnywhy]

I like my above CloseWorkbook method. But it does mean the OnTime statement appears twice in the code, which is redundant. And it doesn't make the normal Workbook_BeforeClose function as expected on programmatically-closed books.

 

[Jaafar Tribak]

I like my above CloseWorkbook method. But it does mean the OnTime statement appears twice in the code, which is redundant. And it doesn't make the normal Workbook_BeforeClose function as expected on programmatically-closed books.

Using the suggested CloseWorkbook wrapper sub makes the whole idea of sinking the application events and the Workbook_BeforeClose redundant.

 

[johnywhy]

Using the suggested CloseWorkbook wrapper sub makes the whole idea of sinking the application events and the Workbook_BeforeClose redundant.

Not redundant for non-programmatic (user-initiated) workbook-close. That's still handled by the oApp_WorkbookBeforeClose event.

 

[Jaafar Tribak]

Not redundant for non-programmatic (user-initiated) workbook-close. That's still handled by the oApp_WorkbookBeforeClose event.

Yes, that's what I meant to say.

BTW, did the workaround I suggested work for you ?