Note: COM Add-Ins were added in Office 2000. They are not supported in Office 97 or earlier versions.

Detecting the shutdown mode is the easy part. The trickier part is to be able to communicate this event back to Excel, so a workbook can perform some action that may be required when Excel shuts down. The communication in this direction, from the CAI back to Excel, is accomplished via Excel's Hidden Name Space, and area of memory usually used by DLLs to store temporary names. Laurent Longre wrote an article for this site, and I have expanded upon that article to create a module of VBA procedures that work with this hidden name space. Read more about it and download the code here. Your VBA Project will need to import the modHiddenNames module available on that page or right here.

First of all, load the COM Add-In. By default, the CAI will load when you start Excel. Then in the workbook via in which you want to detect Excel shutting down, import the modHiddenNames module. In the VBA code of that workbook, define two constants exactly as shown below.

    Private Const C_RUN_ON_USER_CLOSE = "RunOnUserClose"
    Private Const C_RUN_ON_HOST_SHUTDOWN = "RunOnHostShutdown"

The CAI will look for these exact names, so do not change the values of the constants. You may, however, change their scope from Private to Public if you desire. Next, your VBA code must create two names in Excel's hidden name space. Names created in this manner will remain in existence as long as Excel is running, even after the workbook that created the names closes. You add names to the hidden name space using the AddHiddenName procedure in the modHiddenNames module. The NAME of the hidden name MUST be the value of one of the constants shown above. The VALUE associated with the name is the names of the macro, qualified with the workbook name, that you want to run when the user closes the CAI manually (the macro associated with the C_RUN_ON_USER_CLOSE constant name) and/or the macro that you want to run when Excel itself closes (the macro associated with the C_RUN_ON_HOST_SHUTDOWN constant name).

For example, you can assign the macro named RunThisOnUserClose to the name defined by C_RUN_ON_USER_CLOSE  with the following code:

    AddHiddenName HiddenName:=C_RUN_ON_USER_CLOSE, _
        NameValue:="'" & ThisWorkbook.Name & "'!RunThisOnUserClose", OverWriteExisting:=True

Similarly, you can assign the macro name RunThisOnHostShutdown to the name define by C_RUN_ON_HOST_SHUTDOWN  with the following code:

    AddHiddenName HiddenName:=C_RUN_ON_HOST_SHUTDOWN, _
        NameValue:="'" & ThisWorkbook.Name & "'!RunThisOnHostShutdown", OverWriteExisting:=True

See the document on the Hidden Names Page for more details about the AddHiddenName procedure. If the name of the workbook containing the macros contains spaces, you must enclose the workbook name in apostrophes. E.g.,

'Book One.xls'!MacroName

The macros assigned to the hidden names cannot accept any parameters, nor can they return a value. They must be simple Sub procedures.

This all may seem somewhat convoluted, but all it really does is create a name that is known by and accessible to both Excel and the CAI, and then the CAI simply executes (using Applicaiton.Run)  the macro that is the value associated with the name. The macro, once called by the CAI, can do anything it needs to do. There are no restrictions on what can be done with the macro, with the exception that it cannot cancel the Excel shutdown (there is not way to do that).

You can download a zip file containing everything you need to get started here. This file contains the Visual Basic 6 Project files, the TestExcelShutdown.dll file itself, and a sample workbook called TestExcelShutdown.xls. To load the CAI, simply go to the Tools menu in Excel, choose the COM Add-Ins button, and check "Test Excel Shutdown". (If you don't have the COM Add-Ins button on your Tools menu, you will need to add it.)