Excel Keeps Crashing? Check your VBA code - Excelerator BI

Excel Keeps Crashing? Check your VBA code

I use Excel 2013 64 bit and sometimes Excel just keeps crashing on me.  It used to happen to me a lot more in the past, but after I discovered what was causing the repeat behaviour I have been able to greatly reduce how often this occurs.

First a description of the problem

excel has stopped working2If this happens to you then you will already be aware of this scenario.  You are inside your workbook doing something important (it always happens when you are doing something important), and then the screen background greys out, and you get a message saying Microsoft Excel has stopped working.  It then “pretends” to be looking for a solution prior to telling you that it can’t fix it and you have to close the program.

And if your experience is anything like mine, once this starts to happen with a workbook, it keeps on happening over and over with seemingly no way out.

So what causes this repeat behaviour?

Well there are 2 main causes for this problem.

The first (the one that happens to me most) is that some VBA code in one of your workbooks becomes corrupt.  If the workbook has its own VBA code, then this can cause the problem.  However I also have my own personal.xlsb workbook that contains lots of VBA that I use on a regular basis, and this workbook can also become a problem.

The second potential problem can be an Add In.  In fact it is probably an Add In that caused Excel to crash the first time ‘round.  However when Excel crashes from an Add In it can create a new and repeating problem in your VBA workbooks, and then it is the VBA code that becomes the repeat offender.

Power BI DAX Book

OK, tell me how to fix it already

If you have repeated crashing and your workbook has VBA code or you are using a personal.xlsb workbook, then read on.

  1. The first thing to do is to open Excel in safe mode.  Make sure Excel is closed, and then go to STARTRUN and type in
    excel.exe /safe

    then press enter.  Depending on your version of windows, you may have to do something slightly different to get the Windows run dialog box up.  When you start Excel in safe mode, none of the Add Ins will load and also your VBA code will be in protected mode – it can’t do anything.

  2. If the VBA code is in a workbook, then open that workbook now.  If the VBA code is in your personal.xlsb then you can skip this step.  If you have both, then you should complete these steps for both.
  3. The next thing is to take a copy of your VBA code so that you can keep using it.  I normally press Alt-F11 to open the VBA window, browse to my VBA code in my workbook(s).  I generally take a copy of the text in the VBA module(s) and save it in notepad.exe for temporary storage.  You could also try exporting the module as *.bas (right click on the module and select “export file”), particularly if you have forms that you need to keep.  I prefer to use the notepad.exe approach if possible as it guarantees any rogue “bugs” are removed and there is nothing lurking that I can’t see.
  4. The final steps vary for a workbook and a personal.xlsb
    1. Now if you have a suspect vba workbook open, then
      1. Save it as an xlsx file format.  This will remove all the VBA modules from the workbook.
      2. Once it is saved, close the workbook and then reopen it (as recommended by Khushnood in the comments below).
      3. Save it again as either xlsm or xlsb (whatever your preference) and then replace the VBA code from where ever you saved it.
      4. You are finished with safe mode now, so close Excel and reopen it in normal mode
    2. If you have a personal.xlsb, once you have copied your VBA code to a safe place, then
      1. Close Excel.
      2. Navigate to the location of the personal.xlsb workbook and delete it.  See below for file locations.
      3. Reopen Excel in normal mode.
      4. Open a blank workbook and record any random new macro keystrokes.  Set it to record to your Personal Macro Workbook.  This will re-create the personal.xlsb file automatically for you.
      5. The last step is to copy your vba code back into one or more modules in this new personal.xlsb workbook.

Personal.xlsb file location

For Excel 2013, the personal.xlsb can be found in the following locations.

Windows 7 and Windows 8
C:Users%username%AppDataRoamingMicrosoftExcelXLSTART
Windows XP
C:Documents and SettingsApplication DataMicrosoftExcelXLSTARTPERSONAL.XLSB

If you can’t find it, just do a global search for “personal.xlsb”.  Older versions of Excel have the same file name but store the file in a slightly different location.  Just do the windows search and you will find it.

Backup your personal.xlsb

Once I realised what the cause of this problem was, I started to routinely backup my personal.xlsb to my DropBox account.  I use Microsoft Synctoy to do this each night.  The reason I back it up to DropBox is that DropBox keeps a date stamped history of the file.  If you discover in 1 week that your personal.xlsb is corrupt, you can always go back and restore the version you had last week.

And what about those troublesome Add Ins?

