0845 643 64 63

Excel crashes when enabling macros

I’ve had a problem with Excel 2013 crashing a number of times when opening/editing spreadsheets with macros. When I click ‘Enable Content’ to enable macros, the Excel window fades out, goes unresponsive and then crashes with a glorious “Microsoft Excel has stopped working, Windows is checking for a solution to the problem” message. Unfortunately Windows never seems to be able to find the elusive solution that we need.

excelmacrocrash1excelmacrocrash2

I’ve found that the spreadsheets tend to stay working whilst you’re looking at them (if you don’t click ‘Enable Content’), however it crashes when you try and do anything useful such as copy/paste, etc. Sometimes Excel just crashes when opening the file, even without enabling macros. This is seemingly caused by a corrupt compile of the macros. Yes I know it’s VBA code which is interpreted and not compiled, but I’m over-simplifying here.

There a a couple of solutions to this:

1) Before enabling the macros try adding a new blank sheet, and then save the spreadsheet, reload, and sometimes it corrects itself. But not always.

2) Recompile the macro

  • Open the Excel spreadsheet (but don’t enable macros yet)
  • In the ‘Developer’ ribbon, click ‘Visual Basic’

excelmacrocrash3

  • On the ‘Tools’ menu, click ‘Options’
  • Then on the ‘General’ tab, untick ‘Compile On Demand’, then click OK

excelmacrocrash4

  • Then scroll down in the ‘Project’ window, right click on ‘Modules’, then Insert a new module

excelmacrocrash5

 

  • Then just close the Visual Basic window, save the spreadsheet and close Excel.
  • Hey presto, when you then load your spreadsheet you’ll be able to enable macros, and Excel won’t crash.

Thanks to Paulina from Microsoft for helping me find this solution – it’s saved my bacon a few times now!

Frog-Blog-Out

