VBA in Excel can't "find project or library"

Rebuilt XP after major crash, installed new Excel 2007, tried to open Workbook whose macros have worked for years -- got thrown in debug with following message:
"Microsoft Visual Basic
"Compile error:
"Can't find project or library""
VBA command "Left$" was flagged -- tried compile, "Date" function flagged with same error message!

Tried another install with a different NEW Excel, got same result!

Have never seen this before!?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Follow these steps-->
  1. Press Alt+f11 over your workbook.This will open the vb project editor.
  2. Press Ctrl+r over the editor.
  3. Then go to tools-->References.
  4. You will have some library which will be starting with missing in there. remove those libraries and then try to run the code it should work.
  5. If you are using the libraries then you have to install them and this will give idea about the libraries which are missing..
Chris BottomleySoftware Quality Lead EngineerCommented:
Assuming standard components then try running the following script, you can see if there are others to be added in the top comments and add them to the strGUID string:

It probably requires automation enabling:
Office Button | Excel Options | Trust Centre | Trust Centre Settings | Macro Settings | Trust Access to the Visual Basic Project


To Create a macro:

Alt + F11 to open the macro editor

  For User Code:
     Insert | Module to insert a code module into the project
     In the project tree select the module.
     Insert the required macro(s) into the selected module, ('Module1' or similar)

Check Security as appropriate:

In the application select Tools | Macro | Security
2003 and Earlier : Select Medium
2007 : Warnings for all Macros
Select OK

To run a macro:

Alt + F8
Select the macro
Select 'Run'

Sub AddReference()
Dim strGUID As String
Dim arrGUID() As String
Dim varGUID As Variant
Dim intRef As Long
    strGUID = "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}, {000204EF-0000-0000-C000-000000000046}, {00020813-0000-0000-C000-000000000046}, {00020430-0000-0000-C000-000000000046}"
    arrGUID = Split(Replace(strGUID, ", ", ","), ",")
    On Error Resume Next
    'Remove missing references
    For intRef = ThisWorkbook.VBProject.References.Count To 1 Step -1
        With ThisWorkbook.VBProject.References
            If .Item(intRef).isbroken = True Then
                ThisWorkbook.VBProject.References.Remove .Item(intRef)
            End If
        End With
     'Add defined references
    For Each varGUID In arrGUID
        ThisWorkbook.VBProject.References.AddFromGuid _
        GUID:=varGUID, Major:=1, Minor:=0
         'If error then ...
        Select Case Err.Number
        Case 32813
             'Reference already in use.  No action necessary
        Case vbNullString
             'Reference added without issue
        Case Else
            MsgBox "An error was reported when adding one of the references." & vbCrLf & "Check the " _
            & "VBA Project, (References) for the error itself.", vbCritical + vbOKOnly, "Oops"
        End Select
    On Error GoTo 0
End Sub

Open in new window

Chris BottomleySoftware Quality Lead EngineerCommented:
Sorry forgot to include the GUID list:

'Office  C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
'VBA C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL    {000204EF-0000-0000-C000-000000000046}
'Excel   C:\Program Files\Microsoft Office\Office12\EXCEL.EXE    {00020813-0000-0000-C000-000000000046}
'Word    C:\Program Files\Microsoft Office\Office12\MSWORD.OLB   {00020905-0000-0000-C000-000000000046}
'Access  C:\Program Files\Microsoft Office\Office12\MSACC.OLB    {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}
'VBScript_RegExp_55  C:\Windows\system32\vbscript.dll\3  {3F4DACA7-160D-11D2-A8E9-00104B365C9F}
'stdole  C:\Windows\system32\stdole2.tlb {00020430-0000-0000-C000-000000000046}
'VBIDE   C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB {0002E157-0000-0000-C000-000000000046}
'MSForms C:\Windows\system32\FM20.DLL    {0D452EE1-E08F-101A-852E-02608C4D0BB4}
'Scripting   C:\Windows\system32\scrrun.dll  {420B2830-E718-11CF-893D-00A0C9054228}
'MAPI    C:\Program Files\Common Files\system\MSMAPI\2057\\cdo.dll   {3FA7DEA7-6438-101B-ACC1-00AA00423326}
'ADODB   C:\Program Files\Common Files\System\ado\msado15.dll    {B691E011-1797-432E-907A-4D8C69339129}
'DAO C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll   {00025E01-0000-0000-C000-000000000046}
'ACRODISTXLib    C:\Program Files\ADOBE\DISTILLR\ACRODIST.EXE    {317DA881-ECC5-11D1-B976-00600802DB86}
'Powerpoint  C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE11\MSPPT.OLB    (91493440-5A91-11CF-8700-00AA0060263B}

The bit you need to addd is of course in {} withing each datum

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mcsumanAuthor Commented:
saurabh726! There are no references marked "MISSING". How does one find out what libraries are missing?  


Your post is fantastic, but bewildering. I'm going to try it, but I don't even begin to understand what is going on. The subject Workbook is very big, filled entirely by macros I have written over the last 20 years. I have never had a "Can't find project or library" compile failure before, and I can't think of anything that could cause it. As I said, I rebuilt XP, and reloaded a new Excel, and the fail occurred.

The following is some kind of clue:

In the Help for Available References there is a note which says:

