Automatically load solver reference library


Greg Snidow

Greetings. I have a macro that runs solver. Every time I switch between my
work computer (Excel 2003) and my personal one (Excel 2007) the solver
reference library needs to be loaded again. When I first open the reference
list, solver is not an option. If I first run solver using the toolbar
menus, it will then appear in the reference list and I can select it, no
problem until I switch computers. How can I get it to load the library
automatically, depending on which version of Excel I am using? Thank you.


Greg Snidow

Well, I was hopeful when I added " Application.AddIns("Solver
Add-in").Installed = False
Application.AddIns("Solver Add-in").Installed = True", because it went
through the whole routine without throwing up an error message. However, it
no longer gives me an answer, so back to the drawing board.

Peter T

I recall suggesting that approach to install the ATP addin, but your problem
has nothing to do with installing addins but adding (and removing) a
reference. Try something like this -

' thisworkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim rf As Object ' Reference
Static bExit As Boolean

On Error GoTo errExit
If bExit Then
bExit = False
Exit Sub
bExit = True
Cancel = True
With ThisWorkbook.VBProject.References
On Error Resume Next
Set rf = .Item("SOLVER")
On Error GoTo errExit
If Not rf Is Nothing Then
.Remove rf
End If
End With



ThisWorkbook.Saved = True

End If

End Sub

Private Sub Workbook_Open()
End Sub

The idea is to add the reference in the workbook's open event, remove it
before saving, then re-add it again to carry on working with it. This is
only lightly tested, let us know how you get on transferring it between 2003
& 2007 systems.

Note - you will need to allow access to VBProject in both systems

Peter T

Peter T

Oops, forgot the install routine which is called from both Open and
BeforeSave events

' normal module or thisworkbook module

Sub AddSolverRef()
Dim sFile As String
Dim ai As AddIn

Set ai = AddIns("Solver Add-in")
sFile = ai.FullName
ThisWorkbook.VBProject.References.AddFromFile sFile

End Sub

Peter T

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question