Determine if Excel App is shutting down w/VBA

[RFraley]

I have a workbook that invokes the Workbook_BeforeClose sub routine when
shutting down. I would like to change the behavior of this routine dependant
upon whether the closing of the workbook is associated with the shutting
down of Excel (version 2003).

Thanks in advance for any assistance with this question.

Ralph

 

[Dave Patrick]

The file would close before the application shuts down so I don't think it's
possible to do from inside the box.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

 

[Chip Pearson]

Ralph,

The only way I can think of is to write a COM Add-In (CAI) and in the
AddinInstance_OnDisconnection event, test the value of RemoveMode. If it is
equal to ext_dm_HostShutdown ( = 0), then Excel is shutting down. If it is
<> 0, then the user closed the add-in and Excel is not shutting down.

I just wrote up a quick and dirty COM Add-In in VB6 to produce a log of the
Excel environment (workbooks open, add-ins, etc) to test the order of what
is getting closed when, and the CAI is getting unloaded before workbooks are
closed. Thus, you could put code in the OnDisconnection event of a CAI to do
whatever needs to be done in your application when Excel shuts down.

If you want a copy of the VB6 code (you can also write CAIs in VBA using the
same source code as in VB6), send me an email. I'll probably write something
about this on my web site in the next day or two.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Chip Pearson]

I should have added that this is not a fool-proof method. The user could
unload your COM Add-In without closing the Application, and in that case
your CAI would not be loaded when Excel is shutdown. Therefore, of course,
you would be unable to detect when Excel is finally shutdown.

The efficacy of this solution depends largely on the sophistication of the
users, in reverse correlation. The less the user knows about Excel, the more
effective this solution will be, since a novice user is unlikely to even
know your CAI is loaded, let alone know how to unload it. A more advanced
user is more likely to be poking around various add-ins and more likely to
unload your CAI.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Dave Patrick]

The other side is I see a lot of novice users that always close the
file..... then the application. They always start with the inner most X then
progressively move out X-ing their way.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

 

[NickHK]

Chip,
Do you plan on adding to your site on this subject ?
I'm interested, as I'm sure others are too.

Thanks

NickHK

 

[Chip Pearson]

Do you plan on adding to your site on this subject ?

It is presently in a "not ready for prime time" state. It will be there in a
day or two.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[NickHK]

Chip,
Whenever you're ready...

NickHK

It is presently in a "not ready for prime time" state. It will be there in a
day or two.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Chip Pearson]

Whenever you're ready...

See http://www.cpearson.com/excel/ExcelShutdown.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[NickHK]

Chip,
Ah...so that's how you do it.

Thanks

NickHK

 

[Peter T]

Hi Chip,

See http://www.cpearson.com/excel/ExcelShutdown.htm .

Referring to your link, 'Application.Run' after the OnDisconnection event
has fired is a revelation!

Previously I had tried to tried to raise an event (RaiseEvent) to be trapped
by a vba class in Excel. Although that worked if the COM was closed by user,
if triggered on close excel, by the time the event fired events are disabled
back in Excel. Even if that had worked a downside would be requirement of a
reference in the vba project to the COM.

Prior to that, I had passed details from vba to the COM via a public class
in the COM. Then in the OnDisconnection event 'do-stuff' with the variables.
This worked but it meant hard-coding 'do-stuff' in the COMl, albeit with the
received variables.

Now I know App-run works after Excel events become disabled I can combine
both methods, simplified -

In the COM -

'' in MultiUse class
Public Sub myMacroName(sMacro As String)
gsMacro = sMacro ' gsMacro public in a normal module in the COM
End Sub

'' in Connect
Private Sub AddinInstance_OnDisconnection( _
ByVal RemoveMode As _
AddInDesignerObjects.ext_DisconnectMode, _
custom() As Variant)
If RemoveMode = vbext_dm_HostShutdown Then
If Len(gsMacro) Then
On Error resume next
objApp.run gsMacro
End If
End If
End Sub

In VBA -

' normal module
Sub MyMacroToCom()
Dim s As String
Dim oClsEntry As Object

On Error GoTo errH
Set oClsEntry = CreateObject("myComName.MultiUseClassName")

s = "'" & ThisWorkbook.Name & "'!Bye"
oClsEntry.myMacroName s
Exit Sub

errH:
MsgBox Err.Description 'probably com not loaded
' code to load the com and try again
End Sub

Sub Bye()
MsgBox "bye"
ThisWorkbook.Worksheets(1).Range("A1") = Now
ThisWorkbook.Save
End Sub


This seems to be working for me without needing to use a Name in the
name-space and the associated code in vba. Also, an array or collection of
macro strings can be maintained in the COM allowing possibility of multiple
app-run's in the close.

