Referencing and loading Solver programatically - how?

[CodeMonkey]

Hi all
I want to load and reference solver in a spreadsheet if its not already
referenced/opened, but I am having some problems.

Here's my code below. I find that SolverInstall runs ok, but if Solver
is not referenced already from VBA or is MISSING, then SolverAutoOpen
fails with the project not referenced. I have to save the workbook
after running SolverInstall and then close/reopen it for solver to be
referenced properly. I am running the SolveIt macro below from a Vb6
application. Can anyone help with a foolproof way to get Solver always
referenced and loaded first time (from vb6)?

Thanks
Andrew

Dim sh1 as worksheet
Sub SolveIt()
SolverInstall
SolverAutoOpen
sh1.Activate
SolverOk SetCell:="$K$63", MaxMinVal:=2, ValueOf:="0",
ByChange:="$K$54:$K$61"
SolverSolve UserFinish:=True
End Sub
Sub SolverInstall()
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
Sub SolverAutoOpen()
If Not SOLVER.AutoOpened Then
SOLVER.Auto_open
End If
End Sub

 

[Tom Ogilvy]

did you try putting the install code in the workbook_open event, rather than
trying to install it in the same routine where you are trying to use it.

 

[CodeMonkey]

Tom,
that's exactly what I tried. I added the SolverInstall Sub to the
Workbook_Open event and it didn't help.

Any more ideas?

Cheers
Andrew

 

[Dana DeLouis]

SolverAutoOpen ' Calls your routine

Hi. Just a guess. Instead of calling you routine here, I'd be curious if
you just skipped it.
I think the AutoOpened flag has a bug left over from who knows how many
versions. I've never seen any code within Solver that calls it.
When Solver opens, it deletes a small internal sheet, and then rebuilds it,
and sets the flag.
( I don't think "If Not SOLVER.AutoOpened then" would ever be executed)
With vb6, I'd be curious to learn if you just set a reference to the correct
workbook, and than reset it again with Solver.Auto_open.

Sub SolveIt()
SolverInstall
SOLVER.Auto_open

As a side note, in a Min problem, value of 0 is ignored.
SolverOk SetCell:="$K$63", MaxMinVal:=2, ByChange:="$K$54:$K$61"

Again, I'd be curious for feedback. :>)

 

[CodeMonkey]

Hi Dana
I just tried your suggestion and no luck
VB doesn't understand what SOLVER is and gives me the error message
"Compile error: Can't find project or library"

I have now found a workaround, which isn't elegant but seems to work. I
open the workbook programmatically from my vb6 app
and then I execute SolverInstall. I then save the workbook and close
it. I then reopen the workbook and execute SolverAutoOpen
and run SolverOK and SolverSolve.

The key here is that I rebuilding the reference to Solver, saving the
workbook and closing it, then reopening it to run solver. I don't know
how else to do guarantee that solver is loaded.
Any input from you and Tom appreciated.

Thanks
Andrew

 

[Dana DeLouis]

open the workbook programmatically from my vb6 app

With AddIns("Solver Add-In")
.Installed = False

Hi. Just a guess. I don't have Vb6, but I wonder if there needs to be some
reference to Excel when doing this.
The Solver code you have probably works on a vba module. From vb6, maybe we
need to set a reference to Excel also.
From vb6, maybe you have something like this...

Dim XLApp As Excel.Application
....etc

Maybe it needs something like this. I don't know. ??
With XLApp .AddIns("Solver Add-In")
...etc

Again, just a guess.

 

[Jon Peltier]

I know there's some disagreement about this, but I have never had success
with referencing Solver the "official" way. Below is what I posted last week
to someone who wanted to programmatically set a reference to Solver:

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 Autpen 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/
_______

 

[CodeMonkey]

Dana
thanks for the response. However the code is actually executing within
the excel workbook itself, rather than the Vb6 application. The vb6
application is just opening the workbook and then telling the macro to
execute.

In fact, if I take the excel workbook itself and forget about the vb6
application and then move it to a machine where the solver.xla is in a
different location thus breaking the link to the reference, I find that
I must run my SolverInstall macro (originally from one of your postings
btw!), then save the workbook, close it and reopen it in order for the
reference to be restored. Just running SolverInstall and then trying to
invoke or even AutoOpen Solver will *not* work (project/library not
found error) until the workbook has been saved and reopened.

For now I am stuck with my work-around, but thanks for replying.

Cheers
Andrew

 

[CodeMonkey]

Thanks Jon. I saw your an earlier post with your code in and the use of
Application.Run was going to be my next port of call. However my
workaround of setting the reference using my SolverInstall routine
(thanks Dana) saving quiting and restarting seems to work at the
moment.

Cheers
Andrew

 

[Dana DeLouis]

Hi. Ok. Just another wild guess... Does your workbook name have any
spaces in it?
There are a few things that won't work with Solver if the workbook name has
a space in it. Also, make sure the workbook is in A1 notation, and not R1C1
notation.
Again...just wild guesses. What version of Excel are you using?

 

[Jon Peltier]

Whatever works, right?

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

 

[Jon Peltier]

Actually, the utility I was working on that led me to Application.Run was a
widely distributed workbook that had to be able to be used by people on
different versions of Excel. If it's only a small group of users on one
version, then twiddling with the reference may be appropriate. Another
constraint is the fact that the VBA Project is protected in recent versions
of Excel and you can't get to it via code (oh sure, trust the users to
change that when they can barely double click on the icon to start the
program). So App.Run was the only feasible option.

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