Excel: Avoiding "Save Changes?" When Closing a Macro Sheet

ID: Q63815

The information in this article applies to:

Microsoft Excel for Windows, versions 2.1, 3.0, 4.0
Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0
Microsoft Excel for OS/2, versions 2.2 and 3.0

SUMMARY

A Microsoft Excel for the Macintosh version 2.2, 3.0 or 4.0 macro sheet can be changed just by running a macro. If these changes do not need to be saved, using SAVE.AS(,0) causes Microsoft Excel to ignore any changes that have taken place, allowing the macro to close without an alert.

There are two benefits of using SAVE.AS(,0) when the macro sheet doesn't need to be saved. The first benefit is that the "Save Changes?" message is not displayed when you close the macro sheet. The second benefit is that you do not waste time by performing an unnecessary save to disk, initiated either by the macro or by Yes being chosen in response to the "Save Changes?" message.

The two forms of the SAVE.AS command are as follows:


   =SAVE.AS(name_text,type_num,passwd_text,backup)
   =SAVE.AS?(name_text,type_num,passwd_text,backup)

A feature in Microsoft Excel is that a "type_num" argument of 0 (zero) can be specified to save the active document in a sort of "null" file format--in effect, not saving it at all. However, Microsoft Excel will act as if the document has been saved to disk.

Note that SAVE.AS(,0) will signal Microsoft Excel that the active document has been saved. Hence, it is very important that the macros that use SAVE.AS(,0) first activate the appropriate document. Issuing SAVE.AS(,0) with the wrong document active could result in important changes not being saved.

When to Use SAVE.AS(,0) in Macros

In a macro with a custom menu and a clear exit point, using SAVE.AS(,0) is relatively straightforward. The macro that is invoked when exiting the application can activate the macro sheet and issue SAVE.AS(,0).

If the macro has no clear exit point, SAVE.AS(,0) can be issued from an auto_close macro. However, with auto_close, the "Save changes?" message appears before auto_close is invoked. If auto_close is inappropriate, SAVE.AS(,0) can be issued in the particular instance (or instances) where the macro is changed.

For more information on the SAVE.AS function, refer to pages 369-370 in the "Microsoft Excel Function Reference," version 4.0. If you are using version 3.0, see pages 204-205 in the "Microsoft Excel Function Reference" version 3.0 manual. If you are using Microsoft Excel version 2.2, see page 272 of the "Microsoft Excel Functions and Macros" version 2.2 manual.

Additional query words: 2.1 2.20 2.21 3.0

Keywords :
Version :
Platform :
Issue type :