How to Fix Excel Macros Disappearing After Saving a Workbook
Question details
The user's Excel macros are missing or have disappeared after saving and reopening a workbook.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Saving and subsequently reopening an Excel workbook containing VBA macros.
- Observed behavior
- The VBA macros previously written or recorded in the workbook are no longer visible or accessible, potentially due to wrong file format, corruption, or security settings.
Before attempting to recover your macros, create a duplicate copy of the affected workbook to ensure you do not accidentally overwrite any remaining recoverable VBA code during troubleshooting.
Verify the Workbook is Saved in a Macro-Enabled Format
Macros cannot be saved in the default .xlsx format. You must ensure the file was saved as an .xlsm or .xlsb.
The most common reason macros disappear is that the workbook was inadvertently saved as a standard Excel Document (.xlsx). This file type automatically strips all VBA code to ensure security.
Locate your workbook in Windows File Explorer or Mac Finder. Check if the file extension is .xlsm (Excel Macro-Enabled Workbook) or .xlsb (Excel Binary Workbook).
If you are actively editing and still have the macros loaded in your current session, go to 'File' > 'Save As'. In the file format dropdown, specifically select 'Excel Macro-Enabled Workbook (*.xlsm)'.
Modify Trust Center Settings and Disable Add-ins
Security settings or conflicting add-ins might block macros from loading, making them appear missing.
Recover an Earlier Version or Backup
If the file was saved as .xlsx and closed, the VBA code is lost. You must restore from a backup or version history.
Try WPS Office for Seamless Spreadsheet Management
Tired of macro errors, missing features, and file corruption in Microsoft Excel? WPS Office offers a free, lightweight, and highly compatible alternative. Enjoy seamless support for standard spreadsheet formats, a familiar user interface, and an efficient environment for all your data processing needs.
- 1. Download WPS Office: Visit the official WPS website and download the free installer for your operating system.
- 2. Install the Software: Run the installer and follow the simple on-screen instructions to set up the lightweight office suite.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel files directly with perfect format compatibility.
Frequently Asked Questions
Why do my Excel macros keep disappearing when I save?
This usually happens if you save your workbook in the standard .xlsx format. The .xlsx format cannot store VBA code by design. You must always choose .xlsm or .xlsb from the 'Save As' dropdown when saving a workbook containing macros.
Can I recover macros if I accidentally saved the file as .xlsx and closed it?
Once an .xlsx file is saved and the Excel application is closed, the macros are permanently removed from that file. Your only option is to recover a previous version via OneDrive Version History, Windows File History, or Excel's AutoRecover feature.
Why is my VBA project completely empty even though it is saved as an .xlsm file?
This can occur due to file corruption, aggressive antivirus software deleting the macro scripts, or third-party add-ins blocking the VBA project from loading. Try opening the workbook in Excel Safe Mode (hold the Ctrl key while launching Excel) to see if the VBA project loads.
