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

User · Answer

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).

User · Answer

In my case I was checking work done on my office computer  with Visio installed  at home  no Visio   Even though VBA appeared to be getting hung up on simple default functions  the problem was that I had references to the Visio libraries still active

User · Answer

I found references to an AVAYA CMS programme file  Totally random  this was in MS Access  nothing to do with AVAYA  I do have AVAYA on my PC  and others don t  so this explains why it worked on my machine and not others - but not how Access got linked to AVAYA  Anyway - I just unchecked the reference and that seems to have fixed the problem

User · Answer

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

User · Answer

I ve had this error on and off for around two years in a several XLSM files  which is most annoying as when it occurs there is nothing wrong with the file  - I suspect orphaned Excel processes are part of the problem  The most efficient solution I had found has been to use Python with oletools https   github com decalage2 oletools wiki Install and extract the VBA code all the modules and save in a text file  Then I simply rename the file to zip file  backup just in case    open up this zip file and delete the xl vbaProject bin file  Rename back to XLSX and should be good to go  Copy in the saved VBA code  which will need cleaning of line breaks  comments and other stuff  Will also need to add in missing libraries  This has saved me when other methods haven t  YMMV

User · Answer

I ve had this error on and off for around two years in a several XLSM files  which is most annoying as when it occurs there is nothing wrong with the file  - I suspect orphaned Excel processes are part of the problem  The most efficient solution I had found has been to use Python with oletools https   github com decalage2 oletools wiki Install and extract the VBA code all the modules and save in a text file  Then I simply rename the file to zip file  backup just in case    open up this zip file and delete the xl vbaProject bin file  Rename back to XLSX and should be good to go  Copy in the saved VBA code  which will need cleaning of line breaks  comments and other stuff  Will also need to add in missing libraries  This has saved me when other methods haven t  YMMV

User · Answer

I found references to an AVAYA CMS programme file  Totally random  this was in MS Access  nothing to do with AVAYA  I do have AVAYA on my PC  and others don t  so this explains why it worked on my machine and not others - but not how Access got linked to AVAYA  Anyway - I just unchecked the reference and that seems to have fixed the problem

User · Answer

I had the same problem  This worked for me     In VB go to Tools  raquo  References Uncheck the library  Crystal Analysis Common Controls 1 0   Or any library  Just leave these 5 references   Visual Basic For Applications  This is the library that defines the VBA language   Microsoft Excel Object Library  This defines all of the elements of Excel   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   Microsoft Office  This defines things that are common to all Office programs such as Command Bars and Command Bar controls   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

User · Answer

Even when all references are fine the prefix problem causes compile errors  What about creating a find and replace sub for all  built-in VBA functions  in all modules  like this  replace text in code module e g   quot   Date quot  will be replaced with  quot   VBA Date quot   e g   quot  Date  quot  will be replaced with  quot  VBA Date  quot     excluding  quot dim t As Date quot  or  quot mydate quot   All vba functions for find and replace are written here   vba functions list

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In my case I was checking work done on my office computer  with Visio installed  at home  no Visio   Even though VBA appeared to be getting hung up on simple default functions  the problem was that I had references to the Visio libraries still active

User · Answer

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).

User · Answer

Even when all references are fine the prefix problem causes compile errors  What about creating a find and replace sub for all  built-in VBA functions  in all modules  like this  replace text in code module e g   quot   Date quot  will be replaced with  quot   VBA Date quot   e g   quot  Date  quot  will be replaced with  quot  VBA Date  quot     excluding  quot dim t As Date quot  or  quot mydate quot   All vba functions for find and replace are written here   vba functions list

User · Answer

In my case  it was that the function was AMBIGUOUS as it was defined in the VBA library  present in my references   and also in the Microsoft Office Object Library  also present   I removed the Microsoft Office Object Library  and voila  No need to use the VBA  prefix

User · Answer

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

User · Answer

I had the same problem  This worked for me     In VB go to Tools  raquo  References Uncheck the library  Crystal Analysis Common Controls 1 0   Or any library  Just leave these 5 references   Visual Basic For Applications  This is the library that defines the VBA language   Microsoft Excel Object Library  This defines all of the elements of Excel   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   Microsoft Office  This defines things that are common to all Office programs such as Command Bars and Command Bar controls   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

User · Answer

In my case  it was that the function was AMBIGUOUS as it was defined in the VBA library  present in my references   and also in the Microsoft Office Object Library  also present   I removed the Microsoft Office Object Library  and voila  No need to use the VBA  prefix

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

Examples related to excel

Examples related to vba

Examples related to reference

Examples related to namespaces