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:
Yes, that's what I meant to say.

BTW, did the workaround I suggested work for you ?

Have not tried it -- the workaround i posted works well, and much simpler. I don't yet see the advantage of your solution over the CloseWorkbook wrapper.

Question: is your code asynchronous? That is, does it fire before, during, or after the save process of the closing-book? The CloseWorkbook wrapper waits for the save to finish. If yours does too, then how is the behavior of your solution different than the CloseWorkbook wrapper?

While it's great to have two potential solutions, neither workaround answers the question of the OP - Why doesn't the event-sink work for programmatic closes? The answer to that question might lead to a solution which isn't a workaround -- that is, a solution which makes the native Close event work with programmatic closes.
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
If you want to know why, you'll probably have to ask Microsoft.
 
Upvote 0
If you want to know why, you'll probably have to ask Microsoft.
⊙_☉ Have you tried their forum? :p Sadly, i don't have a paid MS support package.

i find places like mrexcel to be more fruitful ,with lots of knowledgeable experienced people.
 
Upvote 0
I have managed to cheat my way by using the following workaround :

VBA Code:
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
The advantage of your API solution is it works with the normal Workbook_BeforeClose event. Clients to your code can therefor use the normal `.Close` syntax. That's great.
 
Upvote 0
Have you tried their forum?
Yes, which is why I wasn’t suggesting it! ;)

It seems likely this is either a bug or a very obscure design decision. The reasons for either one I can’t see being discoverable outside of the Excel team. I don’t have access to them anymore, so you might try and find an MVP who does?
 
Upvote 0
Upvote 0
This other thread is relevant, because it's another feature which is inexplicably broken when a book is closed programmatically.
 
Upvote 0
That's basically the same question, and it's also clearly why, not how do I work around it, so...
 
Upvote 0
That's basically the same question, and it's also clearly why, not how do I work around it, so...
There are similarities, mainly that they both concern the effect on the BeforeClose event, when programmatically closing a workbook.

It's not the same question. This thread is about OnTime. The other thread is about executing a subsequent Workbook.Close after canceling the first Workbook.Close.

i think it may be the same answer -- we don't know at this point.
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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