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.

L2WD banner ad

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

Share?

Comments

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

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

  3. 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!

    • 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

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

  4. 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!

    • 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

      • 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

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

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

      • 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

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

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

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

  9. 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?

  10. 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…

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

Leave a Reply

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

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x