Saving Macro-Enabled Workbooks and Managing Security in Microsoft Excel

Course: Advanced Excel Training

Topic: Saving Macro-Enabled Workbooks and Managing Security

Course Link: Advanced Excel Course at JustAcademy

Demo Link: Register for a Course Demo

Website: Visit JustAcademy

1. Introduction

Microsoft Excel supports automation through macros and Visual Basic for Applications (VBA). Macros can automate repetitive tasks such as formatting worksheets, preparing reports, cleaning data, and performing calculations. However, saving a workbook that contains macros requires choosing the correct file format and understanding Excel's security controls.

A workbook saved in an incompatible format may lose its VBA code. In addition, macros from untrusted sources can contain harmful instructions that modify files, access information, or perform unwanted actions. Therefore, Excel users must understand how to save macro-enabled workbooks correctly and manage security responsibly.

This topic explains macro-enabled file formats, saving procedures, trusted documents, macro security settings, and practical methods for protecting workbook automation.

2. Learning Objectives

• Understand the difference between standard and macro-enabled Excel files.
• Identify file formats that support VBA macros.
• Save a workbook as an Excel Macro-Enabled Workbook.
• Understand what happens when a macro-enabled file is saved as .xlsx.
• Recognize Excel macro security warnings.
• Understand trusted documents, trusted locations, and digital signatures.
• Apply safe practices when receiving or sharing macro-enabled files.
• Troubleshoot common saving and security problems.
• Build a secure workflow for storing and distributing automated Excel reports.

3. What Is a Macro-Enabled Workbook?

A macro-enabled workbook is an Excel file that can store VBA code along with worksheet data, formulas, formatting, charts, and other workbook content. It allows users to save automation procedures and execute them when permitted by Excel's security settings.

For example, a finance team may use a macro to format monthly financial statements, update report layouts, or prepare a summary worksheet. To retain the VBA code inside the workbook, the file must be saved in a format that supports macros.

Common Excel File Formats

Key point: Use .xlsm when you need a standard Excel workbook that preserves its VBA macros. Other formats, such as .xlsb and .xltm, serve different purposes and can also support VBA.

4. Why Is the Correct File Format Important?

When a workbook contains macros, saving it in the correct format is essential. A standard .xlsx file is designed for workbook content without embedded VBA projects. If you attempt to save a workbook containing VBA code as .xlsx, Excel may display a warning that the VBA project cannot be saved in that format.

What Can Happen If You Choose the Wrong Format?

• The VBA project may be removed from the saved file.
• Macro buttons may no longer work because their assigned procedures are missing.
• Automated report preparation steps may stop functioning.
• Users may need to restore a previous macro-enabled copy or recreate the code.
• Some workbook features may be lost when saving in formats such as CSV.

Before changing a file format, read any compatibility warning carefully. If you need to preserve macros, select a macro-enabled format rather than continuing with a format that removes VBA.

5. How to Save a Workbook as .xlsm

Follow these steps when your workbook contains recorded macros or VBA procedures that must remain available.

Step-by-Step Procedure

1. Open the Excel workbook containing the macro.
2. Click File on the ribbon.
3. Select Save As. Depending on your Excel version, you may need to select Browse or another location option.
4. Choose the folder where you want to save the workbook.
5. Enter a descriptive filename, such as MonthlySalesAutomation.
6. Open the Save as type or file format dropdown.
7. Select Excel Macro-Enabled Workbook (*.xlsm).
8. Click Save.
9. If Excel displays a compatibility message, confirm that you want to retain the macro-enabled format.
10. Close and reopen the saved file when appropriate, then check that the macro is still available.

Example

Suppose you create a workbook named SalesReport.xlsx and record a macro called FormatSalesReport. To preserve that macro, save the workbook as SalesReport.xlsm. The macro can then remain stored in the workbook, subject to Excel's security settings.

Important: Changing the file extension manually in the filename is not the correct way to convert a workbook. Use Excel's Save As file type option to create the appropriate format.

