CODEWITHSUNDEEP
vbaexcel
Suraj Nagabhushana Ponnaganti
Suraj Nagabhushana Ponnaganti

Reputation: 1257

VBA Solver code issue

I am currently using a loop is which I access the excel solver. But every time the solver solves an optimization problem, I have to click ok. I tried record macro, but no code came up when I click the OK button. I will always accept the solution suggested by the solver.I do not want to keep pressing Ok button all the time. There could potentially be 2000+ optimization problems being solved. Is there a way of making this work?

Also, is there a way of solving unbalanced transportation problem is a solver without having to make dummy supplies or demand nodes?

Thank you.

The codes is as follows: SolverReset

SolverOk SetCell:="$B$18", MaxMinVal:=2, ValueOf:=0, _
ByChange:="$M$5:$T$12", Engine:=2, EngineDesc:="Simplex LP"

SolverAdd CellRef:="$U$5:$U$12", Relation:=1, FormulaText:="$V$5:$V$12"

SolverAdd CellRef:="$M$13:$T$13", Relation:=2, FormulaText:="$M$14:$T$14"
SolverSolve

Upvotes: 0

Views: 849

Answers (1)

John Coleman
John Coleman

Reputation: 51998

The documentation at Solver.com (the website of the company that made the Solver add-in) contains:

SolverSolve(UserFinish, ShowRef)

UserFinish Optional Variant. True to return the results without displaying the Solver Results dialog box. False or omitted to return the results and display the Solver Results dialog box.

ShowRef Optional Variant. You can pass the name of a macro (as a string) as the ShowRef argument. This macro is then called, in lieu of displaying the Show Trial Solution dialog box, whenever Solver pauses for any of the reasons listed below.

Thus it seems that you need to replace the line SolverSolve by

SolverSolve True

But -- it is probably a better idea to read the rest of the documentation about the return value of SolverSolve and perhaps do something like

retValue = SolverSolve(True)

and then check retValue with some error-handling if it isn't one of the values which correspond to a successful run. People can be optimistic. Code should be pessimistic.

Upvotes: 1

Related Questions