Calling Analytic Solver through VBA

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:

1. With Excel open, press Alt-F11 to open the VBA Editor.
2. Select menu choice Tools -> References.
3. Scroll down until you find Analytic Solver Platform 2018 Type Library.
4. Check the box next to this entry, and click OK to close the dialog.
5. 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.  

 *For more information on each stopping condition, please refer to the chapter, "Solver Result Messages", in the Analytic Solver Platform Reference Guide. 

• Invent_DLL using VBA for KB.xls