6. Understanding the Personal Macro Workbook

The Personal Macro Workbook is a hidden Excel workbook commonly named PERSONAL.XLSB. It can store macros that you want to use across multiple Excel workbooks on the same computer.

When Should You Use It?

• When you repeatedly apply the same formatting to different reports.
• When you use common data-cleaning procedures across multiple files.
• When you want to access personal macros whenever Excel starts.

Important Considerations

• Macros stored in PERSONAL.XLSB are not automatically embedded in every workbook you open.
• Sharing a separate workbook does not automatically share the macros stored in your Personal Macro Workbook.
• Back up the Personal Macro Workbook if it contains important automation code.
• Only store trusted macros in it because the workbook may load automatically when Excel starts.

7. Understanding Excel Macro Security

Macro security controls whether Excel allows VBA code to execute. Since macros can change workbook content and perform other actions, Excel uses security settings and warnings to help protect users from potentially harmful code.

A macro-enabled extension such as .xlsm indicates that a workbook can contain macros. It does not mean that the macros are automatically safe or that Excel will necessarily allow them to run.

Why Macro Security Is Necessary

• Malicious macros may delete, alter, or corrupt files.
• Macros can perform actions that the user did not expect.
• Untrusted workbooks may contain code designed to compromise a computer or organization.
• Security policies help control which macros can execute.

For this reason, never enable macros simply because a workbook displays a warning. First confirm that the file comes from a trustworthy source and that its purpose is understood.

8. Types of Macro Security Settings

In supported desktop versions of Excel, macro security options are available through the Trust Center. The exact options and their availability can vary by Excel version and organizational policy.

Recommended practice: Keep a security-conscious setting, such as disabling macros with notification where appropriate, and follow your organization's IT policy. Avoid enabling all macros globally just to run a single workbook.

9. How to Access Macro Security Settings

In supported Windows desktop versions of Excel, you can review the macro security configuration through the Trust Center.

Step-by-Step Instructions

1. Open Microsoft Excel.
2. Click File.
3. Select Options.
4. Open Trust Center.
5. Click Trust Center Settings.
6. Select Macro Settings.
7. Review the available options and follow your organization's security policy.
8. Click OK to confirm any permitted changes.

You may also be able to access the same settings through Developer > Macro Security. In a managed business environment, your administrator may restrict changes to these settings.

10. Understanding Security Warnings When Opening a Workbook

When you open a workbook containing macros, Excel may display a security notification. The notification indicates that the file contains active content that may require a trust decision.

How to Respond to a Security Warning

1. Check the workbook's source. Confirm who created it and why you received it.
2. Verify that the file was expected and that its purpose is clear.
3. If appropriate, inspect the macro code or ask your IT team to review it.
4. Follow your organization's approved process for trusting the workbook.
5. Enable content only if you have established that the file and its macros are trustworthy and your security policy permits it.

Important: A familiar filename or sender name is not enough to establish that a workbook is safe. A trusted account can be compromised, and legitimate-looking files may contain harmful code.

11. Trusted Documents and Trusted Locations

Excel and other Microsoft Office applications provide trust mechanisms that can affect how active content is handled. Two concepts are trusted documents and trusted locations.

Trusted Documents

A trusted document is a file for which a user has approved active content under the applicable Office security process. Depending on the situation, Excel may stop displaying the same security warning when the document is reopened.

Trust a document only when its source and contents are trustworthy. If the workbook changes or is moved, Excel's trust behavior may differ.

Trusted Locations

A trusted location is a designated folder that Office treats as a trusted source for files. Workbooks stored there may receive fewer security checks, and active content may be allowed to run according to the applicable settings and policies.

Guidelines for Trusted Locations

• Use trusted locations sparingly.
• Choose a specific folder rather than trusting a broad location such as an entire drive.
• Restrict write access so unauthorized users cannot place files in the folder.
• Do not use a trusted location as a way to bypass warnings for unknown files.
• Follow IT guidance before creating trusted locations in a business environment.

