locked
Two Excel Solver bugs when adding two constraints RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    ----------------------------------------------------------------

     



    Tuesday, August 12, 2014 12:27 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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/en-US/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"

    Wednesday, August 13, 2014 1:23 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I think solver is more related to the data and I find you have much used range in your VBA code, you'd better share your sample workbook so that we can run you code and understand you more.

    Friday, August 15, 2014 7:29 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Here it is: http://www.amsterdamoptimization.com/etc/ErrorDemo,xlsm

    ----------------------------------------------------------------
    Erwin Kalvelagen
    Amsterdam Optimization Modeling Group
    erwin@amsterdamoptimization.com
    http://amsterdamoptimization.com
    ----------------------------------------------------------------

    Friday, August 15, 2014 4:25 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    Friday, August 15, 2014 4:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    It is a dummy objective. The real problem is a portfolio optimization problem where we want to trace an efficient frontier (this is non-linear). 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
    ----------------------------------------------------------------



    Friday, August 15, 2014 5:14 PM