Microsoft KB Archive/112784

= Microsoft Knowledge Base =

Excel: Macro to Close All Open Add-ins
Last reviewed: September 12, 1996

Article ID: Q112784

The information in this article applies to:

- Microsoft Excel for Windows, version 3.0, 4.0, 4.0a - Microsoft Excel for Macintosh, version 3.0, 4.0 - Microsoft Excel for OS/2, version 3.0

SUMMARY
In Microsoft Excel, the Close All command (accessed by pressing SHIFT and selecting the File menu) does not close any open add-in macros. This behavior is by design; add-in macros are meant to become part of the application when they are loaded.

The Close All command closes add-in macros that have been opened as normal macro sheets (that is, you opened the macro sheets pressing and holding down the SHIFT key).

MORE INFORMATION
Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided 'as is' and Microsoft does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of the code to suit customer requirements for a particular purpose.

To close all open add-ins, use the following macro:

A1: =ECHO(FALSE) A2: =SET.NAME("AddinList",DOCUMENTS(2)) A3: =IF(ISERROR(AddinList),RETURN) A4: =SET.NAME("Total",COLUMNS(AddinList)) A5: =ERROR(FALSE) A6: =FOR("Counter",1,Total) A7: =SET.NAME("ActiveSheet",INDEX(AddinList,1,Counter)) A8: =ACTIVATE(Activesheet) A9: =RUN(2) A10: =FILE.CLOSE A11: =NEXT A12: =ERROR(TRUE) A13: =RETURN

Explanation of above macro code.

A1: Turns off screen redrawing.

A2: Defines array called "AddinList" to names of open add-ins. Note: you

must enter this formula as an array. To do this:

Microsoft Excel for Windows or OS/2 : CTRL + Shift + Enter Microsoft Excel for the Macintosh  : Command + Enter A3: Checks to see if no add-ins are open.

A4: Sets variable "Total" equal to the number of add-ins that are open.

A5: Turns off any error messaging.

A6: Starts loop for each add-in listed in variable "AddinList."

A7: Sets variable "ActiveSheet" to the add-in listed in AddinList.

A8: Activates the add-in.

A9: Runs the Auto_Close macro for the add-in if it exists. This Auto_Close

macro may be set up to remove the item from the menu or to disable an    ON.TIME function. A10: Closes the add-in.

A11: Ends the loop.

A12: Resets error messaging.

A13: Ends the macro.