My macros are disappearing

[Perry320]

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.

[#2]

@Perry320 

The disappearance of macros in Excel can be caused by various factors. Here are some common reasons and solutions:

1. 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.
2. 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."
3. 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.
4. 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.
5. 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.
6. Excel Updates:
• Make sure that your Excel application is up to date. Install the latest updates from Microsoft.
7. Malware or Viruses:
• Perform a scan for malware or viruses on your computer. Some malicious software might interfere with the normal functioning of Excel.
8. 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.

[#3]

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.

[#4]

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.

[#5]

@Perry320 We have been having the same issues with all our users. Restoring the file to a previous known working version is the easiest solution. Today, 2/16/24, we are testing having some users move to Office 2021 LTSC to see if its an issue with Office 365. 

[#6]

I didn't post this on the main thread because I had no absolute answer.  Here is what happened:  I discovered an icon on my screen that I didn't put there.  I checked my downloads and there it was.  I could shoot myself for not making a note of what it was, but I didn't.  I deleted it.  Since I did that not ONE of my macros has disappeared. Not a single problem.  I'm not 100% sure it was that download... but...  awfully big coincidence if it wasn't.  Like I said, since I wasn't sure of anything, and didn't remember the name of the download, so I didn't want to post a response.  @Nevin930 

[#7]

@Nevin930 I have the same thing happen with my macro-heavy workbooks, and it's gotten so bad that I  am constantly saving backup versions to date-coded folders (with descriptions of any changes) cos this problem is arbitrary, chronic, and infuriating.  In some cases, my previously-fine backups (I always check to see if the macros are there with [ALT]-[F8] before closing) are likewise corrupted.  This has been going on for years, and the "just back up every new version" has become the only way to save my work from total Excel annihilation.

[#8]

It's April 2, and I still haven't had one macro disappear.  Since my last post, I have discovered another workaround.  Buttons.  I was able to get rid of 26 macros on one file by using option buttons.  On another file, radio buttons.  But I still have a couple simple ones, and they haven't vanished.  I MIGHT have remembered the icon that mysteriously appeared on my computer:  One Drive.  I'm not sure, but I saw the icon somewhere, and it struck a cord.  Good luck.  I was on the brink of giving up a project I had been working on for a year because of this problem.    

[#9]

@Perry320 mine are data-intensive crop-yield modeling workbooks that are linked to databases for weather data, crop yield data, and satellite vegetation health data... so I am sure I am pushing Excel to the limits.  But I am also the only person in our office this keeps happening to.  It just happened now, and the previous two versions of the workbook I backed up, which worked fine when I backed them up, are likewise f___ng corrupt and now useless.  This sends me to a dark, angry place.  I suspect there is either a DLL file or something within my MS Office or Excel that is corrupt.  Our IT has already wiped my MS office clean and reinstalled it, and that doesn't seem to have stopped the problem.  I am really at my wit's end.  

[#10]

@Perry320 

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/

[#11]

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.

[#12]

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.

[#13]

@bhs67 I feel your pain.  Do the macros not work at all?  Are they there when you hit ALT-F8?  

If they are there and sometimes work, then my advice:  Keep date-coded backup folders, and back them up frequently.  Yeah, a pia, but this has saved me a ton of aggravation and F Bombs.

If they don't work at all, then sorry... no ideas.

Good luck.

[#14]

It's very interesting that when select alt-F8, it displays "Personal.xlsb.Black Black". It is finding Personal.xlsb! When I select "Run", it executes that macro and turns the font color to black!

What on earth is not set correctly?

[#15]

I discovered the solution! File > Options > Quick Access Toolbar > Macros > select the macros that start with Personal.xlsb!

For example, select Personal.xlsb!Red > Add > Modify > select red icon. Now the red icon on the Quick Access Toolbar changes the font to red.

---
I'll repeat --- 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.

[#16]

@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. 

[#17]

@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. 

[#18]

@Douglas Cortez 

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.  

[#19]

@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.

[#20]

Thanks a bunch.