{"id":164,"date":"2014-02-28T11:49:29","date_gmt":"2014-02-28T18:49:29","guid":{"rendered":"https:\/\/blogs.ubc.ca\/coetoolbox\/?page_id=164"},"modified":"2016-09-26T10:32:04","modified_gmt":"2016-09-26T17:32:04","slug":"solver","status":"publish","type":"page","link":"https:\/\/blogs.ubc.ca\/coetoolbox\/vba\/appdesign\/solver\/","title":{"rendered":"Connecting to Open Solver"},"content":{"rendered":"<p><strong>Reset Model<\/strong><\/p>\n<p><span style=\"color: #0000ff;\">SolverReset()<\/span>: resets model and restores all the Solver settings to their defaults.<\/p>\n<p><strong>Set decision variables and objective function<\/strong><\/p>\n<p><span style=\"color: #0000ff;\">SolverOK<\/span> (SetCell, MaxMinVal, ValueOf, ByChange)<\/p>\n<ul>\n<li>SetCell as Range: the objective function cell<\/li>\n<li>MaxMinVal: maximize = 1, minimize = 2, target a specific value = 3<\/li>\n<li>ValueOf: (only if MaxMinVal = 3) the target value<\/li>\n<li>ByChange as Variant: range of decision variables<\/li>\n<\/ul>\n<p><strong>Set constraints<\/strong><\/p>\n<p><span style=\"color: #0000ff;\">SolverAdd<\/span> (CellRef, Relation, FormulaText)<\/p>\n<ul>\n<li>CellRef as Variant: range of cells that forms the left side of a constraint<\/li>\n<li>Relation: the relationship between the left and right sides of the constraint:<\/li>\n<\/ul>\n<p style=\"padding-left: 60px;\">1 &lt;=<br \/>\n2 =<br \/>\n3 &gt;=<br \/>\n4 CellRef must have integer values<br \/>\n5 CellRef must have binary values<br \/>\n6 CellRef must have different and integer values<\/p>\n<ul>\n<li>FormulaText as Variant. The right side of the constraint. If you choose 4, 5, or 6, FormulaText should not be specified and CellRef must refer to decision variable cells.<\/li>\n<\/ul>\n<p><strong>Set solving options<\/strong><\/p>\n<p><span style=\"color: #0000ff;\">SolverOptions<\/span> (\u2026)<\/p>\n<p>MaxTime: maximum time (in seconds) to solve problem<\/p>\n<p>AssumeNonNeg: \u2018true\u2019 to assume non negative decision variable (for the ones that do not have explicit lower limits)<\/p>\n<p>AssumeLinear: \u2018true\u2019 to assume that the underlying model is linear<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Solve the model<\/strong><\/p>\n<p><span style=\"color: #0000ff;\">RunOpenSolver<\/span> (SolveRelaxation As Boolean)<\/p>\n<p>SolveRelaxation = \u2018true\u2019 forces OpenSolver to solve the LP relaxation of an IP<\/p>\n<p>You can also use Excel Solver, but you will have a limit of 200 decision variables.<\/p>\n<p><span style=\"color: #0000ff;\">SolverSolve<\/span> UserFinish:=True<\/p>\n<p>The command \u000breturns the type of result the Solver found<\/p>\n<ul>\n<li>0, 1, or 2 = a successful run in which a solution has been found<\/li>\n<li>4 = there was no convergence<\/li>\n<li>5 = no feasible solution could be found<\/li>\n<\/ul>\n<p>Note: before you use these commands you need to have Solver and OpenSolver add-ins installed, enabled and referenced in the project library.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Reset Model SolverReset(): resets model and restores all the Solver settings to their defaults. Set decision variables and objective function SolverOK (SetCell, MaxMinVal, ValueOf, ByChange) SetCell as Range: the objective function cell MaxMinVal: maximize = 1, minimize = 2, target a specific value = 3 ValueOf: (only if MaxMinVal = 3) the target value ByChange [&hellip;]<\/p>\n","protected":false},"author":22982,"featured_media":0,"parent":78,"menu_order":4,"comment_status":"open","ping_status":"closed","template":"page-templates\/full-width.php","meta":{"footnotes":""},"class_list":["post-164","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/164","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/users\/22982"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/comments?post=164"}],"version-history":[{"count":5,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/164\/revisions"}],"predecessor-version":[{"id":1504,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/164\/revisions\/1504"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/78"}],"wp:attachment":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/media?parent=164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}