1.8 Saving Your Recorded Macro
When you record a macro, it is automatically saved in the location you chose when you started the Macro Recorder. It is not permanently saved, however, until the workbook you chose to store the macro in is saved to disk.
For example, if you chose This Workbook (that is, the active workbook) in the Record Macro dialog when you started the Macro Recorder, your recorded macro is not permanently saved until you save changes to the active workbook.
If you chose the Personal Macro Workbook as the location in which to store your recorded macro, your macro is not permanently saved until you close Excel. If there are any unsaved changes in Personal.xlsb, Excel prompts you to save those changes when you close Excel.
If you chose This Workbook or New Workbook as the location in which to store your macro, you must change the workbook format from a standard .xlsx file to a macro- enabled workbook format, with the .xlsm extension.
If you try to save a workbook that has macros in it to the standard .xlsx format, Excel will display a warning similar to the one shown in Figure 1.7. If you see this warning, and you want to save your recorded macros, you must click the No button, and save your workbook into the macro-enabled format (.xlsm).
|
|
|
Figure 1.7 – Excel's warning when saving a workbook containing macros to standard format. |
If you have the warning dialog shown in Figure 1.7 on your screen, save your workbook as a macro-enabled workbook by following these steps:
-
Click No in the warning dialog. Excel returns you to the Save dialog.
-
In the Save as type list, choose Excel Macro-Enabled Workbook (*.xlsm).
-
Click the Save button. Excel saves your workbook as a macro-enabled workbook.
If you do not convert your workbook to macro-enabled (.xlsm) format, Excel will remove all macro/VBA code from the workbook when it is saved. Any macros or VBA code you have written in the workbook will be lost.
If you want to proactively convert or save your workbook in macro-enabled format, follow these steps:
-
Click the File tab; Excel displays the "backstage" window.
-
In the column at the left of the backstage window, click Save As. Excel displays the Save As window
-
Click the folder you want to save your workbook in, or click Browse; Excel displays the Save As file dialog.
-
In the Save as type list, choose Excel Macro-Enabled Workbook (*.xlsm).
-
Click the Save button to save your workbook in the macro-enabled format.
For security reasons, Excel and most MS Office applications make a distinction between files that contain macro/VBA code and files that do not. This is to help prevent malware that might be in a workbook from being executed.
When you open an .xlsm workbook, Excel typically disables the VBA content of the workbook – you will see a notification about disabled content under the Excel ribbon. Choose the "Enable content" option to enable your macros for the current work session.
When you choose Enable Content, Excel shows you a dialog asking if you want to make the workbook a "Trusted Document." If you click Yes to this prompt, the VBA code in the workbook will always be enabled when you open it.
You may also want to consider using the Excel Trust Center settings to "trust" the location where you store your macro-enabled workbooks. Workbooks opened from a trusted location have their VBA/macro content enabled automatically.
Refer to the Excel help system for more information about Trusted Documents and using the Trust Center settings.