48 Responses to Excel crashes when enabling macros

  • Thank you very, very much!

  • Thanks – a useful tip. Better than others out there to achieve the same end.

  • Thank you so much! Almost had to redo a very large file.

  • Thank you, this worked. Was receiving a visual basic error 400. Now it is fixed!

  • You are amazing! Thank you SO MUCH for this. I’m glad Google ranked you #1 when I searched for how to fix this. You really saved me a lot of time!!

  • Thank you for this solution. It worked for a massive file that I thought that I had to abandon.

  • Thanks, solution 1 worked!

  • Thanks – much appreciated this worked!

  • God.Bless.You

  • YOU ARE A LIFESAVER!!!!! seriously!!! THANK YOU!!!!!!

  • This has saved my large file on many occasions.
    Unfortunately, it is good for about 3 – 4 times of opening the file and then has to be done again. This issue seemed to start when Microsoft pushed out updates; it’s an old file, but has always been stable until updates the past couple of years. Anyone else had this problem or know any ways to correct it permanently? Rebuilding my excel program every year is extremely time consuming and complex. (And yes, we have tried the repairing excel and completely re-loading excel).

    Thanks!!!!

    • Hi Sean
      I’ve also found that the latest Office 365 version of Excel is far more robust and can often fix this problem, so give that a go if I were you.
      Regards
      Alex

  • Solution #1 worked for me, thanks very much.

  • Adding a sheet and resaving worked for me.

  • You are super BRILLIANT!!!!!! I almost started re-building my ultra-large Workbook!!! Thank you!!

  • I am having this problem using Office/ Excel 2011 on a Mac. Any suggestions?
    Thank you!

  • I have an issue where enabling content (enabling macros) causes the workbook to close. We have 3 files all of them have the same behaviour all of a sudden. For months together it has worked just fine. I am guessing it would be some updates that may have caused it. However trying to get to see if we can fix it in Excel somehow. I tried the above steps outlined however this did not resolve the issue. Any suggestions?

  • dear sir i am unable to working the above tips in a macro sheet because when i open any macro work sheet the exel has stoped working before enable macro

  • Hey Alex
    From one day to another I got the problem. I use Excel 2016 and Office 365. I have tried to repair Office, I have cancelled the whole Office package and re-installed it. Did’nt help !

    Now I have tried your solution, but as soon I want to save the file, Excel hangs up and is not responding. So the file is not saved.
    Any suggestions?

    Regards
    Jorgen S.

    • If you can open the file, I’d just try copying/pasting into a new document. If that doesn’t work then you may have to contact Microsoft support to get them to help out.
      Thanks
      Alex

    • I’m having this exact same problem with saving. Did you find a solution to this?

  • I also have the same trouble with the new update of 365, version 1609. Please help!

  • http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10/o365-issue-after-latest-update-with-macro-enabled/90a3d408-ab6b-4890-9fab-2cc0287ad41d

    Here explains why the 1609 is the problem. It can’t be solved with this solution.

  • For anyone having an issue with build 7369.2038, there is currently no solution.
    The current workaround is to rollback to the previous version of Office 365. https://support.microsoft.com/en-us/kb/2770432

  • Thanks Alex!!!

  • Thanks! you saved my life (and my job)

  • Savior! Thank you

  • Thanks a bunch! This was very helpful and easy

  • It worked beautifully!

    Thank you for sharing.

    You just earned 500 life karma points.

  • This has worked very well for me…but can anyone explain why? I have noticed that I can open the same file on several different computers and some excels crash while others do not’, leading me to believe that it is based on some individual settings on each computer.

  • I’m wondering the same. I’ve created several macro-driven tools for use across our company nationally and some folks have crashing issues and others do not. Users have Windows 7, 8 or 10 and have either Office 2010 or 2013, 32 bit. A file will work fine for me and others on our machines but if I email it to some Excel will crash upon them attempting to open it. I can fix it for them by following the suggestions in this article but I don’t want to keep having to do that for them every time there’s and update to the tool. I also have an auto-update system in place where the latest version is stored on a SharePoint site but when a user updates they’ll have the crashing issue again.

  • Thank-you! Adding the blank sheet before enabling macros worked for me. I had already spent a couple of hours with other troubleshooting steps before finding your suggestion and would have spent a few more.

    Dale

  • The cause of my problem was that I had my own function in a lot of cells that checked if a certain file exist. When I suddenly lost my permission to a certain folder where one of the files was it just crasched without entering debug. Maybe it can help someone!

  • In Excel 2010 files were working. In Excel 2016 – files crash. It was due to cells defined as NUMBER. When I open file in Excel 2010, change cell formatting from NUMBER to TEXT, save the file, open it in Excel 2016 – then Excel 2016 is not crashing anymore.

  • Update: it was because in Windows 10 -> Control Panel -> Clock, Language, and Region -> Change date, time or number format tab: Formats -> Format = English (United States) and in Excel 2016 -> Cell property -> Cell data type = Number and when trying to activate: Use 1000 separator (,) . I have changed Windows Format to German (Germany) and was able to activate Use 1000 separator (,) without Excel 2016 crashing.

  • Many thank for this. You’re a star. It seems to work for me.
    Can you please explain what the consequence of unticking the Compile on Demand box is? Will this affect the future behavior of the file? Thanks again.

    • Hi Dan
      It just changes the timing of when and how the code is compiled, it doesn’t change the functionality. By unticking this box the code is compiled all at once, instead of a bit at a time as needed. This should therefore actually have a small positive impact on macro runtime performance.
      Alex

  • MS Access crashes after enabling macros, please help me to solve the problem.

  • Hi,

    I cannot get to the point to even try either of the two solutions above. Once I open the workbook, the error message “Microsoft Excel has stopped working, Windows is checking for a solution to the problem” comes up straight away. Any tips on how best to tackle this?

    Apologies if there is already something in the thread around this.

    Thanks,
    Amy

  • Hello All,

    Need help on one thing, Whenever i open Excel which containing macro an error occurs as “Security Warning Macro have been Disable. Enable Content” . After clicking on Enable content button the excel open in good manner.
    Now I have to disable any cell value or change color to while, let say cell F5 having text “Best of Luck” which is in black color. I record the macro as to change it to white color/it should be disabled. OK.

    My Question is, whenever we click on Enable content button, the text color “Best of Luck” of cell F5 should be change to white/ disappear.

    So How to do, need help.

  • I love you.

  • Another solution that I found is to open the file in Excel 2010 (after copying to a machine with 2010), enable macros there, save it, and copy it back. I’m not sure why this works, but it may be due to the fact that 2010 has a different file structure and by loading it in 2010, saving it, and loading it back in 2013, it corrects the corruption.

    Having a machine with an older copy of Excel has saved me a lot of times before I found these other solutions.

    • Great tip, thanks

      • One thing that I forgot to add is that (as far as I can tell) this works even if you have used new formulas or features in 2013 that do not exist in 2010. I meant to add that for those who might be worried. The newer formulas won’t work in 2010, but it has no trouble loading the worksheet formulas, macros, etc., that contain the new features and saving them back.

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags are not allowed.

343,476 Spambots Blocked by Simple Comments

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon