How to make Application.Run "SolverOptions" work?

Question

If necessary, please refer to "badSolver.xlsm" (click here) [1].  Ignore any preview errors; box.net limitations.

How can I make Application.Run "SolverOptions" work?

The following macros work just fine when I select the Solver reference (Tools > References).  Execute goodTest.

Sub goodTest()

    solveIt "F1", "F2"

End Sub

Private Sub solveIt(myChangeCell As String, mySetCell As String)

    Dim x As Variant

    Range(myChangeCell).ClearContents

    SolverReset

    SolverOk setCell:=mySetCell, MaxMinVal:=3, _

        ValueOf:=0, ByChange:=myChangeCell, Engine:=1, _

        EngineDesc:="GRG Nonlinear"

    SolverOptions MaxTime:=0, Iterations:=0, _

        Precision:=0.00000000001, Convergence:=0.00000000001, _

        StepThru:=False, Scaling:=True, AssumeNonNeg:=True, _

        Derivatives:=1

    SolverOptions PopulationSize:=True, RandomSeed:=0, _

        MutationRate:=0.075, Multistart:=False, _

        RequireBounds:=True, MaxSubproblems:=0, _

        MaxIntegerSols:=0, IntTolerance:=1, SolveWithout:=False, _

        MaxTimeNoImp:=30

    x = SolverSolve(True)

    If x > 3 Then

        With Range(myChangeCell)

            .HorizontalAlignment = xlRight

            .Value = "#Error " & x

        End With

    End If

End Sub

But the following macros, in another module, fail on the highlighted line with runtime error #13 ("type mismatch") when I deselect the Solver reference, as intended.  Execute badTest.

Sub badTest()

    solveIt "F1", "F2"

End Sub

Private Sub solveIt(myChangeCell As String, mySetCell As String)

    Dim x As Variant

    Range(myChangeCell).ClearContents

    Application.Run "SolverReset"

    Application.Run "SolverOk", "setCell:=mySetCell", "MaxMinVal:=3", _

        "ValueOf:=0", "ByChange:=" & myChangeCell, "Engine:=1", _

        "EngineDesc:=GRG Nonlinear"

Application.Run "SolverOptions", "MaxTime:=0", "Iterations:=0", _         "Precision:=0.00000000001", "Convergence:=0.00000000001", _         "StepThru:=False", "Scaling:=True", "AssumeNonNeg:=True", _         "Derivatives:=1"

    Application.Run "SolverOptions", "PopulationSize:=True", "RandomSeed:=0", _

        "MutationRate:=0.075", "Multistart:=False", _

        "RequireBounds:=True", "MaxSubproblems:=0", _

        "MaxIntegerSols:=0", "IntTolerance:=1", "SolveWithout:=False", _

        "MaxTimeNoImp:=30"

    x = Application.Run("SolverSolve", True)

    If x > 3 Then

        With Range(myChangeCell)

            .HorizontalAlignment = xlRight

            .Value = "#Error " & x

        End With

    End If

End Sub

The only difference is:  I replaced direct calls to SolverXXX with calls to Application.Run "SolverXXX" in effort to avoid the need to select the Solver reference, following the paradigm described by Jon Peltier (click here) [2].

In an effort to isolate the problem, I removed syntax piecewise on the Application.Run "SolverOptions" statement, to no avail.

When I reduce the syntax to just Application.Run "SolverOptions" with no parameters, I no longer get a VBA error.  But SolverSolve returns error #13 (coincidentally).  Ostensibly, that means:  "Error in model. Please verify that all cells and constraints are valid".

But goodTest works just fine even if I remove all options from the SolverOptions calls.  Of course, first I must select the Solver reference.

---

[1] https://app.box.com/s/x1ts7ceumx46v4gtb95v

[2] http://peltiertech.com/Excel/SolverVBA.html

Answer 1

You must pass the arguments to SolverOK and to SolverOptions by position, not by name, and you must pass the correct data types

Private Sub solveIt(myChangeCell As String, mySetCell As String)

    Dim x As Variant

    Range(myChangeCell).ClearContents

    Application.Run "SolverReset"

    Application.Run "SolverOk", mySetCell, 3, 0, myChangeCell, 1, "GRG Nonlinear"

    Application.Run "SolverOptions", 0, 0, 0.00000000001, , _

        False, , 1, , 1, True, True, True, 0, False, _

        True, 0.075, 0, 0, False, 30

    x = Application.Run("SolverSolve", True)

    If x > 3 Then

        With Range(myChangeCell)

            .HorizontalAlignment = xlRight

            .Value = "#Error " & x

        End With

    End If

End Sub

Answer 2

This looks so close to what I need, The only thing I need to do is solve for negative numbers and for some reason I can't get it to work. I would think the 12 item is AssumeNonNeg so I tried this but it wouldn't work:

Application.Run "SolverOptions", 0, 0, 0.00000000001, , _        False, , 1, , 1, True, True, False, 0, False, _        True, 0.075, 0, 0, False, 30

Tried stepping through and setting others to false and that really didn't work either. I'm guessing there's some kind of combination of these settings that I have to change but really don't know what they would be. Do you know how I could get solver to work using application.run on negative values?

Thanks so much for the help this post was extremely valuable. Got me 98% to where I need to be!

Actually I figured it out Should have tried a bit more but if anyone is interested this works:

Application.Run "SolverOptions", 0, 0, 0.00000000001, , _

        False, , 1, , 1, True, 0.9, False, False, 0, False, _

        True, 0.075, 0, 0, False, 30

Answer 3

HansV wrote:

You must pass the arguments to SolverOK and to SolverOptions by position, not by name, and you must pass the correct data types

[....]

    Application.Run "SolverOk", mySetCell, 3, 0, myChangeCell, 1, "GRG Nonlinear"

    Application.Run "SolverOptions", 0, 0, 0.00000000001, , _

        False, , 1, , 1, True, True, True, 0, False, _

        True, 0.075, 0, 0, False, 30

Ding!  Thanks.  I was fooled because I did not get a runtime error with the named parameters on the Application.Run "SolverOk" call.  But I should have become suspicious when SolverSolve returned Solver error #13.  I didn't expect the named parameters to work in the first place.  Klunk!

Answer 4

Hi.  I see you have an answer.

Just note the x = 3  "Coud be an error" if it aborts the calculations due to too much time.

3   Stop chosen when the maximum iteration limit was reached.

If you wish ...

   With Application

        .Run "SolverReset"

        .Run "SolverOk", mySetCell, ...

        .Run "SolverOptions", 0, 0,...