Iterating Excel Solver - VBA

Question

Does the Solver, when run through a VBA induced loop only work once?  I have a chunk of code that runs the Solver iteratively over a number of rows. 

It works fine if I break the loop with a dialog/msgbox, but fails to update cell contents if I take out the message box. (Code at the bottom)

Is this a know issue?  Or is there something I am missing in running the solver?

I am running Excel 2007 on Win7

I've only found this so far;

http://www.teachexcel.com/excel-help/excel-how-to.php?i=128408

Anyone have any ideas?

 - A

Here's  a simplified code extract;

' There are 200+ rows that I need to run the solver on

' So I've set up a message box to allow the solver to progress - but only for the first 20 rows

' the solver works as expected for the first 20 rows, and then seems to be pypassed for the rest of the loop

For rwcounter = 0 to 200                          ' loop through 200 rows

-- Some code in here that updates the rows that are being worked on and the two cells the solver uses (pricecell, avcell)

If rwcounter <= 20 Then

       Dim result As Long

       result = MsgBox("Run solver?",  vbYesNo)

       If result = vbYes Then

               If solveprices(pricecell, avcell) Then

                      ' Do nothing    

               Else

                       MsgBox ("failed to solve prices")

               End If

       End If

End If

Next rwcounter

Private Function solveprices(pricecell As String, avcell As String) As Boolean

    SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.001, AssumeLinear:= _

        False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _

        IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False

    SolverOk SetCell:=avcell, MaxMinVal:=3, ValueOf:="0", ByChange:=pricecell

    SolverSolve userfinish:=True

    SolverFinish KeepFinal:=1

    SolverReset

    solveprices = TRUE     'Set to true for now, just to get

End Function

Answer 1

Does the Solver, when run through a VBA induced loop only work once? 

No. But you should call SoverReset before you calculate the formula to make sure all settings are correct.

Execute the sub below in an empty sheet.

Andreas.

Sub Example_SolverPrecision()

  Dim i As Integer

  Dim A As Range, B As Range, C As Range

  Cells.ClearContents

  Set A = Range("A1")

  Set B = Range("B1")

  Set C = Range("C1")

  For i = 1 To 16

    'Setup the Pythagoras formula

    C.Formula = "=Sqrt(" & A.Address & "^2+" & B.Address & "^2)"

    SolverReset

    'Solve first row with 0.1, second row with 0.01, etc.

    SolverOptions Precision:=1 / 10 ^ i

    'Solve cell in column C, change cells in column A and B

    SolverOk SetCell:=C.Address, MaxMinVal:=3, ValueOf:=5, ByChange:=A.Address & "," & B.Address

    'Cell in column B must be an integer

    SolverAdd CellRef:=B.Address, Relation:=4

    'Solve it

    SolverSolve True

    'next row

    Set A = A.Offset(1)

    Set B = B.Offset(1)

    Set C = C.Offset(1)

  Next

End Sub

Answer 2

Hi Andreas,

thankyou for the reply.  I played with your code and it worked fine although it made no difference to the output if the SolverReset line was commented/uncommented.

I jiggled some of my code about and it works now - again it makes no difference if I use the SolverReset command or not.  Here's my final code structure;

FOR loop

    update row references

    call solver (references)

NEXT

SOLVER (reference)

     SolverOK Setcell etcetc

     SolverOptions maxtime etc etc

     SolverSolve userfinish:TRUE

     SolverFinish KeepFinal:=1

END

Looks like SolverRest might fix things in some circumstances, but not all. I'll have to test my code on a few other machines, but seems to be one of those Excel quirks!