12. Understanding Digital Signatures for Macros

A digital signature can help users verify the identity of a macro publisher and determine whether signed code has been altered since signing. In Office, VBA macro projects can be digitally signed using an appropriate code-signing certificate.

Benefits of Digital Signatures

• Help identify the publisher associated with a signed macro.
• Help detect changes made to the signed code after signing.
• Support controlled distribution of automation within organizations.
• Allow security policies to distinguish macros from trusted publishers.

Important Limitations

• A signature does not guarantee that a macro is harmless.
• A signature from an unknown publisher should not automatically be trusted.
• Changing signed VBA code can invalidate the signature.
• Organizations should manage certificates and trusted publishers carefully.

13. Macros Downloaded from the Internet

Modern Office security may block macros in files obtained from the internet, including certain email attachments and downloaded workbooks. This can happen even when the file has an .xlsm extension and the user wants to run the macro.

Safe Handling Procedure

1. Verify the source of the file and confirm that it was expected.
2. Do not enable macros if you cannot establish that the workbook is trustworthy.
3. If the file is for work, ask your IT team to review blocked content when required.
4. Use the organization's approved process for trusted publishers or trusted locations where appropriate.
5. Avoid disabling security protections globally to force a downloaded macro to run.

Security behavior can depend on the file's origin, Windows file markings, Office configuration, and organizational policies. Follow Microsoft's current guidance for your version of Office.

14. Protecting Macro-Enabled Workbooks Before Sharing

When sharing an automated workbook with colleagues or clients, consider both the reliability of the automation and the security of the recipient's environment.

• Use a descriptive filename: For example, MonthlySalesReport.xlsm.
• Document the purpose: Explain what the macro does and what input it expects.
• Remove unnecessary sensitive data: Check for confidential information before sharing.
• Test the workbook: Verify that its macros, formulas, buttons, and worksheet references work correctly.
• Use appropriate access controls: Share the workbook only with intended recipients.
• Use digital signatures where appropriate: This can help recipients verify the macro publisher.
• Keep a clean backup: Retain a known-good copy before distributing a modified version.
• Communicate security requirements: Tell recipients how to verify the file's origin and whom to contact if Excel blocks the macros.

15. Common Problems and Troubleshooting

16. Best Practices for Macro Security

• Keep macro security protections enabled according to your organization's policy.
• Use macro-enabled formats only when the workbook genuinely needs VBA.
• Never enable unknown macros just to view or edit worksheet data.
• Review code from unfamiliar sources before execution.
• Use backups before running macros that change or delete data.
• Limit trusted locations to specific, controlled folders.
• Keep permissions restrictive for folders that contain trusted workbooks.
• Use digital signatures and trusted publishers where appropriate.
• Keep Microsoft Office and endpoint security software updated.
• Do not store passwords, secret keys, or confidential credentials in VBA code.
• Document the macro's purpose, required inputs, and expected outputs.
• Ask IT for help when security warnings cannot be resolved through approved procedures.

17. Practical Exercise: Save and Secure a Sales Automation Workbook

Objective: Create a macro-enabled workbook, preserve its VBA code, and review its security considerations.

Exercise Steps

1. Create an Excel worksheet containing sample sales data.
2. Record a macro named FormatSalesReport that formats the headings, number formats, and column widths.
3. Open File > Save As.
4. Save the workbook as SalesAutomation.xlsm.
5. Close and reopen the saved workbook.
6. Confirm that the macro is available through the Macros dialog.
7. Review the workbook's source and the macro code.
8. Inspect the available macro security settings without weakening protections.
9. Test the macro on sample data and confirm the expected formatting.
10. Keep a backup copy of the completed workbook.

Expected Outcome

The workbook should retain its VBA macro in the macro-enabled format. You should also understand how Excel's security settings influence whether the macro can run and why the workbook's source must be verified before enabling active content.

18. Mini Project: Secure Monthly Reporting Template

Develop a reusable monthly reporting workbook that includes a standard worksheet layout and a formatting macro.

