"Can't find Project or Library" for standard VBA functions

Question

So I'm having to run someone else's excel app on my PC, and I'm getting "Can't find Project or Library" on standard functions such as date, format, hex, mid, etc.

Some research indicates that if I prefix these functions with "VBA." as in "VBA.Date" then it'll work fine.

Webpages suggest it has to do with my project references on my system, whereas they must be ok on the developer's system. I'm going to be dealing with this for some time from others, and will be distributing these applications to many others, so I need to understand what's wrong with my excel setup that I need to fix, or what needs to be changed in the xls file so that it'll run on a variety of systems. I'd like to avoid making everyone use "VBA." as an explicit reference, but if there's no ideal solution I suppose that's what we'll have to do.

• How do I make "VBA." implicit in my project properties/references/etc?

-Adam

Solution 1

I have seen errors on standard functions if there was a reference to a totally different library missing.

In the VBA editor launch the Compile command from the menu and then check the References dialog to see if there is anything missing and if so try to add these libraries.

In general it seems to be good practice to compile the complete VBA code and then saving the document before distribution.

Solution 2

I had the same problem. This worked for me:

• In VB go to Tools » References
• Uncheck the library "Crystal Analysis Common Controls 1.0". Or any library.
• Just leave these 5 references:
  1. Visual Basic For Applications (This is the library that defines the VBA language.)
  2. Microsoft Excel Object Library (This defines all of the elements of Excel.)
  3. OLE Automation (This specifies the types for linking and embedding documents and for automation of other applications and the "plumbing" of the COM system that Excel uses to communicate with the outside world.)
  4. Microsoft Office (This defines things that are common to all Office programs such as Command Bars and Command Bar controls.)
  5. Microsoft Forms 2.0 This is required if you are using a User Form. This library defines things like the user form and the controls that you can place on a form.
• Then Save.

Solution 3

I have experienced this exact problem and found, on the users machine, one of the libraries I depended on was marked as "MISSING" in the references dialog. In that case it was some office font library that was available in my version of Office 2007, but not on the client desktop.

The error you get is a complete red herring (as pointed out by divo).

Fortunately I wasn't using anything from the library, so I was able to remove it from the XLA references entirely. I guess, an extension of divo' suggested best practice would be for testing to check the XLA on all the target Office versions (not a bad idea in any case).

Solution 4

In my case, I could not even open "References" in the Visual Basic window. I even tried reinstalling Office 365 and that didn't work. Finally, I tried disabling macros in the "Trust Center" settings. When I restarted Excel, I got the warning message that macros were disabled, and when I clicked on "enable" I no longer got the error message.

Later I re-enabled all macros in the "Trust Center" settings, and the error message didn't show up!

Hey, if nothing else works for you, try the above; it worked for me! :)

Update: The issue returned, and this is how I "fixed" it the second time:

I opened my workbook in Excel online (Office 365, in the browser, which doesn't support macros anyway), saved it with a new file name (still using .xlsm file extension), and reopened in the desktop software. It worked.

Solution 5

For those of you who haven't found any of the other answers work for you.

Try this:

Close out of the file, email it to yourself or if you're at work, paste it from the network drive to your desktop, anything to get it to open in "protected mode".

Now open the file

DON'T CLICK ANY ENABLE EDITING OR THE YELLOW RIBBON

Go to the VBA Editor

Go to Debug - - Compile VBA Project, if "Compile VBA Project" is greyed out, then you may need to click the yellow ribbon one time to enable the content, but DO NOT enable macros.

After you click Compile, save, close out of the file. Reopen it, enable everything and it should be OK. This has worked for me 100% of the time.

View more solutions

Comments

[Fionnuala]

I agree. Missing references will be clearly marked MISSING. In your case, I suspect it is a later version of the Excel library, which can be got around with late binding, or selecting the earlier library. I do not believe that compiling the code will help with missing references.

[Adam Davis]

Hmm. I'm getting a request for a VBAproject password. The plot thickens.

[Toby Allen]

Time to find the origional developer I think.

[Oliver Gray]

I just wanted to add that I too had this problem but in a rather unique scenario. Some machines would have all the DLL's (including in this case Acrobat Distiller) and some would not. I still needed the machines without the Acrobat DLL to run macros that did not use it and so resorted to the VBA. namespace addition for these functions. This resolved the issue and worked in both scenarios.

[ELW]

For mine I had to uncheck "MicroSoft MapPoint 19.0 Object Library" which was unused in the macro in question but used elsewhere in the spreadsheet.

[Rich]

Mine was "Microsoft Windows Common Controls 6.0 (SP4)", which was present on my Excel 2010 machine but on a laptop with Excel 2013. In addition, it seems to be in use (I get "Can't remove control or reference; in use" if I uncheck it) so I need to find out what is using it and get rid of that...

[Rich]

[Correction: the above should have said "not on a laptop..."] Turns out I can't remove the project reference on the machine where it works, either. I've now tried to remove it programatically (using .VBProject.References, looping over them, and calling Remove on the bad one to remove it) and I get "Run-time error '-2147319779 (8002801d)': Object library not registered" on the machine without the library, and I get "Run-time error '57101': Can't remove default reference" on the machine that does have the MSComctlLib library present. Grrrr.

[Rick]

I just wanted to point that I have meet the same problem, with none of the references marked as MISSING, I haven't found a resolution for it, but a temporary solution is: open the reference dialog in VBE, don't touch anything just click OK, and the VBA project back to normal.

[Mako212]

Opening the workbook in Excel online is the only solution to this problem that's worked for me so far, and believe me, I've tried everything from safe mode, to re-installing/repairing Office, to restarting my computer and praying. I wish I understood more of the cause and effect here, because it happens seemingly randomly to macro-enabled .xlsm workbooks, although fairly infrequently - anecdotally it seems to happen more often to more complicated files. It seems some element of the VBA library can get corrupted, but the fact that Excel online can fix it, while other methods can't is odd

[flo5783]

Easiest fix I've found so far for this very annoying bug. My guess is that it would come from co-authoring users (the Excel files are on Sharepoint) who all have the same Excel major version, but not the same minor updates (e.g. Version 2101 Build 13628.20000 -vs- Version 2101 Build 13624.20002). Could that be it? I'd very much like to get to the bottom of this problem, which seems to be much more frequent since OfficeUploadCenter got removed by Microsoft in the last months (which now causes many sync errors).