VBA Code Reference to Solver Failure !

[monir]

Hello;

Could someone please advise on how to check in a VBA macro if Solver has
encountered a problem and could not find a solution ??

Basically, after loading and calling Solver, I would like to make sure that
a solution has been found by Solver before continuing with the computations.
--------------------------------------------------------
SolverOk SetCell:="$E$54", MaxMinVal:=1, ValueOf:="0", ByChange:= _
"$B$49,$F$54,$G$54"
'....your statement .....
--------------------------------------------------------

And, if Solver is run manually from the Tool menu, is "Sover could not find
a feasible solution" a run-time error ??

Thank you kindly.

 

[Tom Ogilvy]

Here is a reference that you might find helpful:

http://support.microsoft.com/default.aspx?scid=kb;en-us;843304
How to create Visual Basic macros by using Excel Solver in Excel 97

 

[Jon Peltier]

Documentation for automation of the Solver is cloudy at best. You need to
treat SolverSolve as a function:

success = SolverSolve(blah blah)

This page lists the stopping conditions and the corresponding value for
success returned by SolverSolve:

http://www.solver.com/suppstdmsgresult.htm

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

 

[monir]

Hello;
I've reviewed your referenced links, and unfortunately could not find a
relevant answer!
I've implemented a relatively simple procedure to assess whether Solver has
succeded or not:
1. In the Solver macro, assign a Scenario name to the solution (in case
there're multiple Solver calls in the macro), and show the Scenario on the
relevant w/s.
2. If Solver failed, the changing cells would almost always have
out-of-bound values.
3. In a separate cell on the w/s, compare the input overall controling value
(as in any what-if) with that from the Solver solution.
4. If the comparison is satisfactory, return a value of 0 to the cell,
otherwise return a text value.
5. In the Solver macro, check the value in that cell. If it's a text,
adjust the values in the changing cells accordingly and as applicable.
6. I've extensively tested the above simple procedure, and it worked in
every and each case when Solver could not initially find a feasible solution.

That said, the idea of treating Solver as a function is extremely
interesting, if I just know a little bit more about how-to!!

Regards.