Project Requirements

• Create a worksheet with clearly defined report headings.
• Record or write a macro that applies consistent formatting.
• Save the workbook in .xlsm format.
• Add a short explanation of the macro's purpose and expected inputs.
• Test the workbook after closing and reopening it.
• Review the security warning behavior using your organization's approved process.
• Prepare a clean backup before sharing the workbook.
• Document any dependencies, such as worksheet names or external files.

Project Evaluation Checklist

• The file is saved in an appropriate macro-enabled format.
• The VBA code remains available after reopening the workbook.
• The macro works with the intended worksheet structure.
• The workbook has been tested using sample data.
• Security settings have not been weakened unnecessarily.
• The workbook's purpose and use are documented.
• The file is shared only with appropriate recipients.

19. Interview Questions and Answers

Q1. What is a macro-enabled workbook?

A macro-enabled workbook is an Excel file that can store VBA code along with worksheet data and other workbook features.

Q2. What is the extension for an Excel Macro-Enabled Workbook?

The extension is .xlsm.

Q3. What happens when a workbook containing macros is saved as .xlsx?

The VBA project cannot be retained in the standard .xlsx format. Excel may display a warning before saving a macro-free version.

Q4. What is the difference between .xlsx and .xlsm?

The .xlsx format does not preserve embedded VBA macros, whereas .xlsm is designed to preserve VBA macros.

Q5. What is the Excel Trust Center?

The Trust Center provides security and privacy settings, including controls for handling macros and trusted content.

Q6. Why should you avoid enabling all macros?

Allowing all macros can permit potentially harmful code to execute, increasing the risk of unwanted changes or malicious activity.

Q7. What is a trusted location?

A trusted location is a designated folder that Office treats as a trusted source. Active content in files stored there may be allowed to run under the applicable security configuration.

Q8. What is a digital signature in VBA?

A digital signature helps identify the macro publisher and detect whether signed code has changed since it was signed.

Q9. Why might Excel block macros in a downloaded workbook?

Office may block macros in files marked as originating from the internet to protect users from potentially harmful content.

Q10. How should you share a macro-enabled workbook safely?

Verify and document its purpose, test the code, remove unnecessary sensitive data, use appropriate access controls, and follow approved security procedures.

Q11. Can a signed macro still be unsafe?

Yes. A signature helps verify the publisher and code integrity, but it does not guarantee that the macro's behavior is harmless.

Q12. What should you do if your organization's policy blocks a macro?

Contact the IT administrator or follow the organization's approved review process instead of attempting to bypass the restriction.

20. Quick Revision Notes

• .xlsm is the standard Excel Macro-Enabled Workbook format.
• .xlsx does not preserve embedded VBA macros.
• .xlsb can also support VBA macros.
• .xltm is a macro-enabled Excel template format.
• Use Save As to select the correct file type.
• Macro security determines whether and under what conditions VBA code can run.
• Trusted documents and trusted locations affect how Office handles active content.
• Digital signatures can help verify a publisher and code integrity.
• Macros downloaded from the internet may be blocked by default.
• Never enable macros from unknown sources.
• Keep backups and follow organizational security policies.

21. Conclusion

Saving macro-enabled workbooks correctly and managing security are essential skills for anyone using Excel automation. Choosing an appropriate format such as .xlsm helps preserve VBA code, while the Trust Center, trusted documents, trusted locations, and digital signatures provide mechanisms for managing active content. By verifying file sources, testing macros, maintaining backups, and following security policies, you can build more reliable and secure Excel automation workflows.

To develop your Advanced Excel skills further, explore the Advanced Excel Course at JustAcademy, visit the JustAcademy website, or register for a course demo.

22. Further Reading

• Microsoft Support: Save a Macro
• Microsoft Support: Change Macro Security Settings in Excel
• Microsoft Support: Protect Yourself from Macro Viruses
• Microsoft Support: Trusted Documents
• Microsoft Learn: Macros from the Internet Are Blocked by Default in Office