Why does programmatically closing a workbook break OnTime?

johnywhy

New Member
Joined
Sep 12, 2008
Messages
47
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Edit:
OnTime works no problem with other events (eg programmatically opening a workbook).

also posted to:
 
Last edited:
Upvote 0
Edit:
"Why doesn't OnTime execute if registered by a Workbook_BeforeClose event programmatically closing a workbook?"
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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
Back
Top