Why does programmatically closing a workbook break OnTime?

johnywhy

New Member
Joined
Sep 12, 2008
Messages
43
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. MacOS
(also posted to excelbanter.com and excelforum.com)

Why does programmatically closing a workbook break OnTime?

This code runs fine:

VBA Code:
' 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.

VBA Code:
' 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.
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

johnywhy

New Member
Joined
Sep 12, 2008
Messages
43
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. MacOS
Edit:
OnTime works no problem with other events (eg programmatically opening a workbook).

also posted to:
 
Last edited:

johnywhy

New Member
Joined
Sep 12, 2008
Messages
43
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. MacOS
Edit:
"Why doesn't OnTime execute if registered by a Workbook_BeforeClose event programmatically closing a workbook?"
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,295
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,306
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

This is weird !! :unsure:

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

1- In a Standard Module:
VBA Code:
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 :
VBA Code:
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

New Member
Joined
Sep 12, 2008
Messages
43
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. MacOS
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 !! :unsure:
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:

VBA Code:
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

New Member
Joined
Sep 12, 2008
Messages
43
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Dec 5, 2002
Messages
8,306
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Sep 12, 2008
Messages
43
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. MacOS
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.
 
Last edited:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,306
Office Version
  1. 2016
Platform
  1. Windows
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 ?
 

Forum statistics

Threads
1,140,933
Messages
5,703,239
Members
421,287
Latest member
Ravi bhatia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top