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]

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.

 

[RoryA]

If you want to know why, you'll probably have to ask Microsoft.

 

[johnywhy]

If you want to know why, you'll probably have to ask Microsoft.

⊙_☉ Have you tried their forum? 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]

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

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]

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]

moved to new question

 

[johnywhy]

might be relevant

Can't close book programmatically inside of Workbook_BeforeClose if triggered programmatically

i'd like to intercept BeforeClose with my own process, and cancel the default process. Not working as expected: Create a new workbook, enter the following, and save: ThisWorkbook module: Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = True...

www.mrexcel.com

 

[johnywhy]

This other thread is relevant, because it's another feature which is inexplicably broken when a book is closed programmatically.

Can't close book programmatically inside of Workbook_BeforeClose if triggered programmatically

i'd like to intercept BeforeClose with my own process, and cancel the default process. Not working as expected: Create a new workbook, enter the following, and save: ThisWorkbook module: Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = True...

www.mrexcel.com

 

[RoryA]

That's basically the same question, and it's also clearly why, not how do I work around it, so...

 

[johnywhy]

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.