Excel 2010 crashes on Save(as) / Macro

Written by Ingmar Verheij on February 15th, 2011. Posted in Office

After working multiple days on an Excel worksheet with lots a data, charts en Macro’s the worksheet somehow crashes today. Altough I haven’t found out what iniated the problem (maybe the installation of Adobe Reader X?) I have found a “solution” to the problem.

First of all the symptoms.
After opening an Excel 2010 worksheet, with the Macros enabled (“Enable Content”), the worksheet can’t be saved. Saving the worksheet, or clicking on the Macros button (on the Developer tab), results in a crashing worksheet.

In the event log the followin error is logged:
Faulting application name: EXCEL.EXE, version: 14.0.4756.1000, time stamp: 0x4b9c0870
Faulting module name: VBE7.DLL, version: 7.0.15.90, time stamp: 0x4b7b2131
Exception code: 0xc0000005
Fault offset: 0x0000000000220b24
Faulting process id: 0x186c
Faulting application start time: 0x01cbcce7fe8ef29f
Faulting application path: C:Program FilesMicrosoft OfficeOffice14EXCEL.EXE
Faulting module path: C:PROGRA~1COMMON~1MICROS~1VBAVBA7VBE7.DLL
Report Id: 46c04576-38db-11e0-9560-8697dfeafdc5

Since the error only occurs when Macros (or VBA) is enabled, and the error references VBE7.DLL, I suspected the error to be in the VBA area.

After some research I found out that a bad / corrupt reference is causing the problem.
Although clicking Macros causes Excel to crash, clicking Visual Basic doesn’t. So let’s click it.

Next, open the project references dialog (Tools > References).

In this dialog (References – VBAProject) deselect a few references and acknowledge. You won’t be able to deselect references that are used, Excel will prevent you from this.
I deselected the reference “Microsoft Office 14.0 Object Library” and “OLE Automation”.

Now you can save the worksheet (and use Macros), the problem is solved!
PS: After saving the worksheet you can select the same references you deselected before.

Ingmar Verheij

Ingmar Verheij

At the time Ingmar wrote this article he worked for PepperByte as a Senior Consultant (up to May 2014). His work consisted of designing, migrating and troubleshooting Microsoft and Citrix infrastructures. He was working with technologies like Microsoft RDS, user environment management and (performance) monitoring. Ingmar is User Group leader of the Dutch Citrix User Group (DuCUG). RES Software named Ingmar RES Software Valued Professional in 2014.

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Tags: , , , ,

Comments (10)

  • ebrayden
    21 May 2013 at 00:47 |

    worked perfectly for me!

    • Ingmar Verheij
      22 May 2013 at 16:28 |

      Awesome!

  • Nate
    9 July 2015 at 18:08 |

    Much appreciated! Finally came across this after 4 hours of trial and error headaches. I thought I was in a world of crap. You’re the best Ingmar! I wonder what effects turning these off have now. To be honest, it doesnt matter! My spreadsheet is saved!

  • Jez
    26 November 2015 at 22:37 |

    OMG, thanks! I’ve wasted hours of work today because of this crashing. This fixed it. I might be able to deliver on time now!

  • Jeff Watson
    25 March 2017 at 21:02 |

    6 years later and your post is still valid. My budget spreadsheet started crashing today for no obvious reason. Been using it with Quicken for years. You saved my bacon and my sanity.

  • Marvin
    12 July 2017 at 04:09 |

    Hi – Do you have to do this on every single Excel sheet that’s macro enabled? I have the same exact issue that my user’s reporting but it only happens in several random Excel sheets in his network drive.

    Cheers.

  • Jim
    20 July 2017 at 20:13 |

    Much appreciated! Thanks for sharing this fix!

  • Sunil
    24 July 2017 at 15:58 |

    Worked like a charm.. Thanks for the fix.

  • Asdrubal
    24 August 2017 at 14:20 |

    It worked!!!!! Thanks bro

  • Fabricio21RJ
    1 November 2017 at 18:27 |

    Thank you so much!

Leave a comment

*

Donate

%d bloggers like this: