It is possible to call the Solver programatically through a user defined macro written in VBA. This might be desirable when the Solver model needs to be protected or hidden, for example, when the model is distributed to one or several end users. This article explains how to use the Object-Oriented API in Analytic Solver Platform to create, modify and solve an optimization model under the control of your custom application written in VBA.
You can define a Problem and instantiate it from the spreadsheet with two lines of code, then access the elements of your model via Variable and Function objects. You can perform an optimization, access the values of decision variables, constraints and the objective, access trial data and summary statistics for uncertain variables and functions, and present them the way you want to your end user. All the power of the Excel object model is available, including database access, charts and graphs, and custom dialogs and controls.
Adding a Reference in the VBA Editor
To use the new object-oriented API in VBA, you must first add a reference to the type library for the Analytic Solver Platform COM server. To do this:
- With Excel open, press Alt-F11 to open the VBA Editor.
- Select menu choice Tools -> References.
- Scroll down until you find Analytic Solver Platform 2017 Type Library.
- Check the box next to this entry, and click OK to close the dialog.
- Use File Save to save your workbook.
Note that this is a different reference from Solver, which is the reference you add in order to use the “traditional” VBA functions.
Example VBA Code
The example VBA Code below shows how a user could use the Object Oriented API to define a new Problem, reset the Task Pane Platform, Engine, and Model Tabs, add variables, constraints, and an objective, setup evaluator functions to monitor the progress of the Solver, and finally solve the model.
Please open the attached model, InventDLL using VBA for KB.xlsm. The model that we will be solving has already been added to the Task Pane Model Tab. This model is nonlinear with 4 variables, 2 constraints, and 8 variable bounds. Please take a moment to become familiar with this model before proceeding with the steps below.
1. Open Microsoft Visual Basic for Applications - If using Office 2007/2010 click Developer > Visual Basic to open Microsoft Visual Basic. If the Developer tab does not appear on the Excel Ribbon, click the Microsoft Office Button (located in the upper left hand corner) > Excel Options > Popular and check "Show Developer Tab in the Ribbon".
If using Excel 2003, click Tools > Macro > Visual Basic Editor
Inside the VBA window, click Insert > Module, then define a new Analytic Solver Platform problem (prob) and instantiate it from the spreadsheet using the two lines of code below.
Dim prob As New RSP.Problem
prob.Init ActiveSheet
You can use the Init method to instantiate the Problem from a named model or worksheet – this will create all of the Variable and Function objects for the problem defined in that model or worksheet. (If you don’t use Init, the Problem is instantiated from the active worksheet.) At this point, if your model has already been setup in the Task Pane Model Tab, you can proceed directly to Step 10 to solve the model. In this example, we clear the model from the task pane to illustrate how to setup and define a model from the very beginning.
2. Clear Previous Model and Reset Parameter Defaults Next we clear the previous model in the Task Pane Model Tab and reset all option values to their defaults in the Task Pane Platform and Engine Tabs.
prob.Variables.Clear
prob.Functions.Clear
prob.Engine.ParamReset
prob.Model.ParamReset
3. Turn off Guided Mode To bypass the Guided Mode Solver dialogs, the Guided Mode parameter is set to False.
prob.Model.Params("GuidedMode").Value = False
4. Set the Initial Starting Point The model appearing in this article is a nonlinear model that will be solved using the Standard LSGRG Nonlinear Engine in Analytic Solver Platform, Risk Solver Platform or Premium Solver Platform, or the Nonlinear GRG engine in Premium Solver Pro. Since the final solution found by a nonlinear engine can depend on the initial starting point, the four lines of code below set the variable values in cells C13:F13 to 50. Therefore, each time this macro is run, the initial starting point will always be the same.
Cells(13, 3).Value = 50
Cells(13, 4).Value = 50
Cells(13, 5).Value = 50
Cells(13, 6).Value = 50
5. Defining the Decision Variables Next we define our decision variables in cells C13:F13, set them as Integers and add lower and upper bounds of .001 and 100, respectively. A lower bound of .001 is added to prohibit Solver from setting the variable values to 0 which would cause a division by 0 in cells C15:F15. Since the GRG engine performs better with both upper and lower variable bounds, an upper bound of 100 is also added.
Dim vars As New RSP.Variable
vars.Init "C13:F13"
vars.VariableType = Variable_Type_Decision
vars.IntegerType.Array = Integer_Type_Integer
vars.LowerBound.Array = 0.001
vars.UpperBound.Array = 100
prob.Variables.Add vars
6. Defining Constraints Next, we add the two constraints: G17 <= H17 (Cost of Products <= Budget) and G16 <= H16 (Space Used <= Space Available). When adding each constraint, we first define the left hand side of the function as con1 (G17) or con2 (G16), designate the function as a constraint (Function_Type_Constraint), define the right hand sides of each function (H17 and H16, respectively), then add each function to the collection with prob.Function.Add.
Dim con1 As New RSP.Function
con1.Init "G17"
con1.FunctionType = Function_Type_Constraint
con1.UpperBound.Array = "H17"
prob.Functions.Add con1
Dim con2 As New RSP.Function
con2.Init "G16"
con2.FunctionType = Function_Type_Constraint
con2.UpperBound.Array = "H16"
prob.Functions.Add con2
7. Defining the Objective Function Finally, we define the objective function in cell G15 to be minimized.
Dim obj As New RSP.Function
obj.Init "G15"
prob.Solver.SolverType = Solver_Type_Minimize
obj.FunctionType = Function_Type_Objective
prob.Functions.Add obj
8. Progress Evaluator In order to monitor the progress of Solver during the solution process, we set up an evaluator to be called on every iteration. Evaluators are implemented in Analytic Solver Platform using an event handler which must be defined within a Class. In VBA, a Class must reside in a Class module. Here, we create the evals class which will reside inside the Class1 class module.
Dim evals As New Class1
Next, we create the MonitorProgress evaluator (Set evals.MonitorProgress = New RSP.Evaluator) and finally assign the evaluator type (Eval_Type_Iteration). There are several different types of evaluators supported by Analytic Solver Platform, Risk Solver Platform, Premium Solver Platform and Premium Solver Pro. For more information on the various types of evaluators supported, please see the article, "Using a Callback Function when calling the RSP through VBA".
Set evals.MonitorProgress = New RSP.Evaluator
prob.Evaluators.Item(Eval_Type_Iteration) = evals.MonitorProgress
9. Monitor Progress Evaluator The MonitorProgress evaluator of type Eval_Type_Iteration must be written inside the Class1 VBA Class Module as mentioned in Step 8 above. To add the Class1 VBA Class Module, click Insert > Class Module.
Before we write our evaluator, we must declare a custom event handler. We declare this event handler directly underneath Option Explicit as shown below.
Option Explicit
Public WithEvents MonitorProgress As RSP.Evaluator
Once this line of code is in place, we can click the down arrow next to General and select MonitorProgress from the menu. VBA will automatically create our nonlinear evaluator, MonitorProgress_Evaluate. Inside this evaluator, you can monitor the progress of Solver by checking the iteration number, the objective, constraint or variable values, etc. In the example below, we check the number of iterations, along with the objective function value. (Since over 150 iterations are performed in this model, we’ve chosen to display the objective on every 10th iteration using the VBA modulus (Mod) operator in the line of code, If Evaluator.Problem.Engine.stat.Iterations Mod 10 = 0. This specifies that if the number of iterations is divisible by 10, then display the number of iterations and the current objective function.) Finally, we tell Solver to continue optimizing by returning Engine_Action_Continue. (Important Note: If Number of Threads is set to 0 or >1 on a multiple core machine running a permanent license, the message box inside the evaluator will cause an error in V12.x. This issue will be addressed in the next version of Analytic Solver Platform. In the meantime, everything inside the evaluator must be thread safe.)
There is one additional Engine_Action symbolic constant, Engine_Action_Stop, which stops the solution process immediately if returned.
Public Function MonitorProgress_Evaluate(ByVal Evaluator As RSP.IEvaluator) As RSP.Engine_Action
If Evaluator.Problem.Engine.stat.Iterations Mod 10 = 0 Then
MsgBox "Iteration Number = " & Evaluator.Problem.Engine.stat.Iterations & vbCrLf & _
"Objective = " & Evaluator.Problem.FcnObjective.Value.Item(0)
End If
MonitorProgress_Evaluate = Engine_Action_Continue
End Function
10. Choose Solver Engine and Solve the Model Back inside the main program in Module1, we choose the Standard LSGRG Nonlinear engine (Nonlinear GRG engine if using Premium Solver Pro) to solve the nonlinear model and solve the problem.
Analytic Solver Platform, Risk Solver Platform, Premium Solver Platform and Premium Solver Pro offer additional Solver engines to solve models of different types. For example, if solving a linear model, the LP/Quadratic engine is available in Risk Solver Platform or Premium Solver Platform, and the LP Simplex engine is available in Premium Solver Pro. To confirm your model type, click back to Excel and then click Optimize > Analyze without Solving on the Solver ribbon or menu. The model will be diagnosed and the problem type will be reported on the bottom half of the Task Pane Model Tab.
prob.Engine = prob.Engines(prob.Engine.GRGName)
prob.Solver.optimize Solve_Type_Solve
When the solution process ends, the property OptimizeStatus will return an integer value that indicates the stopping condition. Analytic Solver Platform, Risk Solver Platform, Premium Solver Platform and Premium Solver Pro return values from –1 to 21. When the Interval Global Solver or field-installable Solver engines are used, Analytic Solver Platform may return engine-specific values for custom stopping conditions, starting at 1000. See the table below for descriptions of each. In this example, we simply stop and advise the user to check the Task Pane Output Tab for the Final Result Message, then turn Guided Mode back on before the macro finishes.
If prob.Solver.OptimizeStatus = 0 Then
MsgBox "Finished! Please see the Task Pane Output Tab for the Final Result Message and your Excel sheet for the final variable values."
Else
MsgBox "Problem with Solve! Please see the Task Pane Output Tab for the Final Result Message."
End If
prob.Model.Params("GuidedMode").Value = True
The Analytic Solver Platform Reference Guide includes a chapter explaining the Analytic Solver Platform Object Model in more detail. Please refer to this guide for more detailed information on calling Analytic Solver Platform, Risk Solver Platform, Premium Solver Platform and Premium Solver Pro through VBA.
Value |
Stopping Condition* |
0 |
Solver found a solution. All constraints and optimality conditions are satisfied. |
1 |
Solver has converged to the current solution. All constraints are satisfied. |
2 |
Solver cannot improve the current solution. All constraints are satisfied. |
3 |
Stop chosen when the maximum iteration limit was reached. |
4 |
The Set Cell values do not converge. |
5 |
Solver could not find a feasible solution. |
6 |
Solver stopped at user's request. |
7 |
The linearity conditions required by this Solver engine are not satisfied. |
8 |
The problem is too large for Solver to handle. |
9 |
Solver encountered an error value in a target or constraint cell. |
10 |
Stop chosen when the maximum time limit has been reached. |
11 |
There is not enough memory available to solve the problem. |
13 |
Error in model. Please verify that all cells and constraints are valid. |
14 |
Solver found an integer solution within tolerance. All constraints are satisfied. |
15 |
Stop chosen when the maximum number of feasible (integer) solutions was reached. |
16 |
Stop chosen when the maximum number of feasible (integer) subproblems was reached. |
17 |
Solver converged in probability to a global solution. |
18 |
All variables must have both upper and lower bounds. |
19 |
Variable bounds conflict in binary or alldifferent constraint. |
20 |
Lower and upper bounds on variables allow no feasible solution. |
21 |
Solver encountered an error computing derivatives. |
1000 |
Interval Global Solver requires Solve with Automatic and strictly smooth functions. |
1001 |
Function cannot be evaluated for given real or interval arguments. |
1002 |
Solution found, but not proven globally optimal. |
*For more information on each stopping condition, please refer to the chapter, "Solver Result Messages", in the Analytic Solver Platform Reference Guide.
Comments
0 comments
Please sign in to leave a comment.