Macros Crashing Excel / Cannot Access VBA References

**mvparker79** · 04-06-2017, 04:18 PM

Hello everyone,

I have a critical spreadsheet that has started acting up. It will open just fine, but when you try to save the spreadsheet or click on "Macros" on the Developer tab, excel crashes. I wanted to take a look at the references, but when I do that, one of my modules is causing the VBA screen to act weird. I can click around the VBA screen however none of the module windows will show any code. I can click tools then references, but nothing happens. I can close module windows EXCEPT for module 2. So that window is clearly the culprit.

Does anyone have any suggestions??

**mvparker79** · 04-06-2017, 04:24 PM

Never mind everyone!! I had a stroke of genius. But for anyone who runs in to a similar issue this is how I resolved my issue.

1) I copied my "corrupted" file and pasted it in a new location. Doesn't matter where. This caused Excel to not immediately enable the macros.
2) I opened the file in the new location but did NOT enable macros.
3) Access VBA screen (Alt + F11)
4) Go to Tools -> References
5) Unchecked Microsoft Office 15.0 Object Library and OLE Automation
6) Press OK. Close VBA screen. Save the file.

Done-zo.

**anna_france** · 05-17-2019, 02:59 AM

Great thanks to you to share the solution!
Save my life...

**Fakla** · 08-30-2019, 03:44 AM

Brilliant solution, you're absolutely great, thank you so much. You've saved my neck

))))

Originally Posted by mvparker79

Never mind everyone!! I had a stroke of genius. But for anyone who runs in to a similar issue this is how I resolved my issue.

1) I copied my "corrupted" file and pasted it in a new location. Doesn't matter where. This caused Excel to not immediately enable the macros.
2) I opened the file in the new location but did NOT enable macros.
3) Access VBA screen (Alt + F11)
4) Go to Tools -> References
5) Unchecked Microsoft Office 15.0 Object Library and OLE Automation
6) Press OK. Close VBA screen. Save the file.

Done-zo.

**davros59** · 12-18-2019, 04:11 PM

This is brilliant!

mvparker79 you have saved my rear end. Thank you.

**MarkB-C** · 01-10-2020, 04:49 PM

Thank-you very much! This answer is still relevant as of Jan 2020, although the exact library to turn off is different.

In my case, the VBA editor/debugger would crash randomly regardless of whether i was editing or executing my code.
It got so bad I was saving after every single edit, before I tried to execute the code again.

To implement your fix, I didn't need to disable macros. I just went to Tools->References and unchecked Microsoft Office 16.0 Object Library (OLE Automation was already off). And I have now gone an hour without a crash. Woo hoo!

Here is what my Tools=>Reference now looks like after I unticked Microsoft Office 16 Object Library:
ToolsReference.png

Just posting this so anyone following will know the exact library to disable may be different.
Mark

Originally Posted by mvparker79

Never mind everyone!! I had a stroke of genius. But for anyone who runs in to a similar issue this is how I resolved my issue.

1) I copied my "corrupted" file and pasted it in a new location. Doesn't matter where. This caused Excel to not immediately enable the macros.
2) I opened the file in the new location but did NOT enable macros.
3) Access VBA screen (Alt + F11)
4) Go to Tools -> References
5) Unchecked Microsoft Office 15.0 Object Library and OLE Automation
6) Press OK. Close VBA screen. Save the file.

Done-zo.

**melkihassa** · 03-09-2020, 07:09 PM

Originally Posted by mvparker79

Never mind everyone!! I had a stroke of genius. But for anyone who runs in to a similar issue this is how I resolved my issue.

1) I copied my "corrupted" file and pasted it in a new location. Doesn't matter where. This caused Excel to not immediately enable the macros.
2) I opened the file in the new location but did NOT enable macros.
3) Access VBA screen (Alt + F11)
4) Go to Tools -> References
5) Unchecked Microsoft Office 15.0 Object Library and OLE Automation
6) Press OK. Close VBA screen. Save the file.

Done-zo.

THANK YOU, you've helped me brilliantly

