Managing Macros

Tips, Tricks, and Answers

The following articles are available for the 'Managing Macros' topic. Click the article's title (shown in bold) to see the associated article.

Aborting a Macro and Retaining Control
If you need to exit a macro before it is finished running, you can do it using a brute force method, or you can build in a way to gracefully exit. This tip discusses the latter way to get out of a macro and still retain a modicum of control.

Automating Copying Macros
You can manually copy macros from one workbook to another, but what if you want to automate the copying process? Here's some ideas you can try out.

Clean Up Your Macro List
Over time you may accumulate a whole collection of macros you no longer need, particularly if those macros were created for only one or two uses. You can help keep your Excel environment clean and tidy by deleting these unneeded macros periodically.

Disabling Shift Key Use when Opening a Workbook
Open up a workbook, and Excel normally runs the macros associated with that workbook. You can disable the automatic running of macros by holding down the Shift key while the workbook opens. If you want to defeat this ability, you may be out of luck.

Generating a List of Macros
Creating a macro to compile a list of macros in an Excel workbook.

Hiding Macros
Macros are normally visible if you take a look at the Macros dialog box. There are ways you can make sure they are not included in the dialog box, and the methods only take a small change or two.

List of Macro Shortcuts in All Open Workbooks
Need a list of macro shortcut keys? It's not as easy in Excel as in some other Office applications, such as Word. It can be done, however, with a little ingenuity, as described in this tip.

One Shortcut for Two Macros
Macros and the shortcuts used to invoke those macros are stored on a workbook-by-workbook basis. If you load two workbooks, each of which uses the same shortcut to run a macro, the results may seem unpredictable. This tip explains how Excel deals with the conflicting shortcuts and how you can work around the apparent conflict.

Opening Personal.xlsb in Excel 2007
If you start up Excel 2007 and all you see is your Personal.xlsb file, then you may wonder what you are doing wrong. The answer is nothing—all you need to do is make sure that the file is hidden, as discussed in this tip.

Recovering Macros from Corrupted Workbooks
Workbooks get corrupted from time to time; that's a fact of life in an Excel world. If those corrupted workbooks contain macros that you need to recover, there are a few steps you can go through to get them back.

Removing a Directory
When writing macros, you may have the need to remove a directory that you previously created. You can use the RmDir function, described in this tip.

Renaming a File
Need to rename a file in a macro? It's easy to do using the Name command, as discussed in this tip.

Transferring an Older Personal.xls to Excel 2007
As you update your computer equipment, it is not unusual to also upgrade to a newer version of your software. With Excel, this may mean updating some of your older customizations to work with the newer system.