Excel macro malfunction，help!!!

Question

When I save and close an Excel macro in Office 365, and then reopen the macro file, Excel reports that the file is corrupted and needs to be repaired. If I click the repair button, Excel deletes the macro, and it cannot run. What kind of issue is this?

Answer 1

Hello He, Linbei,

Thank you for reaching out to Microsoft Q&A forum

That behavior usually means your workbook structure is getting corrupted, and when Excel runs Repair, it recovers what it can by removing the parts it considers unreadable and unfortunately that can include the VBA project / macro parts, so your macros disappear.

Here is what should do to prevent similar thing from happening:

Use “Open and Repair” with Macros Disabled When opening your file

• try holding down the Shift key to prevent macros from running automatically.
• Use Excel’s Open and Repair feature by going to File > Open, selecting your workbook, clicking the drop‐down arrow next to Open, and choosing Open and Repair.
• This approach can help prevent problematic macro sheets from executing during startup

For more info on repair workbook, you can read it here: Repair a corrupted workbook - Microsoft Support

Because once the workbook is repaired, Excel may permanently remove macro content, so you should make a copy of your workbook as a backup just in case.

You should also export your VBA modules to prevent it getting deleted in the future:

• Open VBA Editor with Alt + F11
• In Project Explorer, right‑click each module > Export
• Save modules locally, then create a new clean workbook and import.

This help separates your macro code from the corrupted container.

Feel free reply back to this post If you need further assistance, I'll be happy to help you.