**mrexceleye** · 03-17-2020, 01:58 PM

I registered on this forum just to thank you. This tip is a big time-saver. Thank you!

**tvidal01** · 08-03-2020, 02:07 PM

Originally Posted by mvparker79

Never mind everyone!! I had a stroke of genius. But for anyone who runs in to a similar issue this is how I resolved my issue.

1) I copied my "corrupted" file and pasted it in a new location. Doesn't matter where. This caused Excel to not immediately enable the macros.
2) I opened the file in the new location but did NOT enable macros.
3) Access VBA screen (Alt + F11)
4) Go to Tools -> References
5) Unchecked Microsoft Office 15.0 Object Library and OLE Automation
6) Press OK. Close VBA screen. Save the file.

Done-zo.

A flash of brilliance, mixed with a dash of kindness. Thanks for sharing.

**Chris42683** · 12-18-2020, 04:16 AM

Still working perfectly as of December 2020 as well.

Would however like to know the cause of this though and perhaps a permanent resolve by fixing the Office Library perhaps.

I ask this due to the fact that any future Macro Enabled Macro file on this machine would need to be resolved in this manner too.

But still, thanks for this solution.

**maheshlkore** · 01-08-2021, 07:51 AM

Originally Posted by mvparker79

Never mind everyone!! I had a stroke of genius. But for anyone who runs in to a similar issue this is how I resolved my issue.

1) I copied my "corrupted" file and pasted it in a new location. Doesn't matter where. This caused Excel to not immediately enable the macros.
2) I opened the file in the new location but did NOT enable macros.
3) Access VBA screen (Alt + F11)
4) Go to Tools -> References
5) Unchecked Microsoft Office 15.0 Object Library and OLE Automation
6) Press OK. Close VBA screen. Save the file.

Done-zo.

You just saved me from re-building new file from scratch.
Thank you very much.

**crainaud** · 01-27-2021, 08:19 AM

I can't thank you enough! Was able to restore my file in time for my meeting with CEO. Bless you.

**iamleg** · 02-17-2021, 02:31 PM

I can't say how many times I've had to restore my file to previous versions due to the "Can't find library" error. This solution was genius and fast!

**AZ-XL** · 05-26-2021, 06:46 AM

Thank you very much,

Worked for me too.

**eroock** · 06-11-2021, 07:16 AM

Following quicker approach worked for me:
1. Disable Macros
2. Open buggy file - VBA code should appear now
3. Save file off under another name
4. Enable macros, close Excel and reopen the new file

Macros Crashing Excel / Cannot Access VBA References

LinkBack

Thread Tools

Rate This Thread

Display

Macros Crashing Excel / Cannot Access VBA References

Re: Macros Crashing Excel / Cannot Access VBA References

Re: Macros Crashing Excel / Cannot Access VBA References

Re: Macros Crashing Excel / Cannot Access VBA References

Re: Macros Crashing Excel / Cannot Access VBA References

Re: Macros Crashing Excel / Cannot Access VBA References

Re: Macros Crashing Excel / Cannot Access VBA References

Re: Macros Crashing Excel / Cannot Access VBA References

Re: Macros Crashing Excel / Cannot Access VBA References

Re: Macros Crashing Excel / Cannot Access VBA References

Re: Macros Crashing Excel / Cannot Access VBA References

Re: Macros Crashing Excel / Cannot Access VBA References

Re: Macros Crashing Excel / Cannot Access VBA References

Re: Macros Crashing Excel / Cannot Access VBA References

Re: Macros Crashing Excel / Cannot Access VBA References

Thread Information

Users Browsing this Thread

Similar Threads

Excel Crashing & Operating Very Slow After Inserting VLOOKUPS and MACROS

Excel crashing when trying to view code before macro run; not crashing after first run

Macros crashing Excel 2013

Macros written in Excel 2010 (PC) crashing Excel 2011 (Mac)

Excel 2010 crashing when running macros....my code?

Simple Macros Crashing Excel

Importing multiple sheet from excel to access vb script keeps on crashing

Bookmarks

Bookmarks

Posting Permissions