MSEXCEL 2016 VBE : Can't find project or Library error

[ambly]

At my Workplace which is a Home Office, running PC:
Windows 10 Pro
MS Office 2016, released via Office 365

Problem with: A specific MS Excel File (xlsm), that has been working perfectly fine since 2013. MS Office 365, has been on the machine for past 2 years.

The Wb (lets say MyWB) is behaving as a database. Within the Project it has Forms, Modules, code that controls how it opens, closes, activates, presents and so on. It has 33300 records and counting. The file size is about 6.5MB. Aside from the recurring issue below, it operates smoothly.

Issue: Since the start of this year, MyWB has 'crashed' 4 times - in as much as it wont load the Project (msg: "Can't find project or library".

If I persist, the dialog : 'Visual Studio Just-in-time Debugger' pops up, stating "an unhandled win32 exception occurred in EXCEL.EXE (2960)" with The Possible Debuggers: New Instance of Mirosoft Office Studio 2010"

I have many other xlsm workbooks, which open fine. It's just this one.

Twice I have 'rebuilt' the broken File - by exporting each 'form''module' 'this workbook' out of a back up copy of MyWB- then creating a new workbook, moving the up-to-date worksheets from MyWB, then importing the project items ( 'form''module' 'this workbook') back in.

Then off it goes, fine until the next time it happens.

The last time it happened, I began to wonder if it was some update that is causing the problems. Or if the something that out ESET security app was doing. So rather than 'rebuild' I decided I would take the misbehaving MyWB home to my own PC (I emailed it to myself). At home, where I also run Office 365, it opened perfectly fine. So I saved it on myPC, then emailed it back to work. When I opened it at work, saved it back onto my Work PC, it opened fine, no problems - this was maybe 2 weeks ago - and it's been okay until today.

I see by google searching the "Can't find Project or Library" error, that a lot of the solutions seem to involve opening up the file, going into VBE, > Tools> References Dialog where I look for and uncheck specific "MISSING" items.

However, when I try do this, I cannot even get the Reference Dialog to open up at all. It's not greyed out, but it will not fire up. Same if I open the file in SAFE mode.

Help! Sorry for long msg, but I'm trying to provide as much info as I think you might need to help me.

Thank you for all help offered.

Cheers
Annie

 

[GirishDhruva]

Hi Mike, I did find myself a solution - though the issue itself is on going! I am only on my phone at mo - i need to get to my files.

Excellent, it worked for me.

Regards,
Dhruva.

 

[ffffloyd]

I just want to add that this also worked for me after a very frustrating two days during which four spreadsheets I had been working on all suffered from the same problem.

Two of them, I laboriously reconstructed but two of them remained broken until I found this fix.

After the fix worked, we worked out that the problem had been caused by an unstable internet connection. What I thought was being saved to my local Documents folder was, in fact, being redirected to an office server. For some reason, during the save process, part of the file was not being saved. I infer that it was part of the compiled VBA code. Applying the method described here fixes that compiled portion, which I am then able to save back. The size of the fixed file is subtly different from the size of the corrupted file.

Thank you to Ambly for posting such a comprehensive, step-by-step solution.

 

[johncox3]

I am not familiar with the recompile line. If I type exactly as shown:
‘ Recompile 270310 error.

(with and without the period after error, it shows as a red "errored" line. It returns "Expected: end of statement" if I try to run the Compiler.
If I remove everything after the word recompile it says "Invalid outside procedure".
So I am not sure how to get it to recompile. Also, one more question, once I figure out the recompile process, Do I comment out the ‘ Recompile 270310 error line from the code.

One more question, would the recompile line go above or under any global declarations at the top of the module? Or does it matter? I tried both.

I sure do appreciate the help. I feel oh-so close to "solving", at least temporarily each time.

I had this problem, and just opening a version I sent myself without macros enabled allowed me to save it back in the original folder in a new name. Came out, went back in. Fixed.

 

[johncox3]

I had this problem, and just opening a version I sent myself without macros enabled allowed me to save it back in the original folder in a new name. Came out, went back in. Fixed.

Macros disabled rather

 

[MikeDBMan]

The whole key to fixing it is not to type anything new in the VBA module but just to load the file without macros enabled. Then go to the macro editor and recompile. Then save and then when you reopen it, enable macros and all is well. Usually this happens to a file I have as a trusted file. So I rename it so that macros don't auto-enable. Then recompile, save, unload, rename and then reopen and all is well. Or if the file is in a trusted path, I move it to a path I've named "C:\untrusted\" where macros are not to be enabled. Then open, recompile, save and then copy back to original location and all is well.

 

[andrezmor]

Hi Mike, I did find myself a solution - though the issue itself is on going! I am only on my phone at mo - i need to get to my files. Essentially, i believe in my case key dll files - a particular one possibly - are not being stored or saved in the right place or move, depending on certain configurations of open xlsm wbs. I think versions of Excel being installed over or beside each other is the main culprit. I also firmly feel excel is nowhere near as robust as it used to be pre 365. Eg: The “ excel is not responding” banner message while long or complicated code is running, infuriates me!! But I digress.....

My fix:1) . Close all open wbs. From file explorer, Copy the misbehaving wb to another location. In my case the home of the file is on a Network drive G:/ (and is a “trusted document” ie: I never need to enable macros). I then copy the file to a folder in my C:/. This causes excel to NOT trust the document in C:/

2) open the misbehaving wb in C:/. Do NOT enable macos...and immediately do as follows:

