How to call SolverSolve portably for Excel XP and Excel 2000

[Chris Russell]

My VBA macro calls Solver.Solve, which requires the code
to reference solver.xla. Unfortunately, the location of
solver.xla is different for Office 2000 (C:\Program
Files\Microsoft Office\Office\Library\Solver) than Office
XP (C:\Program Files\Microsoft Office\Office10
\Library\Solver).

Thus, if I set the reference (using the Visual Studio 6
IDE menuitem "Tools" > "References...") for Office XP,
then the macro fails on Office 2000, and visa versa.

How can I create a single version of my macro that
automatically references the proper location of
solver.xla so it can call SolverSolve?

I have tried the following approaches so far (without
success)...

1. Application.VBE.ActiveVBProject.References.AddFromFile
Application.LibraryPath & "\Solver\Solver.xla"

This approach ALMOST works, but it requires the user to
lower their security settings for macros (allowing self-
referential code), which is a major downside in today's
world of macro viruses. Also, it sometimes fails with a
namespace conflict, even though solver.xla is not active.

2. status = Application.Run("SolverSolve", True)
instead of:
status = SolverSolve(UserFinish:=True)

Using Application.Run() doesn't solve the problem... the
call still fails unless the reference is set.

3. Set obj = CreateObject("Excel.Solver")
or: Set obj = CreateObject("Excel.Solve")
or: Set obj = GetObject(,"Excel.Solver")
or: Set obj = GetObject(Application.LibraryPath
& "\Solver\Solver.xla")
etc. etc. etc.

I have not been able to find a proper incantation of
CreateObject() or GetObject() parameters that return an
object in which I can invoke the SolverSolve
function/method.

I even tried a few zanier things to no avail.

Please help! How can I write portable code that calls
SolverSolve and works with BOTH Excel XP and Excel 2000
(without requiring my macro code file to be copied into
the same directory as solver.xla)?

Any assistance is greatly appreciated.
Chris Russell

 

[Rob Bovey]

Hi Chris,

The Application.Run approach is the one I would suggest. It doesn't
require a reference to the Solver add-in, but it does require that the
Solver add-in be open in Excel and that the calls to Solver procedure names
be fully qualified, e.g.:

Application.Run "Solver.xla!SolverSolve", False

Because Solver is a demand-loaded add-in, you have to do something a
little strange to make sure it's actually open in the user's instance of
Excel. Run the following two lines of code prior to calling any Solver
procedures:

Application.AddIns("Solver Add-in").Installed = False
Application.AddIns("Solver Add-in").Installed = True

The reason for this is that if the user already has Solver selected under
Tools/Add-ins when they open Excel, Excel will consider the add-in loaded
even though Solver.xla doesn't actually open until you select its menu.
Explicitly unloading it and then reloading it in VBA forces Solver.xla to
open no matter what the user's initial settings were.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Chris Russell]

Thank you... fully qualifying the procedure name solved
the problem.

Application.AddIns("Solver Add-in").Installed =

False

has the annoying side effect of popping up an informative
worksheet containing copyright and contact info from the
developer's of the Solver add-in, which changes the
Selection and disrupts the execution of my macro. (I
have the premium solver installed from
http://www.solver.com. Perhaps that's why, but the
problem exists even when I'm set to use the standard
solver as well.)

The problem doesn't exist with:

Application.AddIns("Solver Add-in").Installed = True

What happens if I just call .Installed = True? Will that
still force the loading of solver.xla, or do I need to
set .Installed = False first to ensure it loads under all
circumstances? If so, then is there an alternative
method of loading solver.xla?

Thank you again.
Chris Russell

 

[Rob Bovey]

Hi Chris,

False

has the annoying side effect of popping up an informative
worksheet containing copyright and contact info from the
developer's of the Solver add-in, which changes the
Selection and disrupts the execution of my macro. (I
have the premium solver installed from
http://www.solver.com. Perhaps that's why, but the
problem exists even when I'm set to use the standard
solver as well.)

I've never seen that with standard solver (which is the only version
I've ever used). Maybe the premium version makes some setting that affects
both of them. Wouldn't be the first annoying thing they've done to impede
the programmability of their add-in.

What happens if I just call .Installed = True? Will that
still force the loading of solver.xla, or do I need to
set .Installed = False first to ensure it loads under all
circumstances? If so, then is there an alternative
method of loading solver.xla?

Just setting .Installed = True will only work if the user does not
already have the Solver selected under Tools/Add-ins. If the user does have
Solver selected, Excel will already think it is properly installed an so
..Installed = True will have no effect. The only method other than setting
installed to False/True would be to open the Solver add-in directly using
Workbooks.Open.

Have you tried saving your active sheet and selection, turning off
screenupdating and disabling events before you run the .Installed =
False/True, then restoring everything at the end? I know solver has an
annoying habit of turning screenupdating on whenever it feels like it, but
doing the rest of this should at least minimize its effect on your program.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


news:[email protected]...