VBA Solver disables the dialog that appears after each iteration
I am using the built-in solver in Excel 2007 in a VBA loop to solve a number of different problems. Sometimes the solver reaches the maximum time, which causes a pop-up dialog box to appear asking if the user wants to continue, stop, or complete. In all cases, I want this to end and move on to the next line of the loop. This will prevent the user from sitting and answering every time.
I ran the macro using passver through thore method ( Catch max time / iteration dialog when using Excel Solver in VBA ) but then it gives me another dialog saying "Formula entered contains error"
I have also tried http://msdn.microsoft.com/en-us/library/office/ff197237(v=office.15).aspx . Which is a less complicated version of "Solver to go through method" but after each iteration it gives me the same message that "Formula entered contains an error" This is my code
Sub Optimize()
'
' OptimizeShortfall Macro
'
'
Set MyFirstObj = Range("I124")
Set MyFirstRange = Range("H600:H698")
Dim i As Integer
For i = 0 To 1
MyObj = MyFirstObj.Offset(0, i).Address
MyTestRange = MyFirstRange.Offset(0, i).Address
SolverReset
SolverOk SetCell:=MyObj, MaxMinVal:=1, ValueOf:="0", ByChange:= _
MyTestRange
SolverAdd CellRef:=MyTestRange, Relation:=1, FormulaText:="100%"
SolverOptions MaxTime:=20, Iterations:=100, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True
SolverSolve UserFinish:=True, ShowRef:="SolverIteration"
Next i
End Sub
Function SolverIteration(Reason As Integer)
MsgBox Reason
SolverIteration = 1
End Function
source to share
http://msdn.microsoft.com/en-us/library/office/ff197237(v=office.15).aspx will clearly explain to you when the dialog box appears, what it means and how to avoid it if you are running the solver in a loop.
One forceful solution for going to the next loop when the maximum time / iteration was reached was to keep Alt + T pressed throughout the entire macro.
Another smarter way is to use the ShowRef argument in the SolverSolve function. What ShowRef does, instead of entering a dialog box, will run the macro given as an argument, in this case "SolverIteration". If you want the solver to keep running for a given iteration value, set SolverIteration to 0. If you want to stop the solver for a given iteration and move on to the next iteration, set SolverIteration to 1.
Note that there are some problems in the ShowRef argument when digesting books with a very long name and / or names with a space in them, so keep the workbook name as short as possible.
Sorry if my explanation is not enough for you. Here is an exhaustive list of three links I need to solve my problem: http://msdn.microsoft.com/en-us/library/office/ff197237(v=office.15).aspx
Catch max time / iteration dialog when using Excel Solver in VBA
http://www.excelforum.com/excel-programming-vba-macros/555406-solved-solver-solversolve-showref.html
PS In my code mentioned in the above question, just remove the "MsgBox Reason" line from the "SolverIteration" function. (This just brings up another dialog with an integer of Reason value on it: P)
source to share