SOLVED

VBA Not Visible after Microsoft Error and Repair Completed

Brass Contributor

I am using the latest version of Microsoft Office 365 ProPlus where I created a Dashboards of sorts in .xlsb format. Which I use all of the time without problems. This file has worked great up until today. When I open it I receive an error stating "Can't Find Project or Library". I do no know this is referencing a missing Reference file in VBA but when I open the VBA Editor the windows become frozen in the editor and no codes are visible. When I select Tools Reference nothing happens. I have had this happen on a different, unrelated file about a month ago and a quick repair fixed the issue. I just completed the repair and the issue still exist. My next step is online repair. Before I do this, I simply do not understand what went wrong, when nothing changed. Is there a fix for this that someone could let me in on. Everyone here knows how critical it is that when presenting data to clients, that in order for them to have confidence in your data it must be correct and without error. Well it just so happens that when I was presenting this in a meeting today is when the file decided to go haywire. My company is beginning to invest heavily in AI software and once again this does not look good on me or Excel. It just bolsters up AI Software even more (which will eventually replace us data analysts). I know the folks at Excel are working hard so, I am not miffed at them but Excel has got to get better. I have so many examples of this type of stuff happening more often than not. Not just me but my other Data Analysis partners as well. Excel needs to hire some of its old school retired programmers back and sort the messes out and teach the new programmers.

4 Replies

@heylookitsme Further Investigation (opened in Safe Mode) allows me to open and select all of the data in the VBA Editor (which I quickly saved to word). Tools, Reference shows 5 Checked Ref Files: out of those 5 files the Locations of them are accurate and good. No Blank or unusual file references showing. However, when I view the Object Library Window there is a selection for VBAProject which is not listed in my Library Reference and the location of the file is showing the same as this file with this file being its reference. So, this is what is causing the error. Now how to remove a Reference Library that is not showing in your Refence toolbox and how to locate and add the VBAProject Reference so it can be added back into the reference library from the correct location. 

best response confirmed by heylookitsme (Brass Contributor)
Solution

@heylookitsmeSo for anyone with this issue here is the fix: I opened tools and selected VBAProject and then changed the name to something else. This caused it to drop that bad file reference object that was just pointing back to the same workbook. Not sure how it happened.

Hi. I have a pretty similar problem. Found this solution on a support blog and it worked for me.
https://www.mrexcel.com/board/threads/msexcel-2016-vbe-cant-find-project-or-library-error.1044196/#p.... This is what it says:

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.


Thanks for sending this my way. However, I may, or may not have found a solution that is much easier. 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.
1 best response

Accepted Solutions
best response confirmed by heylookitsme (Brass Contributor)
Solution

@heylookitsmeSo for anyone with this issue here is the fix: I opened tools and selected VBAProject and then changed the name to something else. This caused it to drop that bad file reference object that was just pointing back to the same workbook. Not sure how it happened.

View solution in original post