3) Via Developers tab, go into VB. Expand the Modules attached to your project . In each module in turn, go into the module. At the top, type a note preceded by the apostrophe - I write:
‘ Recompile 270310 error.

The date being the date today and error being the error msg (i have a few).

4) on the debug tab, select and click the compile option.

I do this on each module then save, then exit vb. Then close wb (save if prompted )

5) copy the wb in c:/ onto clipboard.

6) Navigate back to G:/ to where the original broken file is. Rename the broken file as eg: RC270319_mywb. Then paste in the fixed file. ItÂ’s original file name is still intact.

7) now open that fixed file; earn its trust again by enabling macros if asked. And it works again, until next time.

8) the broken file and the file you moved to c:/, can be deleted - tho I keep them in a folder just to build up a picture of when this occurs. I also never delete or over write the
‘Recompile message in the modules, for same reason.

Maybe this works for you! Let me know how you go.

Thank u very much. It worked for me.

 

[speed88bump]

Hi Mike, I did find myself a solution - though the issue itself is on going! I am only on my phone at mo - i need to get to my files. Essentially, i believe in my case key dll files - a particular one possibly - are not being stored or saved in the right place or move, depending on certain configurations of open xlsm wbs. I think versions of Excel being installed over or beside each other is the main culprit. I also firmly feel excel is nowhere near as robust as it used to be pre 365. Eg: The “ excel is not responding” banner message while long or complicated code is running, infuriates me!! But I digress.....

My fix:1) . Close all open wbs. From file explorer, Copy the misbehaving wb to another location. In my case the home of the file is on a Network drive G:/ (and is a “trusted document” ie: I never need to enable macros). I then copy the file to a folder in my C:/. This causes excel to NOT trust the document in C:/

2) open the misbehaving wb in C:/. Do NOT enable macos...and immediately do as follows:

3) Via Developers tab, go into VB. Expand the Modules attached to your project . In each module in turn, go into the module. At the top, type a note preceded by the apostrophe - I write:
‘ Recompile 270310 error.

The date being the date today and error being the error msg (i have a few).

4) on the debug tab, select and click the compile option.

I do this on each module then save, then exit vb. Then close wb (save if prompted )

5) copy the wb in c:/ onto clipboard.

6) Navigate back to G:/ to where the original broken file is. Rename the broken file as eg: RC270319_mywb. Then paste in the fixed file. ItÂ’s original file name is still intact.

