Forum Discussion
Excel Macros disappear from xlsm file
A spreadsheet that we have been using for 3 years suddenly loses all the VBA code or throws the error "Damage to the file was so extensive that repairs were not possible. Excel attempted to save your formulas and values, but some data may have been lost or corrupted."
Our only solution right now is to back up very frequently and to store all the macros in exported .cls files so that they can be re-imported.
We have had to re-build this file 3 times now. It is very time consuming and painful. Please Microsoft - roll back to a version that works.
However on 5.Jan.24 I updated my Excel to Microsoft® Excel® for Microsoft 365 MSO (Version 2312 Build 16.0.17126.20132) 32-bit, since then my Macro enabled Excel files has been run without problem. I use task schedule and script to auto run macro on excel files 18 times per day, I haven't seen any errors. Maybe my macro is rather simple comparing to yours, mine only refresh Data Connection and save.
Since we know Version 2311 is the problematic one, maybe you should make sure everyone update their Excel to 2312?
JazzTea This is also what I did, I used to have 365 MSO runing in 64 bit version until 14 December 2023 when al my xlsm sheets encountered problems with VBA. What i did is reinstall MSO 365 in the 32 bit version (Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17318.20000) 32-bit) and now all works again.
As my excel sheets daily collect data from the web and store it I can see that from 13/14 December 2023 something went wrong.
Hi, I have made the very same experience. As long as the 32 bit version was installed everything was fine (although I have to admit I installed by chance the 32 version not on purpose). I just tried to reinstall the 64 bit version 2 days ago and right on the 2nd they I have my first break down again.
So Microsoft try to dig deeper in the 64 bit version as something is not OK!!!!
Cheers
Peter
Joost_van_Andel Hi I am glad to know that your Macro enabled Excel runs ok now. I have just noticed that on Microsoft Release notes, they did admit Excel Macro corruption problem. https://learn.microsoft.com/en-us/officeupdates/current-channel?redirectSourcePath=%252farticle%252f95c8d81d-08ba-42c1-914f-bca4603e1426
Version 2401: February 13
Version 2401 (Build 17231.20236)Excel
We fixed an issue where macros were being corrupted when saving Excel files.I hope this would fix Macro problem once and forever.
- how do i do that?
- Easy:
Completely deinstall MSO 365 (64 bit) with uninstall support tool from microsoft: (option 2)
https://support.microsoft.com/en-gb/office/uninstall-office-from-a-pc-9dd49b83-264a-477a-8fcc-2fdf5dbf61d8
Than login to your Microsoft Account and reinstall MSO 365 (32 bit), be sure not to choose the default option but specific select the 32 bit version .
For me that worked excel is collecting data from the web on a daily basis, and VBA is stable incuding my buttons on the sheets.
Current stable version: Microsoft 365 MSO (Version 2402 Build 16.0.17318.20000) 32-bit
- Hi Jazz since I updated I've not had issues. I'm keeping my fingers crossed and my files backed up though.
I found the following on another site and it worked for me but I still want to know why it's happening.
Open the file in protected mode (usually I save it on the share network so it prompts me to "Enable Content"). You must make sure you're in protected mode so no macros/libraries are activated.
DON'T ENABLE CONTENT
Go to Developer, Visual Basic, then click on "Debug" and click on "Compile VBA Project".
Save the file -- > Close - - > Reopen.
MKirsch Thanks for sharing... that worked for me too 🙂
