Forum Discussion
Excel Macros disappear from xlsm file
Hi,
since a few days we are experiencing the following on several computers (we are O365 customers and Excel is always update automatically (Version 2311 build 17029.20068) with Excel:
We are working with quite a lot of xlsm files (running since years without problems). The first issue was that when there was a VBA code for saving a file, suddenly the macro stops at this VBA line (nothing has changed in this file and as mentioned it is working since 5 years without any problem). We are closing this file without saving. Then we are trying to open this very same file again and Excel is crashing and wants to open only in safe mode (i hope this is the English expression for that as I am using Hungarian version).
We are going on with our work and it is quite sure that on this day it will happen, that there will be an other file (used after the above mentioned crash) where our macros will not work anymore.... If I go to developer tab to the macros menu it is empty. If I go Visual Basic in the module folder I can see the names of my former modules, but I can not click on them (when I used to click then the VBA wording of the module appeared). BUT if I change the xlsm extension to zip of the file and I go to the xl directory then I can see the vbaProject.bin file which seems to be fine, so in the very background the macros are there.
This happens total randomly with files. Probably the only link between the cases is that the files, from which the macros disappear, where used after the crash of the Excel (prior story) and after Excel was once opened (but immediately closed) in safe mode.
The whole issue has started last week on one of our colleagues computers, but this weeks these symptoms appear each day on more and more computers in our office.
Somebody has the same experienced lately? If yes is there any explanation for this strange behavior of Excel?
I was assuming (but this is just a pure guess) that when Excel was started in safe mode it can happen that in this mode if an xlsm file is opened and saved probably macros are eliminated from the file...
Thanks for you help and thought is advance
Peter
Hello @ PeterNagy
I understand that English is not your first language and I do appreciate you trying. I wished I could reply in the Hungarian language, but such is our World! Having this out of the way I wanted to ask you:
A) What happens if you restart the computer after each crash? For example, if you are working and suddenly the computer crashes, INSTEAD of going back to work, you take a moment and do a SECOND fresh RESTART and then continue with the work you were doing. (One Restart for the computer crash, and a second another RESTART just to clear the bugs out of the system)
B) If MS-Excel workbooks are corrupted they can behave in unpredictable ways. Try and find a clean copy of the workbook at work from this "clean" copy. If you do not have a better workbook, try and use the "Open and Repair" option with the workbook that will open.C) "when there was a VBA code for saving a file, suddenly the macro stops at this VBA line (nothing has changed in this file and as mentioned it is working since 5 years without any problem). " Can you post the line of code and are you sure the path and folder where the workbook file is being saved is correct?
Let us know how things progress and maybe we all can learn some new things.
G.Hi GeorgieAnne
thanks a lot for you answer. Just today I have sent out an all staff email, that in case of crash of Excel a restart of the computer has to happen immediately.
But anyway Excel is behaving strange in the last days (I am not really following up when there is an update in the background - e.g. I have realized just a few days ago that the appearance of linked excel files pop up window has been changed) and as mentioned we have never experienced such crashes before and it looks like it happens with xlsm files and not with xlsx. But I really hope that this is just a transitory period.
It would be interesting to know if such issues have popped up in case of other users more frequently these days or not
But for sure I will give a feedback in this chat
Cheers
Peter
Hi, thank you for Sharing the Excel Macro problem started in December Excel Version 2311. I have also struggled with it for almost a month. But I think I might have found a solution? well, Microsoft might have solved the problem:
On 5 Jan 2024, I uninstalled and reinstalled Office 365, and made sure Excel is updated to latest version 2312 (released on January 04) - https://learn.microsoft.com/en-us/officeupdates/current-channel
And since then, I haven't had Macro enabled Excel crashed and Macro disappeared.
I schedule update a couple of Macro Enabled Excel files every 30 mins, and since Friday, it still update fine.
Just want to share this with you guys.
Cheers
- Hi,
no, final answer yet. But I have realized that one of our xlsm files (our timesheet file, which is running on each computer) is generating this problem most frequently (this file is started by the task scheduler on hourly basis). In this file we hade a macro which was running automatically when the file has been opened. I have cut out this macro and since then we have experienced this problem less. But there was no science behind this step and it is absolutely possible that there is no link at all and it was just pure luck.
Cheers
Peter- Thank you Peter for your updating and sharing your findings. This problem is really frustrating and really a headahe for me. 6 out of 10 items I run my reports and they crash and I cannot save the files and all my macros are missing. I hope microsoft can produce a quick solution to this. Kind regards, Kebin
- One of my colleagues is also experiencing something similar to this. She's working out of a template spreadsheet that has macros tied to buttons to show/hide columns (for ease of use) and we've recently discovered that the macros tied to the buttons no longer exist. The modules where the macros should be living still do, but the macros themselves are gone. So, when the button is clicked, we get a VBA error and the spreadsheet basically locks up and doesn't allow it to be saved. Pretty frustrating but I get a little comfort knowing that others are having similar odd issues with macros at the moment. We're also running Office 365 with the latest Excel build, so I'm hoping it's just an issue with this particular build version.
- Hi All
I've had this problem twice in the past month. The first time on 12/31, the only way I could fix it is to copy and paste as much as I could into a new file and rewrite the macros, extremely painful. Today after running a Dell update and rebooting I've run into the same problem. Any insight into how to recover will be much appreciated.
Thanks and good luck
Mike- Hi Mike,I have this occurrence several times a week, still having no solutions. Very frustrating. Hopefully Microsoft will find this and solve this soon.
This is the message I get when I try to record a new macro in the file, It will not accept the macro name regardless of the syntax....
- This is because you already have a macro named "Macro1" in your document, It is more likely that it is disabled for use at the moment for use. If you try naming to other names such as Macro_ABCD etc, you should be table to record a new one.
- No still get that message when I give it an unique and valid name
- We started having this problem last month (Dec. 2023). We are running Office 365 Ver 2312 (Build 17126.2032 Click-to-run). A couple of employees that use the same file are on the previous version 2311.
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.dgoeldner Same issue here since 14 december 2023 my excel sheet .xlsm stopped working with ActiveX buttons on my sheet. Insofar I have found no solution how to recover or repair the file. I'm in doubt if it is a Windows 11 issue or an Excel 365 (Beta channel) issue. Also since 14 december my Excel seems less sable than before. Reinstalling did not resolve my problem.
- I did have problems many times since December, I kept good copies and restored all the time.
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?- Hi Jazz since I updated I've not had issues. I'm keeping my fingers crossed and my files backed up though.
- I have been experiencing this problem for some time with increased frequency now almost daily. I have the latest 365 Excel version and it has made no difference. Huge headache for my business
