Solver programming in VBA

[faehnle]

In trying to model an optimization problem, I have successfully
written a macro to iterate through all of the branches of the problem,
and copy specified values from the relevant sensitivity reports to a
new worksheet (called "Summary"). However, in adding some new
constraints to the model, it turns out that the model becomes
infeasible for some of the branches. Thus, rather than generating a
Sensitivity Report, solver generates a Feasibility Report.

My problem: in my VBA code, I reference the Sensitivity Report
worksheet by name rather than index number. This causes my macro to
bomb out if a particular branch of the problem is infeasible (because
there is no appropriate Sensitivity Report to reference). However, if
I were to call the sheet by index number, I'd end up (1) not knowing
that some of the solutions in "Summary" were infeasible, and that some
of the data on that sheet comes from feasibility reports rather than
sensitivity reports.

My question: is there a way to have my macro behave differently if
solver finds that a particular iteration of a problem is infeasible?

Relevant code snippet below:

SolverSolve (True)
efficiency = Cells(27, 11)
'Create Sensitivity report if branch is inefficient
If (Round(efficiency, 2) < 1) Then
SolverFinish ReportArray:=Array(2)
refset = 0
nrefset = 0
'Find branches that constitute reference set of
inefficient branch
While refset < branches
Sheets("Sensitivity Report 1").Select
If (Round(Cells(refset + 28, 5), 2) > 0) Then
Sheets("Summary").Select
Cells(k + 27, nrefset + 12) = (refset + 1)
nrefset = nrefset + 1
End If
refset = refset + 1
Wend
Application.DisplayAlerts = False
'Get rid of Sensitivity report, no questions asked...
Sheets("Sensitivity Report 1").Delete
Application.DisplayAlerts = True
Sheets("Branch Analysis G-IN").Select
End If

My apologies if my code is obtuse or cluttered, this is my first foray
into VBA.

Thanks for your help!

Andrew

 

[SteveM]

Andrew,

You want to intercept the Solver return code and then run reports only
if the return value is optimal. An optimal solution for a linear
model returns 0, and 5 for an infeasible solution. However if you a
running an integer model that terminates within a specified optimal
bound it will return a different code than 0 even though it may not be
theoretically optimal. I use the Premium Solver Platform so cannot
guarantee that the return codes are the same for the Standard solver,
but I'm guessing that they are.

Note that infeasible formulations can often be formulated to always
feasible by the insertion of "Big M" dummy variables in the
formulation. A check of the dummy variable value ( > 0) at the
conclusion indicates whether the thing was fundamentally feasible.
This is a very effective strategy for resource allocation problems
with a weighted objective function in which less than full resources
are available.

BTW, you have more control over the outputs using the SolverFinish
function. So check that out too.

SteveM

 

[Dana DeLouis]

An optimal solution for a linear model returns 0,

Hi. Just to add. Depending on the op's model, either 0,1, or 2 may also be
valid.

Results = SolverSolve (True)


0 Solver found a solution. All constraints and optimality
conditions are satisfied.

1 Solver has converged to the current solution. All constraints
are satisfied.

2 Solver cannot improve the current solution. All constraints are
satisfied.

As a side note, make sure you don't use Integer constraints, as Solver will
not generate a Sensitivity report.

Another general idea for reports:

If (Round(efficiency, 2) < 1) Then
SolverFinish ReportArray:=Array(2)
Else
SolverFinish ' No Reports
End If

 

[Jon Peltier]

I've lost the original source, but here are the return codes for standard
Solver:

0 Solver found a solution. All constraints and optimality conditions are
satisfied.
1 Solver has converged to the current solution. All constraints are
satisfied.
2 Solver cannot improve the current solution. All constraints are
satisfied.
3 Stop chosen when the maximum iteration limit was reached.
4 The Set Cell values do not converge.
5 Solver could not find a feasible solution.
6 Solver stopped at user's request.
7 The conditions for Assume Linear Model are not satisfied.
8 The problem is too large for Solver to handle.
9 Solver encountered an error value in a target or constraint cell.
10 Stop chosen when maximum time limit was reached.
11 There is not enough memory available to solve the problem.
12 Another Excel instance is using SOLVER.DLL. Try again later.
13 Error in model. Please verify that all cells and constraints are valid.

- Jon