Asked by:
Two Excel Solver bugs when adding two constraints
Question

For a very small problem (actually a series of related small problems) I tried to use Solver from VBA. I encountered two bugs that were not completely trivial to find a workaround for.
Sub runsolver() Dim sumcell As String Dim objcell As String Dim xcells As String Dim fixcell As String Dim fxvalue As Double: fxvalue = 0.5 sumcell = Range("sumcell").Address(True, True, xlA1, True) objcell = Range("objcell").Address(True, True, xlA1, True) xcells = Range("xcells").Address(True, True, xlA1, True) fixcell = Range("xcells").Cells(1, 1).Address(True, True, xlA1, True) Call runsolver2(sumcell, objcell, xcells, fixcell, fxvalue) End Sub Sub runsolver2(sumcell As String, objcell As String, xcells As String, fixcell As String, fixvalue As Variant) Call Solver.SolverReset ' constraint: sum(x) = 1 ' this constraint is ignored ' workaround: FormulaText:=0.99999999999999 Call Solver.SolverAdd(CellRef:=sumcell, Relation:=2, FormulaText:=1) ' constraint: x1=0.5 ' this constraint leads to unexpected internal error or out of memory ' workaround: declare argument as: BYVAL fixvalue as variant Call Solver.SolverAdd(CellRef:=fixcell, Relation:=2, FormulaText:=fixvalue) Call Solver.SolverOk(SetCell:=objcell, MaxMinVal:=2, ValueOf:=0, ByChange:=xcells) Call Solver.SolverOptions(AssumeNonNeg:=True) Dim rc As Integer rc = Solver.SolverSolve(True) Call Solver.SolverFinish(KeepFinal:=1, ReportArray:=Array(1)) End Sub
The first bug is somewhat incomprehensible to me: if I introduce a constraint like x1+x2=1 the whole constraint is completely ignored (without a warning message). When I change the 1 into 0.99999999 the constraint is accepted and will be used in the optimization of the model.
The second bug is an Unexpected Internal Error or Out Of Memory. The workaround is mentioned in the above code.
After applying the workarounds I get the correct results:
The problem occurs in different Excel versions including Office 2013.

 
Erwin Kalvelagen
Amsterdam Optimization Modeling Group
erwin@amsterdamoptimization.com
http://amsterdamoptimization.com
 
 Edited by Erwin Kalvelagen Tuesday, August 12, 2014 12:31 AM
 Moved by George123345 Wednesday, August 13, 2014 1:24 AM
All replies

Hi,
This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel
http://social.msdn.microsoft.com/Forums/enUS/home?forum=exceldev&filter=alltypes&sort=lastpostdesc
The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.
Thanks
George Zhao
Forum Support
________________________________________
Come back and mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback on our support, please click "tnfsl@microsoft.com" 

Here it is: http://www.amsterdamoptimization.com/etc/ErrorDemo,xlsm
 
Erwin Kalvelagen
Amsterdam Optimization Modeling Group
erwin@amsterdamoptimization.com
http://amsterdamoptimization.com
  
I don't understand the objective, do you want to change two cells F6:F7 (xcells) or as suggested in the text along side only one of them. Anyway this worked fine for me, from the macro recorder
SolverOk SetCell:="$F$5", MaxMinVal:=3, ValueOf:="1", ByChange:="$F$6:$F$7" SolverSolve
Might want to adjust the precision in the options

It is a dummy objective. The real problem is a portfolio optimization problem where we want to trace an efficient frontier (this is nonlinear). That means solving many problems in a loop, so we need to do this through VBA. This is just a small example that demonstrates the bugs I encountered. I am quite sure they are not related to precision.
A quick google search reveals this problem is encountered by many users, but I thought the small example with some workarounds may be beneficial.
Yes I want to change x1 and x2. The model is given in fairly standard notation. I don't see much ambiguity in how I stated the model even after rereading it.
Erwin
 
Erwin Kalvelagen
Amsterdam Optimization Modeling Group
erwin@amsterdamoptimization.com
http://amsterdamoptimization.com
 
 Edited by Erwin Kalvelagen Saturday, August 16, 2014 8:13 AM