Hello everyone,

I am trying to use the Excel Solver to optimize 3 variables (smoothing parameters) for a statistical forecast model. In the attached file, you will see that I set up the Objective Function (minimize) in column AM. I have the 3 variables in columns AN, AO, and AP. The VBA code uses a "For" with a nested "If" to loop the Solver to execute for each row individually. You will see I have 17 rows it loops through, so it must find a solution for 17 problems when executing the macro.

I have two questions, both pertain to the SolverOptions function of the Solver:
  1. You can see in my attached file that the Solver violates the lower and upper bounds I set for the variables. Why is this? Are theer SolverOptions settings I can tailor to ensure the solution is within the defined bounds?
  2. The Solver runs through these problems very quickly. I fear that it is not converging on the "optimal" solutions (variable values). Again, are there SolverOptions I can set to ensure I am close to the Optimal? (I am trying to achieve variable values to the nearest hundreth (+/-0.01).

Also, here is the VBA:

Sub Macro()

    'Initialize variables
    Dim lngMyRow As Long
    Dim lngLastRow As Long
    
    'Find where the last row currently resides
    lngLastRow = Range("AM:AM").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Application.ScreenUpdating = False
    
    'Beginning on row 33
    For lngMyRow = 33 To lngLastRow
        
    'IF statement to run this 1st portion only if "AL & lngMyRow" = Y, otherwise, skip:
          
    If InStr(1, Range("$AL$" & lngMyRow).Value, "Y") Then
     
        'Reset the Solver for Trend Seasonal model
        Application.Run "SolverReset"
        'Include the constraint that the cell value must be less than or equal to 0.99 (1 means "less than or equal to")
        Application.Run "SolverAdd", "$AN$" & lngMyRow & ":$AN$" & lngMyRow, 1, ":$AN$30"
        Application.Run "SolverAdd", "$AO$" & lngMyRow & ":$AO$" & lngMyRow, 1, ":$AO$30"
        Application.Run "SolverAdd", "$AP$" & lngMyRow & ":$AP$" & lngMyRow, 1, ":$AP$30"
        'Include the constraint that the cell value must be greater than or equal to 0.01 (3 means "greater than or equal to")
        Application.Run "SolverAdd", "$AN$" & lngMyRow & ":$AN$" & lngMyRow, 3, ":$AN$29"
        Application.Run "SolverAdd", "$AO$" & lngMyRow & ":$AO$" & lngMyRow, 3, ":$AO$29"
        Application.Run "SolverAdd", "$AP$" & lngMyRow & ":$AP$" & lngMyRow, 3, ":$AP$29"
        'Solve to minimize the objective function (the cell containing MAPE) (2 means "minimize)
        Application.Run "SolverOk", "$AM$" & lngMyRow, 2, "0", "$AN$" & lngMyRow & ":$AP$" & lngMyRow
        Application.Run "SolverSolve", True
    End If
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub
example.xlsm