?
Solved

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

Posted on 2010-04-09
13
Medium Priority
?
6,217 Views
Last Modified: 2013-11-25
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!?
0
Comment
Question by:mcsuman
  • 6
  • 6
13 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 30225183
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..
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 30286829
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

Chris

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
    Next
    Err.Clear
     '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
    Next
    On Error GoTo 0
End Sub

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 30286917
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

Chris
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:mcsuman
ID: 30306170
saurabh726! There are no references marked "MISSING". How does one find out what libraries are missing?  

Chris!

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.

0
 

Author Comment

by:mcsuman
ID: 30397582
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?

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 30400659
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).

Chris
0
 

Author Comment

by:mcsuman
ID: 30458652
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
0
 

Author Comment

by:mcsuman
ID: 30458979
What am I supposed to do here?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 30481095
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
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 30481247
As a starter, you mentioned errors on the left function so try prefixing left by vba.left
0
 

Author Comment

by:mcsuman
ID: 30547943
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.
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 30581716
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.

Chris
0
 

Author Closing Comment

by:mcsuman
ID: 31712849
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.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question