VBA Macro optimization using Solver in Excel not returning optimal variables

Question

I am trying to optimize three parameters in Excel in order to minimize the error between a experimental value and a theoretical value. I use Solver for each parameter, one at a time, in a for loop. However, I want to iterate this solver for loop (loop inside a loop) until the error in the experimental value and the theoretical value is less than some target value.

My experimental value is $K25.
My theoretical value (calculated based on my model equations) is $J$25.
My parameters that need to be optimized are $C$4, $C$5, $C$6

When I run the following VBA code my parameters in $C$4, $C$5, $C$6 do not change from their initial values. However, the macro compiles fine with no errors. Can anyone help me out here?

Here is the code:

Sub Macro3()
    Application.ScreenUpdating = False
    SolverReset
    Dim j As Integer
    For j = 1 To 100 Step 1
        If "$J$25" > "$K$25" Then
            Dim i As Integer, s As String
            For i = 4 To 6 Step 1
            s = Format(i, "0")
                SolverOk SetCell:="$J$25", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$" & s, Engine:= _
                1, EngineDesc:="GRG Nonlinear"
                SolverOptions MaxTime:=0, Iterations:=1000000, Precision:=0.000001, Convergence _
                :=0.00001, StepThru:=False, Scaling:=True, AssumeNonNeg:=True, Derivatives:=1
                SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, Multistart _
                :=False, RequireBounds:=True, MaxSubproblems:=0, MaxIntegerSols:=0, _
                IntTolerance:=1, SolveWithout:=False, MaxTimeNoImp:=30
                SolverOk SetCell:="$J$25", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$" & s, Engine:= _
                1, EngineDesc:="GRG Nonlinear"
                SolverSolve (True)
                SolverReset
            Next i
        End If
    Next j
    Application.ScreenUpdating = True
End Sub

Solution 1

I'm not really sure you need to do this in VBA, as what you're looking for is exactly what the Solver ought to do - modify a set of parameters so that something else is maximized/minimized!

Therefore, all you need to do is to insert the formula =ABS(J25-K25) in another cell. This cell will display the delta between your experimental value and the theoretical value. Now set up your Solver so that it minimizes this cell by changing your three parameters - and you're done! (Note that you can provide more than one cell in the "By Changing Variable Cells" field!)

In case you want to stick to your approach, here is the syntactical correct code. Note that I have not tested it - but only corrected the mistakes I could spot by looking through the code. It will hopefully be a good starting point. In fact, looking at this approach, I'm sure you'll end up with the wrong result, because each run optimizes only one variable - and you'll therefore never look into any effects that result from the combination of two or three parameters!

Anyway, here's your code:

Sub RunSolver()
    Dim j As Integer, i As Integer

    Application.ScreenUpdating = False
    SolverReset

    For j = 1 To 100
        Application.Statusbar = j & "/100"
        If Range("$J$25") > Range("$K$25") Then
            For i = 4 To 6
                SolverOk SetCell:=Range("$J$25"), MaxMinVal:=2, ValueOf:=0, ByChange:=Range("$C$" & i), Engine:= _
                1, EngineDesc:="GRG Nonlinear"
                SolverOptions MaxTime:=0, Iterations:=1000000, Precision:=0.000001, Convergence _
                :=0.00001, StepThru:=False, Scaling:=True, AssumeNonNeg:=True, Derivatives:=1
                SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, Multistart _
                :=False, RequireBounds:=True, MaxSubproblems:=0, MaxIntegerSols:=0, _
                IntTolerance:=1, SolveWithout:=False, MaxTimeNoImp:=30
                SolverSolve (True)
                SolverReset
            Next i
        End If
    Next j

    Application.StatusBar = False
    Application.ScreenUpdating = True
End Sub

Solution 2

You may double check that line of your code that says:

Engine:= 1, EngineDesc:="GRG Nonlinear"

According to MS documentation:

• 1 for the Simplex LP method,
• 2 for the GRG Nonlinear method, or
• 3 for the Evolutionary method.

Probably, your objective function is nonlinear and you thought you are using the GRG Nonlinear solver since you mention it under the EngineDesc parameter. Which is incorrect. This is just a description parameter.

The solver you are actually using is Simplex LP which has the value of 1.

Change to 2 to use GRG Nonlinear solver.

Comments

[user2092724]

Thanks! What you suggest is correct. I am calculating the absolute value of the total difference in my theoretical equations versus my experimental results. I then optimize my parameters to minimize this difference. $K$25 is my target difference and $J$25 is my current difference based on the parameters in $C$4, $C$5, $C$6. So yes, as you suggest I could simply have solver optimize all three parameters at once and I would be done. However, I eventually want to optimize 10 parameters instead of three over much more complicated equations. And combinations of multiple parameters will be tricky!

[Peter Albert]

Pretty sure the Solver can also handle 10 parameters and get to better results then a macro optimizing each parameter wrongly assuming ceteris paribus!

[user2092724]

The code you suggest works. I just tested it. However, if $K$25 (target difference) is set below a value on which Solver converges Excel keeps calculating and doesn't get anywhere. Not sure if you know a quick way of preventing this from happening?

[Peter Albert]

what about multiplying the difference with a large factor?

[user2092724]

Yes solver can handle ten parameters. But likely not when the equations I have are complicated and solved over 100K rows. In my more complicated case the most I can get solver to do is two or three parameters at a time for which only one parameter actually changes from its initial value in the end. Hence, I thought doing one parameter at a time is as good as it gets even though I am neglecting the effects of changing multiple parameters at once. I am hoping that iterating through each parameter will eventually converge on a meaningful result that physiologically makes sense. Thanks Again!

[Peter Albert]

I see. Check for alternative solvers add-ins. I remember that there are way more powerful solvers out there in the market, though don't know any name anymore. Also, they most likely will not be free...

[Peter Albert]

Okay, I just checked my old files - the Solver was www.solver.com. They provide a free trial version that is fully functional as far as I remember - give it a shot. (Disclaimer: I'm not associated with them)