You could now save the workbook, but before you do so, you should determine the file type you need
and consider the security issues covered in the next section.
You can’t save the workbook as the default Excel Workbook (*.xlsx) type. This file format does not allow
macros to be included. You can save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) type,
which is in XLM format, or you can save it as an Excel Binary Workbook (*.xlsb) type, which is in a
binary format. Neither of these file types is compatible with previous versions of Excel. Another alterna-
tive is to save the workbook as an Excel 97-2003 Workbook (*.xls) type, which produces a workbook
compatible with Excel versions from Excel 97 through Excel 2003.
To develop macros with minimum interruption, work with Office 2007’s security restrictions. Without
getting into the complications of digitally signing your workbooks, you have a couple of simple options.
Select the Developer tab on the Ribbon and click the Macro Security button. You will see the Trust Center
dialog box, where you can select Macro Settings. Here you can enable all macros. This is not recom-
mended because it leaves you wide open to macro viruses.
Abetter alternative is to nominate a specific directory as a trusted location. Click Trusted Locations to
the left of the Trust Center dialog box. You probably already have a number of trusted locations, includ-
ing your XLSTART directory and templates directories. Use the Add new location button to specify a
suitable directory for storing your workbooks.
You should now save the workbook containing the newly recorded macro into the trusted location. Click
the Microsoft Office button and select Save As. In the Save as type drop-down, select the .xlsm type and
save the workbook in the trusted location as Recorder.xlsm.
The Personal Macro Workbook
If you choose to store your recorded macro in the Personal Macro Workbook, the macro is added to a
special file called Personal.xlsb, which is a hidden file that is saved in your Excel Startup directory
when you close Excel. This means that Personal.xlsbis automatically loaded when you launch Excel
and, therefore, its macros are always available for any other workbook to use.
If you can’t see the file extensions, such as .xlsm, in the Save As dialog box, you
should open Windows Explorer, click the Tools menu, and choose Folder Options.
Inthe View tab, remove the check against Hide extensions for known file types.
It is important to remember to stop the recorder. If you leave the recorder on and try
to run the recorded macro, you can go into a loop where the macro runs itself over
and over again. If this does happen to you, or any other error occurs while testing
your macros, hold down the Ctrl key and press the Break key to interrupt the macro.
You can then end the macro or go into debug mode to trace errors. You can also inter-
rupt a macro with the Esc key, but it is not as effective as Ctrl+Break for a macro that
is pausing for input.
Chapter 1: Primer in Excel VBA