Home
Welcome to the SolverWrapper Wiki! Suggestions and contributions are welcome in this discussion here.
- Introduction
- Using the ActiveX DLL
- Using the VBA code library
- Example Test Code
- A Walkthrough Example
- Using the Enhanced Callback
- Using SolverWrapper Events
Appendix: Object Model Overview
The Solver Add-in (from FrontLine Systems) that comes installed with Microsoft Excel is a powerful tool for linear and non-linear spreadsheet model optimization. However, automating the Solver via VBA can be awkward due to Solver's cryptic "functional" design, and the requirement that the Add-in must be installed (activated) before a VBA reference can be made to it (see Peltier Tech for details).
This repo offers two compatible solutions for automating Solver via VBA. One consists of SolverWrapper object model in VBA code, and the other is an ActiveX DLL referenced from within your VBA projects. The DLL, compiled in twinBASIC, can either be installed/registered, or be called without registration if the use of IntelliSense and the Object Browser are not important.
These unique solutions control Solver by communicating directly with the SOLVER32.DLL, thus circumventing the SOLVER Add-in, and eliminating having to ensure that the Add-in has been loaded into Excel. Another advantage is that we can rewrite the user-DLL interface to add improvements such as a more flexible and easier to understand Object Model, an enhanced Solver callback protocol, and an event-model for monitoring optimization progress.
This repo includes a 32-/64-bit ActiveX DLL compiled in twinBASIC, as well as a corresponding Inno Installer for installing the ActiveX DLL on your system. Once installed, the SolverWrapper code library can be referenced by your Excel VBA projects to expose the SolverWrapper object model without having to manage the SolverWrapper source code.
In order to use the ActiveX DLL in your VBA projects, you must set a VBA reference to SolverWrapper. From the VBA IDE, select Tools>References and scroll down the list of references until you see SolverWrapper. Click to set the checkmark and then hit OK from the References dialog.
Also of note is that the DLL requires no dependencies (such as .Net Framework) and was designed so that it can be used (alternatively) without registration, if use of IntelliSense and the Object Browser are not important.
I have included the twinBASIC source and project files, and the Inno Setup source in case you would like to compile and install from scratch.
For more details on installation, see the ActiveX DLL FAQ in this Wiki.
As an alternative to referencing the ActiveX DLL, I have provided a VBA compatible code library. Import (not copy paste!) these into your VBA project. The VBA and DLL versions work exactly the same.
A set of example test code modules can be found in the test folder of the repo. These examples automate solving each of the optimization problems in SOLVSAMP.XLS, which is distributed with MS Office Excel and can be found programmatically in:
Application.LibraryPath & "\..\SAMPLES\SOLVSAMP.XLS"...which on many systems resolves to:
C:\Program Files\Microsoft Office\root\Office16\SAMPLES\SOLVSAMP.XLSImport these test modules into the sample Workbook above, load the SolverWrapper VBA source or set a reference to the SolverWrapper DLL and then save SOLVSAMP.XLS to SOLVSAMP.XLSM.
These examples should solve each problem without modification.
The following example automates solving the problem in SOLVSAMP.XLS (discussed above) 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 SubStep 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.
Just as with the Solver Add-in, SolverWrapper allows for monitoring the optimization progress through a callback function. However, SolverWrapper improves on this by not only passing the callback trigger reason, but also passing the trial number and the parent SolvProblem object for programming convenience. In order to activate the callback, one must set the following before running the SolveIt method:
'pass the name of the user-defined callback function
oProblem.Solver.UserCallbackMacroName = "ShowTrial" 'assumes ShowTrial function resides in ThisWorkbook
'oProblem.Solver.UserCallbackMacroName = ActiveWorkbook.Name & "!" & "ShowTrial" 'if not in ThisWorkbook
oProblem.SolveItThe following illustrates an example callback function. The function must adhere to the following input arguments, and must return a Boolean specifying whether or not to stop Solver.
Function ShowTrial(ByVal reason As Long, ByVal trialNum As Long, oProblem As SolvProblem) As Boolean
Dim i As Long
If trialNum = 1 Then Debug.Print "Solver started on Worksheet: " & oProblem.SolverSheet.Name
Debug.Print "Trial number: " & trialNum
Debug.Print "Objective: " & oProblem.Objective.CellRange.value
For i = 1 To oProblem.DecisionVars.Count
Debug.Print oProblem.DecisionVars.CellRange(i).Address, oProblem.DecisionVars.CellRange(i).value
Next i
Debug.Print "Constraints Satisfied? " & oProblem.Constraints.AreSatisfied
'decide whether to stop solver based on the reason for the event trigger
Select Case reason
Case SlvCallbackReason.slvShowIterations 'new iteration has completed or user hit esc key
stopSolver = False
Case SlvCallbackReason.slvMaxTimeLimit
stopSolver = True 'if set to True then solver is stopped!
Case SlvCallbackReason.slvMaxIterationsLimit
stopSolver = False
Case SlvCallbackReason.slvMaxSubproblemsLimit
stopSolver = False
Case SlvCallbackReason.slvMaxSolutionsLimit
stopSolver = False
End Select
ShowTrial = stopSolver
End FunctionAn arguably more powerful alternative to the callback function for monitoring optimization progress is through the use of SolverWrapper native events, albeit at the cost of a little more work. There are three native events: BeforeSolve which fires once before optimization begins, ShowTrial that fires on each trial solution, and AfterSolve which fires after optimization is completed. Below is an example of how to set up your own event class and connect that to SolverWrapper.
In order for SolverWrapper events to be triggered, the EnableEvents property of the SolvSolver class must be set to True, and a user-created event sink instantiated, as in the following example:
'enable SolverWrapper native events
oProblem.Solver.EnableEvents = True
If oProblem.Solver.EnableEvents Then
'connect-up user-created events processing class
Dim eventSink As SolverEventSink
Set eventSink = New SolverEventSink
Set eventSink.Problem = oProblem
End If
oProblem.SolveItIn order then to process the SolverWrapper events, the user should create their own event sink class, such as the example below:
'Class Name: SolverEventSink (user preference)
'@ModuleDescription "This is an example of a user-written class to process events triggered by the SolvProblem class."
'@folder("SolverWrapper.Examples")
'The EnableEvents property of the SolvSolver class must be set to True to activate SolverWrapper events
Private WithEvents oProblem As SolvProblem
Private Sub oProblem_ShowTrial(ByVal reason As Long, ByVal trialNum As Long, stopSolver As Boolean)
'this event is triggered for any one of the reasons below
Dim i As Long
Debug.Print "Trial number: " & trialNum
Debug.Print "Objective: " & oProblem.Objective.CellRange.value
For i = 1 To oProblem.DecisionVars.Count
Debug.Print oProblem.DecisionVars.CellRange(i).Address, oProblem.DecisionVars.CellRange(i).value
Next i
Debug.Print "Constraints Satisfied? " & oProblem.Constraints.AreSatisfied
'decide whether to stop solver based on the reason for the event trigger
Select Case reason
Case SlvCallbackReason.slvShowIterations 'new iteration has completed or user hit esc key
stopSolver = False
Case SlvCallbackReason.slvMaxTimeLimit
stopSolver = True 'if set to True then solver is stopped!
Case SlvCallbackReason.slvMaxIterationsLimit
stopSolver = False
Case SlvCallbackReason.slvMaxSubproblemsLimit
stopSolver = False
Case SlvCallbackReason.slvMaxSolutionsLimit
stopSolver = False
End Select
End Sub
Private Sub oProblem_BeforeSolve(stopSolver As Boolean)
Debug.Print "Solver started on Worksheet: " & oProblem.SolverSheet.Name
End Sub
Private Sub oProblem_AfterSolve(ByVal returnMsgCode As Long, ByVal trialNum As Long)
Debug.Print "Solver completed with iterations= " & trialNum
Debug.Print "Solver return code= " & oProblem.Solver.MsgCodeToString(returnMsgCode)
End Sub
Public Property Set Problem(obj As SolvProblem)
Set oProblem = obj
End Property
Private Sub Class_Terminate()
Set oProblem = Nothing
Debug.Print "terminating SolvEventSink"
End SubWhat are the system requirements for the SolverWrapper DLL?
64-bit MS Windows 32- or 64-bit MS Excel
Where should the SolverWrapper DLL be installed on my User Account?
This installer will install SolverWrapper in any location that you select. It is recommended that it be installed in C:\Users\[user name]\AppData\Local, which is the default location, in order to prevent the DLL file from being inadvertently moved or deleted after it has been registered. Moving or deleting the DLL after installation without first properly uninstalling will break any code that references the DLL, and render it unusable.
The installer will place a SolverWrapper shortcut on your Desktop to provide easy access to the installation folder, uninstaller file, readme instructions, and example resources.
What happens if I upgrade to a new version, or reinstall to a new location?
If upgrading to a new version, or re-installing with the same version but choosing a different install location, this installer will first unregister and then remove the older version.
How do I uninstall the SolverWrapper DLL from my system?
To uninstall SolverWrapper from your system, you can run the unins000.exe file in the installation directory. This unregisters the SolverWrapper DLL and removes all the original files from your system.
Once I have installed the SolverWrapper DLL, what is the quickest way to get started?
Use the SolverWrapper shortcut of your Desktop to navigate to the examples folder in the installation directory. Here you will find test modules that solve each of the optimization problems in the SOLVSAMP.XLS spreadsheet that comes with MS Excel (see readme file for more info). You can move or copy the example test modules (or the entire example directory) to another location on your system. But be sure NOT to move any other files, such as the DLL or the uninstaller file.
Open your copy of SOLVSAMP.XLS document and go to the Visual Basic Editor. Import each of test modules. Select Tools>References and scroll down the list of references until you see SolverWrapper. Click to set the checkmark and then hit OK from the References dialog. Save the file in a location of your choosing as a macro-enabled .xlsm file, and then run any of the test procedures. Once the file is saved, this step will not have to be repeated.