VBA Solver code issue

Question

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

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 — Suraj Nagabhushana Ponnaganti
– Suraj Nagabhushana Ponnaganti, Commented Feb 4, 2016 at 13:00

John Coleman · Accepted Answer · 2016-02-04 13:39:28Z

1

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.

answered Feb 4, 2016 at 13:39

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Suraj Nagabhushana Ponnaganti Over a year ago

Thank you for your reply. Great advice!

Guille Over a year ago

@John Coleman Totally agree, super advice!!

Collectives™ on Stack Overflow

VBA Solver code issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related