A Walkthrough Example

The following example automates solving the problem in SOLVSAMP.XLS (discussed here) on the "Portfolio of Securities" worksheet. A brief description of the automation code follows.

Sub Solve_Portfolio_of_Securities()
    Dim oProblem As SolvProblem
    Dim ws As Worksheet

    Set oProblem = New SolvProblem
    
    Set ws = ThisWorkbook.Worksheets("Portfolio of Securities")
    
    'Step 1: initialize the problem by passing a reference to the worksheet of interest
    oProblem.Initialize ws
    
    'Step 2: define the objective cell to be optimized
    oProblem.Objective.Define "E18", slvMaximize
    
    'Step 3: add and initialize the decision cell(s)
    oProblem.DecisionVars.Add "E10:E14"
    oProblem.DecisionVars.Initialize 0.2, 0.2, 0.2, 0.2, 0.2
    
    'Step 4: set the constraints
    With oProblem.Constraints
        .AddBounded "E10:E14", 0#, 1#
        .Add "E16", slvEqual, 1#
        .Add "G18", slvLessThanEqual, 0.071
    End With
    
    'Step 5: set the solver engine to use
    oProblem.Solver.Method = slvGRG_Nonlinear
    
    'Step 6: set solver options
    oProblem.Solver.Options.RandomSeed = 7
    
    oProblem.Solver.SaveAllTrialSolutions = True

    'Step 7: solve the optimization problem
    oProblem.SolveIt
    
    'Step 8: save all trial solutions that passed the constraints to the worksheet for post-processing analysis
    If oProblem.Solver.SaveAllTrialSolutions Then
        ws.Range("o2:az10000").ClearContents
        oProblem.SaveSolutionsToRange ws.Range("o2"), keepOnlyValid:=True
    End If
End Sub

Step 1: Initialize. The first step in setting up a Solver optimization problem is to initialize SolverWrapper by passing a reference of the host Worksheet using the Initialize method of the SolvProblem class. This is a required step.

Step 2: Define the Objective. An important component to an optimization problem is the Objective Function that is to be maximized or minimized by Solver. This is accomplished by defining a single Objective (or Target) cell and Goal Type (Minimize, Maximize, or Target value). This step is required.

Step 3: Add the Decision Variable(s). Decision Variables represent variable cells that are used in computing the formulas in the Objective and Constraint cells. Solver adjusts the values in the Decision variable cells to satisfy the limits on constraint cells and to produce the optimized result for the Objective cell. There can be multiple Decision cells defined with the Add method of the SolvDecisionVars class, but there must be at least one defined for the problem to be valid. Additionally, the Decision cells can be initialized with reasonable guess values prior to solving with the Initialize method of the SolvDecisionVars class. This is a required step.

Step 4: Set the Constraints. Constraints are logical conditions that a solution to an optimization problem must satisfy. While setting constraints is not required, it is generally a good practice to apply constraint(s) to each Decision variable in order to narrow the solution space that has to be searched, where possible. Searching an unnecessarily large solution space can be computationally wasteful and expensive. In fact, if using the Evolutionary solver engine, each Decision variable is required to have both low- and high-bound constraints.

Constraints can also be used to limit what type of value a Decision variable can hold, such as an Integer or Binary (0 or 1) type. SolverWrapper exposes the SolvConstraints class to set and manage constraints.

Step 5: Select the Solver Engine. Solver has three methods for searching the problem solution space. You select which based on the nature of the optimization problem. While the details of each method are beyond the scope of this Wiki, general advice in choosing which to use is as follows:

• Generalized Reduced Gradient (GRG) Nonlinear: Use for problems that are smooth nonlinear (default).
• LP Simplex: Use for problems that are linear.
• Evolutionary: Use for problems that are non-smooth.

Step 6: Set the Solver Options. Solver has many optional settings that affect the Solver search, such as convergence criteria, the degree of precision to match constraints, etc. For more info, see the SolvOptions class in the Object Model Overview.

Step 7: Solve the Problem. Finally, after defining the problem by setting the Objective, Decision Variables, and Constraints, and then selecting the Solver Engine and Options the optimization can be solved via the SolveIt method of the SolvProblem class. The Excel status bar will show progress and a a final status message.

Step 8: Post-Processing. If the user tells SolverWrapper to save all trial solutions, as shown in the above example by setting the SaveAllTrialSolutions property to True, then those solutions can be saved to an array or a worksheet Range for further analysis.

The image below shows the result of running the above optimization procedure.

Next Topic

Using the Enhanced Callback