MS-Excel / General Formatting

---

Prompting to Save Nonexistent Changes

You might have noticed that sometimes simply opening a workbook and taking a look around is enough to trigger Excel to prompt you to save changes to your personal macro workbook-despite the fact that you've made no changes whatsoever. Whether you know it or not, you most likely have a volatile function within your personal macro workbook.

A personal macro workbook is a hidden workbook created the first time you record a macro (Tools → Macro → Record NewMacro) and specify "Personal Macro Workbook" in the "Store Macro in" drop-down menu. It is opened each time you use Excel. A volatile function (or formula) is one that automatically recalculates each time you do almost anything in Excel, including opening and closing either the workbook or the entire application. Two of the most common volatile functions are the Today( ) and Now( ) functions.

So, although you might believe you've made no changes to the workbook at hand, those volatile functions running in the background might have. This counts as a change and triggers Excel's prompt to save said invisible changes.

If you want Excel to stop prompting you to save changes you didn't make, you have a couple of options open to you. The most obvious is not to store volatile functions within your personal macro workbook in the first place, and to delete any volatile functions that are already there. Or, if you need volatile functions, you can use this rather simple snippet of code to circumvent the check by tricking Excel into thinking your personal macro workbook has been saved the moment it opens:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Me.Saved = True
    End Sub

This code must live in the private workbook module of your personal macro workbook. To get there from any workbook, select View → Unhide under Windowoptions (pre-2007, Window → Unhide), select Personal.xls from Unhide Workbook, and click OK. Visit the VBE and enter the aforementioned code. Press Alt-Q to get back to Excel when you're done.

Of course, if you have a volatile function that you want to recalculate and you want to save the changes, you need to explicitly tell Excel to do so:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Me.Save
    End Sub

This macro will save your personal macro workbook automatically each time it is opened.

Stopping Excel's Warning Prompts for Recorded Macros One of the many drawbacks of recorded macros is that, although they're pretty good at mimicking just about any command, they tend to forget your responses to prompts. Delete a worksheet and you're prompted for confirmation; run a macro for the same and you'll still be prompted. Let's turn off those prompts.

Select Developer → Macros under Code options or Alt/Option-F8 (pre-2007, Tools → Macro → Macros) to bring up a list of your macros. Make sure "All Open Workbooks" is selected in the Macros In: box's pull-down menu. Select the macro you're interested in and click the Edit button. Put the cursor before the very first line of code-i.e., the first line without an apostrophe in front of it-and prepend the following:

Application.DisplayAlerts = False

At the very end of your code, append the following:

Application.DisplayAlerts = True

Your macro should look something like this:

    Sub MyMacro( )
    '
    ' MyMacro Macro
    ' Deletes the Active worksheet
    '
    '
     Application.DisplayAlerts = False
     ActiveSheet.Delete
     Application.DisplayAlerts = True
    End Sub

Note that you've turned alerts back on at the end of your macro to re-enable standard Excel prompts while working in Excel. Leave this out, and you'll see no alerts at all, not even those that might have been good to include.

If your macro does not complete for any reason-a runtime error, for instance-Excel might never get to the line of code that turns alerts back on. If this happens, it's probably wise to quit and restart Excel to set things back to the way they were.

Now you know how to use Excel without prompts. Be aware, though, that these prompts are there for a reason. Make sure you fully understand the purpose of a prompt before summarily turning it off.