'You can't remove the "Visual Basic For Applications" and "Visual Basic objects and procedures" references, because they are necessary for running Visual Basic.'

But "Visual Basic objects and procedures" is not listed among my Available References.

mcsumanAuthor Commented:
No one has answered why I am getting "Can't find project or library" in Excel 2007?

All references in MS KB indicate that this message is related to MS SOAP Type Library 3.0 and MS Office SOAP Type Library 3.0.

All this has to do with Office 2003.

Fixes refer to "MISSING: Microsoft SOAP Type Library v3.0" in the References - VBAProject dialog box.

But I am trying to run a VBA macro in Excel 2007. As I understand, Excel 2007 doesn't use SOAP at all. And I don't have anything marked "MISSING" in the Reference dialog box.

What is going on?

Chris BottomleySoftware Quality Lead EngineerCommented:
Left is one of the standard functions that I imagine is in the VBA reference library.  Saurabh started with the manual process of identifying libraries that are marked missing.  I have identified a means of making sure they exist, replacing as necesaary for missing.  We cannot of course see your PC to address the issues directly so are trying those approaches that have worked for others.

If you have tried the macro route of enabling them and that hasn't worked either then iwe will have to think deeper ... but noting the lack of alternate input currently it may be that our colleagues are also stumped.  If you can advise the results of the macro attempt we can at least draw a line under the references themselves.

As an aid can you advise the list of references in the word app that are installed, (i.e. checked).

mcsumanAuthor Commented:
Chris: I apologize if you feel something I said sounded sharp, but there is an edge in your last response that I feel like dulling.

I certainly appreciate that both Saurabh and you tried to help, and ask that you forgive me for wincing at Saurabh's "Press Alt+f11 over your workbook".

If you know Grice's Principle of Conversational Implicature you will understand why after my mentioning of "macros I have written over the last 20" your observation of "Left is one of the standard functions" sounds out of place.

But I will  apologize for taking offense. [I will plead that as a head of software engineering at a major company before (according to your profile) you were born, my ego got in the way.]

I did try to give you an upbeat compliment on your GUID installation code.

Unfortunately it did not get at the problem because, as I said, the "MISSING" reference was not flagged. My question is still, if anyone knows, just how to identify a missing VBA reference -- I'll guess this one was mssoap30.dll -- when it is NOT flagged in the VBA reference dialog.

You are quite right that "it may be that our colleagues are also stumped."

There is, as I said, extensive discussion of "Can't find project or library" in the Microsoft Knowledge base and among other expert discussion sites, but it is all in reference to code that, as I said, I don't think Excel 2007 has.

[Well known is that back with "Office XP" the VBA project references the MS SOAP Type Library, but MS Office Office used the MS Office Type Library. Maybe VBA 6.5 which I think is in Excel 2007 still does?]

Anyway, I have managed to work around the error on a machine running XP but not on one running Windows 7 (which my client has). The fix is interesting: I cleared out all Windows Office back to 2000, reinstalled windows Office 2000, and then updated to Excel 2007. The compile error still occurred, but now a MISSING reference WAS flagged; the interesting thing is the flagged reference had nothing to do with Excel (it was an old Cakewalk module, not used around here for years). Removing it got rid of the offending compile error.

I don't know why a Cakewalk .dll not used by Excel would be referenced by an Excel VBA module on a machine that never ran Cakewalk. But one Guru I know
says that STUFF gets attached to VBA and if any .dll is referenced that can't
be found it is often misidentified by the VBA Reference dialog.

Thanks for the help, Mike
mcsumanAuthor Commented:
What am I supposed to do here?
Chris BottomleySoftware Quality Lead EngineerCommented:
You still have a malfunctioning installation as I understand it so since it is looking like a non default scenario, first off is there any line of code in the application that is higlighted, for example try compiling the project.

Chris BottomleySoftware Quality Lead EngineerCommented:
As a starter, you mentioned errors on the left function so try prefixing left by vba.left
mcsumanAuthor Commented:
Sorry, I guess I didn't make that clear before. It's not just the Left function, but most, maybe all standard Basic functions, like Date. They don't appear to be referenced in the standard VBA module.

Prefixing VBA to each of them does cause them to be recognized. This is  mentioned in a number of places related to Excel 2003.

I really don't understand why this should be?

Another odd thing I just discovered: as I said, installing Excel 2000 and updating that to Excel 2007 and then deleting the spurious reference to an old Cakewalk module, caused the compile error to go away; but it turns out that the error which was first discovered on a Windows 7 machine no longer occurs with the otherwise unchanged copy from the XP machine.

Apparently just running the workbook with the macros on the XP machine caused some hidden change that stays with the workbook that contains the macros.

Can referenced modules somehow be attached to workbooks which have VBA macros? I'm baffled.
Chris BottomleySoftware Quality Lead EngineerCommented:
The behaviour is typical of missing reference scenarios, leaving aside how it is triggered as a missing ref.  There are as I see it only two 'solutions', prefix where necessary with the library, i.e. vba. or add the reference.  I don't know the reference GUID for cakewalk nor does it really change the problem on a new machine whereas at least the prefix works for all so that is my suggestion in this instance.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcsumanAuthor Commented:
I think that's enough effort for a score. There are underlying open questions in my mind, but apparently we don't know enough about the internals of VBA to put them to rest.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.