Setting Solver Reference-programmatically

[davidm]

For the umpteenth time, can I please push my request for the syntax to programmatically set SOLVER reference. If it can't be done, I'll be content to settle for that as an answer. Thanks

David

[Jon Peltier]

It can be done. I won't tell you how, because it will bring you no joy. This
advice is the result of hours of trial and error, mostly error, and
fruitless searching of Microsoft and Frontline Systems help.

Later versions of Excel not only have the macro protection but also protect
the VBA project from changes, and this means the ability to set references.
"Enable macros" is one thing, but it's never a good idea to rely on the user
to muck around with VBA protection.

What you need to do is make sure Solver is installed on the computer, then
run Solver's Auto_Open procedure to initialize it, then use Solver. All
calls to Solver should be made using Application.Run, to avoid needing the
reference.

When opening the workbook check that the following function CheckSolver is
true, which checks that the Solver add-in is installed, then initializes the
add-in:

''==========================================================================
Function CheckSolver() As Boolean
'' Adjusted for Application.Run() to avoid Reference problems with Solver
'' © 2004 J. Peltier, Peltier Technical Services.

Dim bSolverInstalled As Boolean

If gbDebug Then Debug.Print Now, "NewCheckSolver"

'' 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,
sTITLE
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
''==========================================================================

The following shows the sequence you'd use to run Solver with
Application.Run:

''==========================================================================

' reset
Application.Run "solver.xla!SolverReset"

' set up new analysis
Application.Run "solver.xla!SolverOk", "Blah1", 1, , "BlahBlah1"

' add constraints
Application.Run "solver.xla!SolverAdd", "Blah2", 3, 0
Application.Run "solver.xla!SolverAdd", "Blah3", 2, "BlahBlah3"

' run the analysis
Result = Application.Run("solver.xla!SolvSolve", True)

' report on success of analysis
If Result <= 3 Then
' Result = 0, Solution found, optimality and constraints satisfied
' Result = 1, Converged, constraints satisfied
' Result = 2, Cannot improve, constraints satisfied
' Result = 3, Stopped at maximum iterations
MsgBox "Solution Found", vbInformation, sTITLE
Else
' Result = 4, Solver did not converge
' Result = 5, No feasible solution
Beep
MsgBox "No solution was found.", vbExclamation, sTITLE
End If

''==========================================================================

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


"davidm" <[email protected]> wrote in
message news:[email protected]...
>
> For the umpteenth time, can I please push my request for the syntax to
> *programmatically *set SOLVER reference. If it can't be done, I'll be
> content to settle for that as an answer. Thanks
>
> David
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
> http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=537657
>

[Tom Ogilvy]

Sub SolverInstall()
'// Dana DeLouis
Dim wb As Workbook

On Error Resume Next
' Set a Reference to the workbook that will hold Solver
Set wb = ActiveWorkbook

With wb.VBProject.References
.Remove .Item("SOLVER")
End With

With AddIns("Solver Add-In")
.Installed = False
.Installed = True
wb.VBProject.References.AddFromFile .FullName
End With
End Sub

--
Regards,
Tom Ogilvy


"davidm" wrote:

>
> For the umpteenth time, can I please push my request for the syntax to
> *programmatically *set SOLVER reference. If it can't be done, I'll be
> content to settle for that as an answer. Thanks
>
> David
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=537657
>
>