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:

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
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,332
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you want to know why, you'll probably have to ask Microsoft.
 

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
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.
 

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
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,332
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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?
 

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
moved to new question
 

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

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
This other thread is relevant, because it's another feature which is inexplicably broken when a book is closed programmatically.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,332
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
That's basically the same question, and it's also clearly why, not how do I work around it, so...
 

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
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.
 

Forum statistics

Threads
1,140,932
Messages
5,703,234
Members
421,285
Latest member
Bebek

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