7) now open that fixed file; earn its trust again by enabling macros if asked. And it works again, until next time.

8) the broken file and the file you moved to c:/, can be deleted - tho I keep them in a folder just to build up a picture of when this occurs. I also never delete or over write the
‘Recompile message in the modules, for same reason.

Maybe this works for you! Let me know how you go.

Great work identifying this work-around solution.

All that is ticked is:
* Visual Basic for Applications
* Microsoft Office 16.0 Object Library
* OLE Automation
* Microsoft Office 16.0 Object Library (again)

(Is that the right amount of ticked files? Why would the MS Office 16 Object Library be there twice?)

I have the same question as Ambly. The first MO 16.0 Object Library references EXCEL.exe and the second MO 16.0 Object Library references MSO.dll file. I can't uncheck any of them. Not 100% sure (so no one try this) but I think removing the one that references EXCEL.exe would be the one that needs to be deleted. Is there a "back door" way of deleting references? Maybe via Registry Files?

 

[speed88bump]

Hi Mike, I did find myself a solution - though the issue itself is on going! I am only on my phone at mo - i need to get to my files. Essentially, i believe in my case key dll files - a particular one possibly - are not being stored or saved in the right place or move, depending on certain configurations of open xlsm wbs. I think versions of Excel being installed over or beside each other is the main culprit. I also firmly feel excel is nowhere near as robust as it used to be pre 365. Eg: The “ excel is not responding” banner message while long or complicated code is running, infuriates me!! But I digress.....

My fix:1) . Close all open wbs. From file explorer, Copy the misbehaving wb to another location. In my case the home of the file is on a Network drive G:/ (and is a “trusted document” ie: I never need to enable macros). I then copy the file to a folder in my C:/. This causes excel to NOT trust the document in C:/

2) open the misbehaving wb in C:/. Do NOT enable macos...and immediately do as follows:

3) Via Developers tab, go into VB. Expand the Modules attached to your project . In each module in turn, go into the module. At the top, type a note preceded by the apostrophe - I write:
‘ Recompile 270310 error.

The date being the date today and error being the error msg (i have a few).

4) on the debug tab, select and click the compile option.

I do this on each module then save, then exit vb. Then close wb (save if prompted )

5) copy the wb in c:/ onto clipboard.

6) Navigate back to G:/ to where the original broken file is. Rename the broken file as eg: RC270319_mywb. Then paste in the fixed file. ItÂ’s original file name is still intact.

7) now open that fixed file; earn its trust again by enabling macros if asked. And it works again, until next time.

8) the broken file and the file you moved to c:/, can be deleted - tho I keep them in a folder just to build up a picture of when this occurs. I also never delete or over write the
‘Recompile message in the modules, for same reason.

Maybe this works for you! Let me know how you go.

Found another possible solution. It worked for me and may for anyone else. For me it 100% worked and got my Excel working without errors this morning. I went in and cut my PERSONAL.xlsb (Personal Macro File) in C:\Users\Username\AppData\Roaming\Microsoft\Excel\XLSTART I then pasted it to my documents for the time being. Opened a new Excel workbook (My error of can't find library or object is missing went away). Went to Developer tab and recorded a new macro (which created a new personal account). I renamed the one I saved in my documents to something other than PERSONAL.xlsb. Opened it up and copied the code into my new PERSONAL.xlsb module and then removed the old one again. Works like new.

 

[dof]

Hi Mike, I did find myself a solution - though the issue itself is on going! I am only on my phone at mo - i need to get to my files. Essentially, i believe in my case key dll files - a particular one possibly - are not being stored or saved in the right place or move, depending on certain configurations of open xlsm wbs. I think versions of Excel being installed over or beside each other is the main culprit. I also firmly feel excel is nowhere near as robust as it used to be pre 365. Eg: The “ excel is not responding” banner message while long or complicated code is running, infuriates me!! But I digress.....

....

Maybe this works for you! Let me know how you go.

Annie, you're a hero! I hope you're not vegetarian cos you just saved my bacon.