Well I hate to say it, but the reality is that the most troublesome Add In I use is Power Pivot.  This is normally what causes my workbooks to crash in the first place.  But like I said above, once this happens the first time, the problem can be moved to your VBA code.  Disabling the Power Pivot Add In is not a long term solution for me (or anyone that uses Power Pivot).  And it is also not a requirement.  Just disable it if you need to and then turn it back on.  I have always been able to recover from a Power Pivot Add In this way and it has never ‘permanently’ stopped working.

But if you are having problems with an Add In in Excel, you can step through a process of disabling them one by one.  This is not in scope for my problem, but you can find out how to do this easily enough by searching the Internet.

35 thoughts on “Excel Keeps Crashing? Check your VBA code”

  1. facing from VBA closing
    following is last commond

    Sub Getdata()
    Call Nifty
    Call BankNifty
    Call MIDCAPNifty
    Call ITNIFITY
    Call CPSENifty
    Call FINNNifty
    This Workbook.RefreshAll
    End Sub
    Sub Ref()
    Call Getdata

  2. I am trying to do the above steps, but my issue is that Excel will crash when I try to open visual basic in my Personal workbook, which is where all of the modules are saved. So I am unable to see them to export them.

    1. Maybe try moving the personal workbook out of the current folder so it doesn’t auto load on start, open Excel in safe mode and the open the personal workbook. I have no idea if it will work, just a thought

  3. I tried a lot of solutions for this, uninstalled Office completely, disabled all Add-ins and opened a file sent from a computer where the file worked, but still had Excel crashing. Followed these steps and it worked perfectly. Thanks!

  4. Really enjoyed reading the article and comments. There is a saying “prevention is better than cure”. This post appears to focus on the “cures”. Anyone got a “prevention” ?

  5. Thank you so much for your helpful blog post. I had an .xlsb macro on 2 different spreadsheets that were very similar (almost identical) to eachother. One worked, the other always caused Excel to crash. I followed your suggestions above, and it seems to have fixed it (for now).
    Question –> I’ve also seen issues with these spreadsheets when trying to do a simple Copy/Paste (values only) from a filtered sheet into another worksheet (so that I’m only copying over the filtered data as values. I get an Out of Memory Error. (There isn’t enough memory to complete this action….. consider using a 64-bit version of Excel). This occurs even though I’m running Office 365 with Excel 64bit version, and somtimes a clean reboot of my machine resolves it, sometimes not (btw, I have 16gb mem). The only way I’ve gotten around this in the past, typically, is to rebuild the offending .xlsb worksheet”.
    Thoughts on the cause/issue here? Buggy VBA code? (FYI, I’m NOT very experienced in writing macros, so most of mine are based off the record feature, then tweaking as needed).

  6. If you happen to have two computers, a quick way out is to save it on something like OneDrive, then open it on the other computer and save and close. When you go back to the original computer and save it in your preferred location, it should be fine. It works for me, anyway. I wish I could figure out how to prevent it altogether. I was glad to see this post, because I thought there was something wrong with the way I was writing my code or something.

  7. Some questions / suggestions for refinement:
    1. I suppose the safe mode is only necessary if the freeze problem prevents us from opening the excel file in standard mode, right?
    2. why not safe the file in .xlsx format (then close and reopen it) and have the corrupted file open at the same time and then copy paste the code directly instead of going via text editor?

  8. Just found this and it was super helpful. Trying to support a convoluted spreadsheet that had 25+ modules, that is 8 years old, developed by someone long gone. Suddenly it was causing Excel to crash, but only on 2 peoples machines (not mine). Exported all the modules, saved as an xlsx, closed it, reopened it, imported all the modules back in. Saved as xlsm. Almost there… when we tried to run an import/download, got a message about adodb connection not defined. Went into Tools>References and added back in the reference to the Microsoft Active X controls… voila! I’m a hero!!! Thank you, thank you, thank you!!!

    1. I’m really glad this helped Steve. Now that you are a hero, you should look Into Power BI, Power Query and Power Pivot. Wait to see what people think of you when you can use hose tools. There is plenty of info on my site.

  9. This was a life-saver. I’m with the people on this thread who are amazed at the abilities of Excel and have been selling it to others but disappointed in Excel’s performance issues. I have just a small amount of VBA in one of my workbooks but it still started crashing repeatedly.

    So far, my two biggest problems that I have experienced can be pin-pointed to anti-virus software interfering with Power Pivot, and VBA somehow becoming corrupt and crashing my workbooks. These two trouble-makers (AV and corrupt VBA) have caused me a LOT of grief and frustration. Like I said, finding this thread was a life-saver.

  10. If u save file after some changes and then reopen file and run macro, excel crashes. U need to put macro to save file before „open userform“ (or some macro) , example:
    Private Sub Test()
    ThisWorkbook.Save
    UserForm.Show
    End Sub

    And on closing workbook also u need tu put macro to save file, example:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Save
    End Sub

    Also I’ve put the delay for application to wait when saving is in proces, like this:
    ‘This is to open my userform
    Private Sub Test()
    ThisWorkbook.Save
    Application.Wait (Now + TimeValue(“00:00:03”))
    UserForm.Show
    End Sub

    ‘This is when I clik on x (close button on userform), it saves workbook and close excel
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Save
    Application.Wait (Now + TimeValue(“00:00:03”))
    Application.Quit
    End Sub

  11. Stephen Snudden

    I repeatedly have this problem with one of my own AddIn’s (Windows 10 with Excel 2007).

    Usually it manifests itself when I compile the VBA code. It gets part way through the compilation and hangs. Looking at Task Manager, Excel has gone power mad and is consuming Memory and CPU.

    Occasionally it will hang when I start to enter new code.

    I used to let Excel try and recover the AddIn but now I keep an empty template AddIn. Every time I make a change to one of the Modules or Class Modules I immediately export the file. Then when it crashes (again!!) I just copy the template AddIn over the top of the old one, drag and drop all the relevant Modules / Class Modules in and off I go again until the next crash.

  12. I don’t know if this applies to your problem but hear this out…
    I’ve had the same problems with a fairly straightforward spreadsheet with vba code…
    Looked into removing all add-ins, especially active-x ones, nope… Tried rewriting the code, spend hours trying to figure out where I missed out, nope.
    I got very frustrated writing code for 10 minutes and then oups, another crash.
    I must have saved about 50 different incremental version of my spreadsheet and it was very hard to keep track of the logic of my code under these conditions…
    It got to a point where I could ‘sense’ Excel was about to crash : Quick save ! Naw, too late. Then it hit me ! Turned off AutoSave and bingo ! Never had that problem again.
    Hope this helps…

  13. I have tried the fix above and it works instantly! However, this has become a recurring issue to me, but ONLY at my PC. My colleagues who are using the same file is not affected at all.

    How to diagnose and identify the cause of my crash?

  14. I’m working with Excel 2016 and windows 10.

    I had a corrupt file that excel claimed it was able to recover (by removing an object from a non-existent sheet17). But upon re-opening the recovered workbook was still corrupted with the same problem. It turned out that one of my other worksheets (not the one the error was referring to) there was a hidden and corrupt button that I had accidentally copied over along with other cell contents.

    To fix the problem I deleted the entire worksheet from the workbook, and upon re-opening the problem was fixed.

    Note: This fix will only be applicable to you if you are working with buttons in your worksheets and if the corrupted file can still be opened to edit.

  15. i m fed up about my MS excel creasing i cannot perform any action about above mention to solve my problem.
    because when ever i am pressing ALT+F11 or pressing the Visual Basic Bottan on Developer Tab, immediately my Excel get Crashed, i am having Excel 2013. with Win 7.
    Excel not giving me any chance to do any thing even in safe mood also, same shit happening again and again.
    i did repair MS Office also and 2 time reinstall the MS Office complete but no any result same thing repeating.
    please give me any solution……
    is it the only solution reaming to re install the full window???????????

  16. I have heard that if you rename your Excel15.xlb file that can be found here %appdata%MicrosoftExcel , that this will also fix the problem. I haven’t tried it yet.
    Close Excel, rename the file, reopen Excel and see if it is fixed. Apparently editing the VBA code forces this file to be updated. Renaming it also forces it to be updated

  17. Anthony Newell

    Hi Matt, I had exactly the same thing happen again yesterday
    I painstakingly went through the above (I have a lot of modules)
    Not long after I had two instances of Excel open.
    In instance A a normal workbook open, In instance B a workbook open with the PowerPivot window open
    I was doing something with measures in the data model then bang ‘Microsoft Excel has stopped working’
    Whenever I’ve had Excel crash in the past as a result of the VBA it hasn’t even let me open the workbook so I’m fast coming to the conclusion that the PowerPivot add-in and data model window is the chief culprit. The VBA is a red herring (but not always)
    I want Microsoft to fix this instability as it’s marring what is great functionality
    Like I say, I can live it with but if users start experiencing it then my case for selling these functionalities is seriously undermined
    I a nutshell: some of the Power BI functionalities are flaky and MS need to sort it!!
    PS feel free to pass this on to Rob Collie etc

    1. There is absolutely no doubt that Power Pivot is flakey and unstable. This happens to me too (as described in the post as the “original cause” of the problem, but it is irregular. However touch wood – I have never had a regular crashing problem with a workbook other than the VBA problem I describe here. Stated another way, every time I have had a regular crashing problem with a workbook (ie it crashes each time I try to touch something behind the scenes, or even worse – it crashes even when I simply open the book), this VBA fix has solved it. Now that is not to say that others don’t have a regular (as opposed to irregular) problem that is specifically Power Pivot related. But in my case, I have not experienced this.

      And Rob is well aware (and just as frustrated as the rest of us).

      1. Anthony Newell

        Thanks Matt
        Is there a chance we can start putting these errors in front of MS and trying to get something done? The PowerQuery team seem supportive so why wouldn’t MS be similarly keen to resolve this, makes no sense to me
        I’ve been talking this software up and am in the process of getting a user to shell out and get a 64 bit laptop to take advantage of these functionalities. I’m going to look a bit of a lemon if they start to complain about it crashing all the time
        I’ve been going guerrilla on this and have finally developed buy-in / traction against a climate where Spotfire is encouraged instead
        Have a good weekend mate

  18. Anthony Newell

    BTW don’t suppose you know if it’s possible to automate screen captures of scenes within tours from PowerMap to Excel worksheet? I’ve dazzled with the graphics but am now saddled with task of manually doing 33 screen captures for the retail regions I’ve set up!

    1. Well I use faststone capture to record screen videos, and I am guessing you could then use Video LAN to extract every 1 second as an image. I seem to remember Video Lan having such a feature – not 100% sure

      1. Anthony Newell

        Cheers, don’t even need a video, just a static screen capture will do
        Once I’ve clicked on Screen capture in Power Map I do a paste at the Excel worksheet end. The picture then can be manipulated as a shape
        Problem is I have to do 30 odd manual screen captures from Power Map
        Ideally, what I’d like to do is manipulate this via VBA but doesn’t appear to be any support to manipulate PowerMap objects as yet

  19. Anthony Newell

    Hi Matt
    I was about to email you about this as I’ve had some real days of despair with this! One ended up with me getting a new PC!!! I’ve questioned my PC and my safe stick as thought it might be corrupting my excel files in some way
    This was the whole reason behind the backup button to create archive copies on a regular basis
    I find this problem more likely when I’m interfacing with data model and/or pivot tables with cube formulae but it’s also happened separate to this
    I’d like to get to the bottom of this with Microsoft as its shaking my confidence a little bit. I can live with it as a developer but if the user of my applications experienced the same then confidence would fly straight out of the window
    We must be kindred spirits as I’d been meaning to contact you about this all week!

    1. Scott from Tiny Lizard also said he has had problems. And like I said, I DID have lots of problems, and always with power pivot like you say, but the problems literally dried up when I cleansed my VBA, especially my personal.xlsb.

      I would be interested If you have tried my suggestion and if it helps

      1. I have quite a few custom number formats saved in my personal.xlsb. Is there a way to export these or would I need to recreate them? I’m running Excel 2013 on windows 7.

  20. Khushnood Viccaji

    I used to face this problem frequently in Excel 2003 and 2007, but very rarely in Excel 2010.
    Surprisingly it seems to have resurfaced in Excel 2013 !
    (I’m using Excel 2010.)

    Also, I had read somewhere that if you have lots of VBA code in your workbooks, it is a good practice to periodically export and remove all code modules, save the Excel workbook (without the code), reopen it, and then re-import the code from the saved files. This is irrespective of whether you’re facing such problems or not.
    And I’ve noticed that it also helps to reduce file-size in a few cases.

    Which brings me to another thing, Matt:
    I think you need to add one more step between 4.1.1 (save as .xlsx), and 4.1.2 (save as .xlsm or .xlsb) — and that is, you should close the .xlsx file and reopen it.

    This step will ensure that all the macros in the original workbook are removed.
    If you don’t close and reopen the .xlsx file, the macros in the original workbook remain as they are (even in the .xlsx file), and the macros are not ‘refreshed’ from scratch, when you save it as a macro enabled workbook.

  21. I found the same thing a month ago. I often just recompile the VBA code and it works, but I suspect that exporting and reimporting the VBA will be more reliable.

      1. Yes, it will let you recompile in safe mode, but you don’t want it to enable macros if it asks when opening.

Leave a Comment

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

Scroll to Top