I have also tried passing a/multiple workbook reference(s) together with
only the macro name to the Com. Ie for use in a vba template where the
workbook's name may be changed with saveas. The COM constructs the path from
the workbook reference name and adds the macro-name. This also seems to be
working but I'm not sure about possible consequences of stray object
variables being left behind.

Regards,
Peter T

 

[Chip Pearson]

I used the method of the hidden DLL name space (full credit for that concept
in general goes to Laurent Longre) to make the COM Add-In (CAI) as generic
as possible. I didn't want to require a reference be set to the CAI because
that adds yet another layer of complexity, and the whole scheme will fall
apart if the workbook is closed and Excel remains running. Also, it will
cause problems if the workbook is used on a machine that does have the CAI
installed or loaded. I wanted to avoid that requirement. I wanted the
configuration to be compatible with the circumstance when the CAI was not
present.

The names defined in the DLL Namespace persist even after the workbook that
created them is closed, so a permanent link (as long as Excel is running) is
established with these names. (See also
http://www.cpearson.com/excel/TrulyGlobalVariables.htm for another method of
creating values, Longs only in this case, that persist as long as Excel is
running, regardless of what workbooks are opened and closed. This method is
intended for one workbook to leave a message for another workbook after the
first workbook is closed. The names used in this method are not quite as
"hidden" as the names in hidden DLL namespace -- a user could use the
EnumProps API to see what properties were there and mischievously change a
property value, but that is beyond the capability of all but the most
advanced users. I decided to ignore that shortcoming.)

My only hesitation on using the Hidden DLL Namespace was based on how long
MS is going to support the old XLM language. Its been 12 years since it was
replaced with VBA, so it should be around for a while, but you never know.
MS got rid of Lotus script compatibility in 2002 and dumped VBA for the Mac
the last year (I think -- a Mac guy could confirm that). They may at some
point decide to dump XLM compatibility. The method described on the
TrulyGlobalVariables page is entirely based in Windows API functions, and is
completely independent of Excel (in can be used in any application that
supports VBA or in VB itself), so it is much less likely to be made obsolete
by MS. With the TrulyGlobalVariable procedures, all you need is the handle
of a window that will continue to exist as long as is necessary. You could
even use the Desktop Window in which case the variables would exist as long
as Windows was running, although I'll admit I haven't yet tested that
scenario.

I played around with Events when writing the ExcelShutdown code, but decided
not to use them because it added more complexity than it did utility. I
wanted to keep the CAI as simple and generic (and thus reliable and
flexible) as possible. I'm not really happy that the Namespace names must be
hard coded in both the workbook and the CAI, but since you can't enumerate
through those names, I couldn't think of a viable alternatative. I'm wide
open to suggestions for alternatives.

The VB6 project is really a single file (the Connect dsr file). Using events
and references would have required that the CAI be installed on every
machine that used the Excel workbook. By using the Hidden DLL Namespace, the
code in the workbook will still work (in this context I use the word "work"
to mean "not blow up") if the CAI is not present on the user's machine or is
not loaded. Nothing will happen, of course, if the CAI is not present or
loaded, but there will be no ill side-effects (e.g., missing reference
problems, compiler errors, etc), The macro names loaded into hidden name
space are just strings, and if the CAI is not present or loaded, they will
not be used. There are no side-effect to creating the strings if the CAI is
not present or loaded.

Anyways, that's that. I hope you find it useful. It was an interesting
intellectual exercise to write.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Peter T]

Thank you for the additional insights.

I confess I was a little nervous about using the NameSpace. I once suspected
problems I had some years ago with Excel were due to my messing around in
areas of the namespace I shouldn't, and without full understanding of what I
was doing. My hesitance to look there again may well be due to unfounded
prejudice. I understand the hidden (or partially hidden) namespace is stored
with the application, for curiosity would you know where.

As regards trying to keep things simple without a reference to the cai I
couldn't agree more. Indeed it would have been a drawback with the 'Event'
method, had it worked.

However in what I tried to outline a reference to the cai is not necessary.
Late binding with Createobject I think works fine, albeit a micro-tad
slower. I also think relatively simple in a workbook to test if the dll is
registered on users system CreateObject would fail (but it's a slow test).
Can also test for COMaddins("mycom").Connect = true, ie loaded as a Com not
merely as a created ActiveX.

Alternatively if the dll is at least registered, call a public sub in the
dll to check a flag that the cai's OnConnection event has fired.

IOW, I don't currently see a problem (ie side effects other than it won't
work) either as regards references or attempting to run when if the cai
doesn't exist. I should add that whilst what I described seems to be working
I wouldn't be at all surprised to find I've overlooked something (eg I've
not tested with multiple xl instances).

Whichever method, NameSpace or pass a variable(s) from the wb into the cai,
both rely on your observation that app-run can work at such a late stage in
the shutdown.

Regards,
Peter T