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

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

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.