An issue with "References" in VBA

[robs3131]

Hi all,

I created a file which is going to be used by many people that uses SOLVER
-- I put in code that I obtained from a site (Jon Peltier's site) that
automatically installs SOLVER every time the file is opened (the code is
within a module that is called in the Private Sub Workbook_Open()).

The issue is that when I sent the file to a user and the user sent the file
back to me (after inputting data into the file and using SOLVER), when I open
the file, I get the following error:

"Compile error: Can't find project of library"

Below is the code -- the line of code preceded by ** is the line that is
highlighted when the error comes up (8 lines down). Does anyone have any
idea on why this would occur?

Public Function CheckSolver() As Boolean
'' Adjusted for Application.Run() to avoid Reference problems with Solver
'' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved.
'' Returns True if Solver can be used, False if not.

Dim bSolverInstalled As Boolean

** If gbDebug Then Debug.Print Now, "CheckSolver "
'' Assume true unless otherwise
CheckSolver = True

On Error Resume Next
' check whether Solver is installed
bSolverInstalled = Application.AddIns("Solver Add-In").Installed
Err.Clear

If bSolverInstalled Then
' uninstall temporarily
Application.AddIns("Solver Add-In").Installed = False
' check whether Solver is installed (should be false)
bSolverInstalled = Application.AddIns("Solver Add-In").Installed
End If

If Not bSolverInstalled Then
' (re)install Solver
Application.AddIns("Solver Add-In").Installed = True
' check whether Solver is installed (should be true)
bSolverInstalled = Application.AddIns("Solver Add-In").Installed
End If

If Not bSolverInstalled Then
MsgBox "Solver not found. This workbook will not work.", vbCritical
CheckSolver = False
End If

If CheckSolver Then
' initialize Solver
Application.Run "Solver.xla!Solver.Solver2.Auto_open"
End If

On Error GoTo 0

End Function

Public Function CheckAntoolpak() As Boolean
'' Adjusted for Application.Run() to avoid Reference problems with
Analysis ToolPak
'' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved.
'' Returns True if Analysis ToolPak can be used, False if not.

Dim bantoolpakInstalled As Boolean

If gbDebug Then Debug.Print Now, "Checkantoolpak "
'' Assume true unless otherwise
CheckAntoolpak = True

On Error Resume Next
' check whether Analysis ToolPak is installed
bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed
Err.Clear

If bantoolpakInstalled Then
' uninstall temporarily
Application.AddIns("Analysis ToolPak").Installed = False
' check whether Analysis ToolPak is installed (should be false)
bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed
End If

If Not bantoolpakInstalled Then
' (re)install Analysis ToolPak
Application.AddIns("Analysis ToolPak").Installed = True
' check whether Analysis ToolPak is installed (should be true)
bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed
End If

If Not bantoolpakInstalled Then
MsgBox "Analysis ToolPak not found. This workbook will not work.",
vbCritical
CheckAntoolpak = False
End If

If CheckAntoolpak Then
' initialize Analysis ToolPak
Application.Run "Analysis ToolPak.xla!Analysis ToolPak.Auto_open"
End If

On Error GoTo 0

End Function

 

[Bernie Deitrick]

Robert,

Look for a missing reference, and uncheck it then find one that you do have. This sort of problem
often happens if you are developing your code in, say, Office 2003, and the user is using Office
2000....

HTH,
Bernie
MS Excel MVP

 

[Jon Peltier]

As Bernie said, check for missing references.

You should remove any mention of me from the second bit of posted code. I
only did the work for SOLVER, and I have no idea whether the Analysis
Toolpak misbehaves in the same way that SOLVER does. They were not written
in parallel, nor even by the same teams of people, so using my SOLVER code
on the ATP is not likely to work. In fact, there is a separate VBA version
of the ATP for using the ATP functionality from VBA.

- Jon

 

[robs3131]

Thanks for your reply, though I still have a couple of questions:

1 - I went into References and saw the following missing item checked:

"MISSING: Ref Edit Control"

What I'm not clear on is after I uncheck this, when you say that I need to
then find the one I do have -- I'm not sure what you mean. Do I need to
scroll down the list of all Reference choices and look for "Ref Edit
Control"? By the way, I did do that, but didn't find it.

2 - Is there any way to automate the unchecking of any/all missing
references and then rechecking the correct reference?

 

[robs3131]

Hi Jon,

Sorry - I will remove that comment from the ATP code. Btw - what I did was
take the code you created to add Solver and I adjusted it to apply to ATP.
So far there have been no issues with the ATP add code. Is there any custom
made code that you know of that does add ATP? If so, I would appreciate it
if you could let me know.

Thanks!

 

[Jon Peltier]

I have no experience programming to the ATP. I don't know why the code I
posted for Solver should be relevant. I just want to make sure I don't get
credit for something I didn't do.

- Jon

 

[Jon Peltier]

I've had this issue, with MISSING RefEdit references when moving a file from
2007 to 2003. It shouldn't be a problem, it's never been a problem with
RefEdits in previous versions. I also found that simply unchecking the bad
reference worked. I don't know if the problem returns for the same or other
files.

If this is happening, it's time to clear the temp directory:
C:\Documents and Settings\<username>\Local Settings\Temp

and also delete any *.exd files in this directory:
C:\Documents and Settings\<username>\Application Data\Microsoft\Forms

- Jon