How to Prevent Excel Macros from Disappearing and XLSM Files Corrupting
Question details
Users are experiencing random corruption of macro-enabled Excel workbooks (XLSM) where recorded macros disappear and new macros cannot be saved.
- Product
- Microsoft Excel (Office 2016)
- Device & OS
- not provided
- Scenario
- Using macro-enabled workbooks (XLSM) over several days and attempting to record or save new macros.
- Observed behavior
- XLSM files become corrupted, existing recorded macros disappear from the Macros list, and the affected files reject new macros from being recorded or saved.
Verify whether this issue happens with all macro-enabled workbooks or just a specific XLSM file by testing a newly created document. Ensure you have administrative privileges on your computer, as modifying Add-ins and security settings requires them.
Re-enable the Personal Macro Workbook in Disabled Items
Excel may automatically disable your Personal Macro Workbook (Personal.xlsb) if it suspects corruption, which hides all your saved macros.
Open Microsoft Excel, click on the File tab in the top-left corner, and select Options from the menu.
In the Excel Options dialog box, click on Add-ins located in the left-hand navigation pane.
At the bottom of the window, find the Manage dropdown menu, select Disabled Items, and click the Go button.
If Personal.xls or Personal.xlsb is listed, select it, click Enable, and then restart Excel to restore your macros.
Reset the Personal Macro Workbook
If the Personal Macro Workbook file itself has become corrupted, resetting it will allow Excel to generate a fresh, working copy.
Adjust Antivirus and Firewall Exclusions
Overly aggressive security software might falsely flag XLSM files, secretly quarantining or stripping your VBA macros.
Try WPS Office for Stable Spreadsheet Management
If Microsoft Excel continues to randomly corrupt your files, consider switching to WPS Office. It provides a lightweight, highly compatible alternative for viewing, editing, and managing your complex spreadsheets without stability issues.
- 1. Download WPS Office: Visit the official WPS website to download the free office suite.
- 2. Install the Application: Follow the simple on-screen instructions to install WPS Office on your device.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and seamlessly open your existing Microsoft Excel workbooks to continue your work.
Frequently Asked Questions
Why do my recorded macros keep disappearing in Excel?
This typically occurs when Excel encounters a crash or stability issue and automatically disables the Personal Macro Workbook (Personal.xlsb) as a safety precaution, hiding it in the Disabled Items list.
Can antivirus software corrupt macro-enabled (XLSM) files?
Yes, some strict antivirus or firewall configurations can misidentify VBA code as a threat, resulting in the security software quarantining the macros, blocking the save process, or corrupting the XLSM file structure entirely.
Where is the Excel Personal Macro Workbook stored?
The Personal Macro Workbook (PERSONAL.XLSB) is typically stored in the XLSTART folder, which is located at C:\Users\[Your Username]\AppData\Roaming\Microsoft\Excel\XLSTART.
How do I recover a corrupted XLSM file?
You can attempt a recovery by opening Excel first, going to File > Open, selecting the corrupted XLSM file, clicking the small arrow next to the Open button, and choosing Open and Repair.
