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

37 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!!!!!!

    • Happy to help 🙂

      • Hi Alex,
        Just tried this with no luck 🙁
        I already have a Module with a Macro (Refreshing a Data Connection and a few other actions), by adding another Module doesn’t seem to help. Any ideas?

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

Leave a Reply

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

HTML tags are not allowed.

341,978 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