Using Solver in VBA sets calculation mode to manual

Question

In trying to help address this question, I came across some very strange behavior from Solver in VBA.

I am wondering if anyone else can reproduce it or if there is something wrong with my system (Windows 10, Excel 2016), and if anyone can point me to what is going on.

The workbook setup is very simple.

Here is the code I am testing ...

Sub mySolve()
    Dim SetRng As Range, ChangeRng As Range
    Dim i As Long
    For i = 2 To 4
        Set SetRng = Sheets("Sheet1").Cells(i, 5)
        Set ChangeRng = Sheets("Sheet1").Cells(i, 4)

        SolverReset
        SolverOk SetCell:=SetRng.Address, MaxMinVal:=3, _
            ValueOf:=1, ByChange:=ChangeRng.Address, _
            Engine:=1, EngineDesc:="GRG NONLINEAR"
        SolverSolve UserFinish:=True
        SolverFinish KeepFinal:=1

    Next i
End Sub

I step through this code using F8 and manually check the calculation mode in Options after each line is executed.

1. After SolverSolve, the calculation mode is manual, and does not get set back unless I do it.
2. If I remove the call to SolverReset, after SolverSolve is called, the calculation mode does NOT change to manual.

Answer 1

I just ran your code in Excel 2010 and got the same results. If anything, it is worse in that I get the bug whether or not SolverReset is called and, furthermore, after running the code the text "Setting Up Problem..." lingers in the status bar on the bottom of Excel

It makes perfect sense that the Solver needs to set calculation to manual with GRG Nonlinear since it is going to be evaluating the objective function by changing cells in the worksheet and wouldn't want to evaluate them until the correct input values are in place. When called from the Excel interface, the Solver cleans up after itself. Whoever put together the VBA interface must have forgotten this little detail. Its clearly a bug. Perhaps you could file a bug report at Solver.com (the website of Frontline Systems, who make the solver for Microsoft) .The workaround is of course easy enough: just put the line

Application.Calculation = xlCalculationAutomatic

at the bottom of the sub.