Macros in unrelated files started crashing around three weeks ago and we cannot figure out why.

[Royzer]

Our company uses Office 365. We are using macros in around 15 unrelated areas of my workplace and suddenly, after years of working perfectly, they seem to be crashing randomly. This started a few weeks ago, and more and more files are having the issue. For example, there is a file used daily with a macro that applies worksheet protection to all sheets at once. Today, clicking the Protect button causes Excel to immediately close, then reopen showing the file as a recovered workbook on the left side of the Excel window, and there is no error message. This macro has worked flawlessly for years.

I am the person who put all these macros into service, so I'm really feeling the pressure to get them working again. I would really appreciate any information about this situation, and hopefully, how to stop it from happening.

Thank you.

*Edited to mention that the files are on a network, but I have been assured by IT that no changes were made to the network prior to this issue arising. Also. returning to the file through Excel's browse window, if you select Open and Repair, it seems to fix the problem. But how could so many different files, being used in different departments, start needing repair at once?

 

[t0ny84]

A few questions:
1) Have you recently changed to Office 365?
2) Are the macros in a single workbook?
3) Is everyone having the same issues?
4) What version of Office 365 do a random selection have? Any difference?
5) Can you supply some of the macro code?

t0ny84

 

[Royzer]

1) We've been on Office 365 for two or three years, I believe.
2) The macros are not in the same workbook. They are in modules loaded into PERSONAL.XLSB of each user who needs to work with the file the module is built for.
3) Not the same issue. Some macros have crashed at the code to save and close the file, some files refuse to save manually, giving a message that they are damaged or corrupt. Generally, you have to go into Task Manager and shut down Excel to get the file closed. However, if you reopen Excel, browse and select the file without opening it, then choose the Open and Repair option the repair will work and the next time the file is used it is normal.
4) I'm on version 2311. The few others I've checked are, too.
5) I've seen the files crash at different points of the same macro, and it is happening in other macros that don't share the same coding. These are all macros that have been dependable for literally years, so it makes me wonder if Microsoft gave Excel a background "enhancement" within the last couple of weeks that is causing these files to suddenly become labeled as damaged or corrupt. If you repair the problem file and rerun the macro on it, it everything works fine.

 

[t0ny84]

Hi Royzer,

It sounds like the actual PERSONAL.XLSB may have become corrupted or been accidentally edited.

Are you able to see when the PERSONAL.XLSB was last modified and does your work have backups they could roll back to?

One way of checking would be to recreate the PERSONAL.XLSB file and then add a macro at a time to see if that fixes it - This forum post lists the Steps

 

[Royzer]

Thanks, I will give that a try.