Application.Quit doesn't quit?

[Dr Fear]

I wonder if anyone could explain why the following code reaches the message box "Why am I here?" and Application.Quit doesn't do what it says? When this code is called by hitting the top right X, at least.

(By the way - why am I using wb as Window instead of as Workbook? Because some windows are hidden, so don't appear in the Workbooks list.)

This code is in ThisWorkbook:



Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim wb As Window
Dim username As String

Application.EnableEvents = False

username = LCase(Environ("USERNAME"))
For Each wb In Application.Windows
' automatically save any audit sheets
If Left(wb.Caption, Len(username)) = username Then
Workbooks(wb.Caption).Save
End If
' just close anything else (use system to ask if save changes)
' except this sheet itself (else the loop stops)
If wb.Caption <> ThisWorkbook.Name Then
Workbooks(wb.Caption).Close
End If
Next wb

' and finally close this (last remaining open) workbook
Application.Quit
MsgBox "Why am I here?"

End Sub

[Aflatoon]

(By the way - why am I using wb as Window instead of as Workbook? Because some windows are hidden, so don't appear in the Workbooks list.)


That should not be true. How did you arrive at that conclusion?

Does the application quit if you remove the MsgBox line?

[Dr Fear]

Windows vs Workbooks - by trial and error. For each wb in Workbooks ... did not find the hidden workbooks (wb did not take the name of any hidden workbooks throughout the loop). Also there is something in the Help about Application.Windows including ALL windows including hidden.

I do kinow that if Workbooks(2) is hidden then Workbooks(2).Close works - but I could not find it using For Each. I am also baffled by this behaviour but decided life was too short to spend on finding out why, given the Windows() solution seemed to fix it.

The application doesn't quit without the MsgBox line. Any code after that .Quit is executed.
Actually let me expand: the close operation goes ahead (after the end of _BeforeClose is reached), but any code following Application.Quit is executed first.

If there are two non-hidden workbooks open at the time of the X it depends which is active.

If the sheet containing this code is the active sheet then everything closes after the "Do you want to save changes ..." dialogs.

If the second or subsequent workbook opened is active when you hit X, then after the save changes dialogs all visible sheets remain on screen (with all their macros dysfunctional of course).

I have noted that when you hit X, you go into the _BeforeClose macro of the first workbook opened regardless of which is active. Or at least, that is what I have always observed to date, it is possible I suppose that the behaviour is even more quirky than that.

[Aflatoon]

Code that appears after the Application.Quit line will naturally run since you have to finish the sub being executed. Why would you have code after it?

[Dr Fear]

But why doesn't .Quit, quit? .Close closes (mid subroutine if you code ThisWorkbook.Close)

But even if .Quit did not come into effect until the end of the routine, why doesn't it work even then?

[Aflatoon]

Because there are other workbooks to take into consideration - it would be extremely bad programming if it simply terminated the program without allowing anything else to do any necessary clean up. If you are just closing your own workbook, that's fine if you simply want to terminate it abruptly (not good practice in my opinion though).
The Quit method should work however, subject to any necessary dialogs thrown up by the application, just as if you quit manually. Does it not even try to quit?

[Dr Fear]

Actually it is "taking the other workbooks into account" that I am trying to achieve!.

I really want to close them all through their own close, however, I see that the _BeforeClose routine of all but the first is NOT called by my wb.Close call.

Then, when I get to the one remaining workbook left, I want to close it and have Excel quit (following the user X command in the beginning). If I use ThisWorkbook.Close then Excel stays open (empty), if I use Quit, it doesn't [always] quit ....

Is there any other way of getting Excel to close the current workbook AND then shut down completely?

[Dr Fear]

Sorry, of course the _BeforeClose of the other workbooks is not called, the code I put up at the top of this thread starts with disabling events. The idea being to simplify so I could be sure no other events routines were being triggered. Ulitmately however, I would like the events on during the closedown ...

[Aflatoon]

Can you not just re-enable events, close the other workbooks, then quit the application?

[Dr Fear]

Yes I will probably give up on the current idea and simply have each sheet save it's own associated hidden sheet in its own _BeforeClose with events enabled.

But it doesn't answer the question as to why .Quit doesn't Quit! (regardless of the wisdom of using it at all, that is.)

[Aflatoon]

It's the equivalent of sending a normal quit message to the application - it does not simply terminate the whole thing abruptly (thankfully).