Compile error: Can't find project of libray.

[TBC]

I'm getting a Compile error: Can't find project of libray.
and the "Dim objExcel As Excel.Application" is highlighted for a second the the:
Public Function GetXLStDev(No1 As Double, No2 As Double, No3 As Double) As Double
I tried this, and added it to a new Module, because the other one doesnt let me chose References - I have also attached the database if you woudl like to see that.
I'm using MS access 2003
From Microsoft:
To resolve this issue, on the computer that has MDAC 2.5 installed, click to clear the Microsoft Jet and Replication Objects 2.6 Library check box, and then add a reference to the Microsoft Jet and Replication Objects 2.5 Library. To do so, follow these steps:
Open the Visual Basic Environment (VBE).
On the Tools menu, click References.
At this point, you receive the following error message:
Your Microsoft Access Database or project contains a missing or broken reference to the file 'msjro.dll' version 2.6.
Click OK.
Click to clear the item in the References list that appears as, "MISSING: Microsoft Jet and Replication Objects 2.6 Library."
Click OK.
On the Tools menu, click References.
In the list of available references, click to select the Microsoft Jet and Replication Objects 2.5 Library check box, and then click OK.

Public Function GetXLStDev(No1 As Double, No2 As Double, No3 As Double) As Double
   Dim objExcel As Excel.Application
   Set objExcel = New Excel.Application
   
   Pause 1
   
   Let GetXLStDev = objExcel.StDev(No1, No2, No3)
   
   objExcel.Quit
   Set objExcel = Nothing
End Function
Public Function Pause(PauseSeconds As Double)
Dim Start
Start = Timer
Do While Timer < Start + PauseSeconds
DoEvents
Loop
End Function

Thanks for your help

 

[Rabbie]

Did you follow the advice from Microsoft? What happened then?

 

[TBC]

I followed the steps and now when I try and run it, it just sits there for over 15 mins

 

[Rabbie]

Have you tried single stepping through your code to see where it is hanging? I ran your code on my PC and it worked fine.

 

[TBC]

How do I do "single stepping through your code"?

 

[TBC]

It opened just find at home, but for some reason it’s not work here at work

 

[Rabbie]

How do I do "single stepping through your code"?

Set a breakpoint by clicking on left margin of the line with " Set objExcel = New Excel.Application"
You should see a brown dot appear. Then run your code. It will pause when it gets to the breakpoint. Then Use the F8 key to obey 1 line at a time.

 

[boblarson]

If you had this line show up as the culprit:

Dim objExcel As Excel.Application


That means that either you have no Excel reference checked or you have the wrong one for your version. Try changing these two lines:

   Dim objExcel As Excel.Application
   Set objExcel = New Excel.Application

to this

   Dim objExcel As Object
   Set objExcel = CreateObject("Excel.Application")

And go to TOOLS > REFERENCES in the VBA window and uncheck the Excel reference.

I'm guessing that this was opened by a later version of Excel than you have on another computer and the computer where you got the error has an earlier version installed. So, going back and forth you will get these errors unless you move to late binding (which I showed you how to do). Just remember, using late binding you have to declare your Excel objects as OBJECT (including any workbook or worksheet or range objects) and then you must tie in everything to the application object. See here for an explanation about that.

 

[TBC]

Works great, thanks Bob