Forum Discussion
My macros are disappearing
My macros are disappearing. It seems to be random. There are several tricks I can use to get them back, but the problem keeps coming back.
The disappearance of macros in Excel can be caused by various factors. Here are some common reasons and solutions:
- Macro Security Settings:
- Excel has security settings that control the execution of macros. If your security settings are set to a high level, Excel may disable macros.
- Go to the "File" tab, click on "Options," select "Trust Center," and then click on "Trust Center Settings."
- Under "Macro Settings," choose a setting that allows macros. "Enable all macros" is less secure but allows all macros to run.
- Disabled Items:
- Excel might disable macros if it encounters an issue with them. Check for disabled items.
- Go to the "File" tab, click on "Options," select "Add-ins," and choose "Disabled Items" in the Manage box. Click "Go."
- If your macros are listed, select them, and click "Enable."
- Macro Location:
- Ensure that your macros are stored in a location that Excel recognizes as a trusted location.
- Go to the "File" tab, click on "Options," select "Trust Center," and then click on "Trust Center Settings."
- Under "Trusted Locations," add the folder where your macros are stored.
- Antivirus or Security Software:
- Some antivirus or security software might interfere with the execution of macros. Temporarily disable your antivirus software and see if the issue persists.
- Corrupted Excel Profile:
- Your Excel profile may be corrupted. Try creating a new user profile on your computer and see if the issue persists there.
- Excel Updates:
- Make sure that your Excel application is up to date. Install the latest updates from Microsoft.
- Malware or Viruses:
- Perform a scan for malware or viruses on your computer. Some malicious software might interfere with the normal functioning of Excel.
- Save Macros in Workbook:
- Instead of saving macros in a personal macro workbook (PERSONAL.XLSB), try saving them in the workbook where you are using them.
If the issue continues to occur, you may need to provide more details about your Excel version, operating system, and any specific error messages you encounter. Additionally, checking the Excel event logs might reveal more information about the disappearance of macros. The text was revised with the AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
- Thanks a bunch for the responses. I think the answer lies in a combination of your suggestions. I am going to uninstall and re-install Office 365. And cross my fingers. The reason I don't think many of your solutions don't apply is because the macro vanishing is so random. I have one file that has never crashed, and others that crash maybe every third time I open them. I'm new here, so I don't know if this answer will show on the thread. If it does, I'll post the workarounds I've discovered. I have always been able to get them to show up, but that's not a real solution. Thanks again.
- Ok... here are some workarounds. Not solutions. But one of them has always gotten my macros back. The easiest: IF you can get into a VBA module, simply hit the RUN button ON THE VBA ribbon. This runs one of the macros and seems to reset the rest. The problem is, that usually, you can't get into a module. Another: If Excel hangs up when you are trying to get into the macro screen, click the red X button on the top right of the screen as quickly as you can. Then when it appears, hit the RESTART button. Another: make a Word document with your macros. Again, IF you can get into the VBA modules, you can paste the Word copy into it and that will work. Another: This one takes a while. Copy the problem file on an exterior hard drive. Open it on a different computer. When it opens, click YES on the Enable Functions button. Save the file on the exterior drive, and save back to the original computer. That one has always worked, but it is a pain.
I think you might be on to something with One Drive. I think One Drive is trying to push itself to be used too much, and when saving, it glitches and doesn't save completely or is still processing or something and becomes read only, which clears the macros.
There's some scuttlebutt on here about EXD files being the problem, but I wonder if it's just coincidence:
http://www.lessanvaezi.com/delete-exd-files-to-fix-object-library-invalid-error/
- Thanks for that. Every time I think about it, I kick myself for not writing down the name of the files I deleted. But I wasn't thinking about macros when I did it.... so I never made the connection.
- My Excel macros stopped working after I installed Win11 and re-installed Office 365.
I reinstalled the macros --- Word and PowerPoint macros work fine --- Excel macros do not.
Initially, the Excel macros worked, until I closed the xlsm. Now the macros no longer work. Selecting a macro icon results in the message, "Office has identified a potential security concern ... Enable ... Disable." ... then "Sorry, we couldn't find ..\Book1.xlsx ...." which is odd as I have no idea where it gets that xlsx name.
I have followed the items listed above, including creating a Personal.xlsb and placed it at C:\Users\xxx\AppData\Roaming\Microsoft\Excel\XLSTART.
Any other suggestions?
---
BTW It would be good if the Microsoft Excel code writers removed all of these restrictions, located in many setting locations. It would be best if they made adding Excel macros as easy to install as Word and PowerPoint macros. This problem with Excel macros has been around for a couple of decades.
Perry320 I had this exact issue and have been looking everywhere to try and solve as none of the provided solutions helped me. BUT what did help me was removing any sorting applied to the data. Don't ask me why, but as soon as I removed any 'sorting' in the data the macros reappeared and worked again.
Perry320 Here is what worked for me, My excel files would not show my macros. Closed excel, restarted and opened by Personal.xlsm file. It showed my old macros under view macros.
I added some to my quickview ribbon. Left personal.xlsm open.
Opened other excel files and they now show my macros and the ones added to quick view.
No idea why this worked. But it did.
I haven't had a reoccurrence since I zapped the program I mentioned above. But yours is a simple solution I will definitely keep in mind. Thanks.
Perry320 Douglas Cortez thank you for the responses. I figured it out a few months ago.
I have an Export macro that exports several charts as PNGs as well as a data sheet as a CSV. It turns out this macro (I think it's the CSV portion) was -- on occasion -- corrupting my files. My solution: Save the XLSM, run the Export macro, then close without saving. This has worked like a charm!
If I had to guess, it's the Export CSV portion of the code that ultimately corrupts the file. But I need both the PNGs and the CSV to support other work, so I have learned to not save the file after running the macro.
FWIW.
