Using Excel Solver with VBA

Excel Solver XLAM-addin by Frontline is a handy tool for small optimization problems. In the case you did not know yet, it is also possible to use Solver in your VBA program. In this post, I am opening one possible way to implement Solver functionality to be used in VBA program. As a practical example, we will use our Solver program to perform a curve-fitting routine. If you need some refresh in this topic, you can check the following links to get some overview: http://en.wikipedia.org/wiki/Curve_fitting (fitting lines and polynomial curves to data points) and http://en.wikipedia.org/wiki/Least_squares 

Solver functions

However, let us first get some familiarity with the most important Solver methods needed in our example Solver program:
 
SolverReset -  Resets all cell selections, constraints and restores all the settings to their defaults.

SolverOk - Defines a Solver model. SetCell: this single cell reference is our objective function. MaxMinVal: integer value of 1 (maximize), 2 (minimize) or 3 (value of). ValueOf: if MaxMinVal is 3, we specify the value to which the objective function value is matched. ByChange: this cell/cells reference is our changing model variable(s).

SolverOptions - Allows you to specify advanced options for Solver model. Every existing setting in Solver Options subwindow can be configured within this function. Just for an example, we configure non-negativity settings. AssumeNonNeg: True, if the lower limit for decision variable(s) need to be zero. False, if negative values for decision variable(s) are allowed.

SolverSolve - Begins a Solver solution run. UserFinish: True to return the results without displaying the Solver Results dialog box. False or omitted to return the results and display the Solver Results dialog box.

More information about Solver functions can be read from here http://msdn.microsoft.com/en-us/library/office/jj945113.aspx

Curve fitting model

We are now ready for the actual problem. Set up the following data into Excel worksheet.

maturity	rate	estimate	error		coefficients
0,08	0,19	0	0,04		a	0,0000
0,25	0,27	0	0,08		b1	0,0000
0,5	0,29	0	0,09		b2	0,0000
1	0,35	0	0,12
2	0,51	0	0,26		errors	29,6609
5	1,38	0	1,91
10	2,46	0	6,07
20	3,17	0	10,07
30	3,32	0	11,04

Let us go through this data first. First we have some actual swap curve data in the first two columns (maturity, rate). In the third column, we have 2nd degree polynomial function rate estimate, calculated by using coefficients a, b1 and b2. In the fourth column we have the squared difference of actual rate and our estimated rate. in the cell errors, we have the sum of error column values.

Calculation formulas 

rate estimate r by using 2nd degree polynomial function:
r = a + b1 * (maturity) + b2 * (maturity * maturity) 

estimate error:
(rate estimate - actual rate) * (rate estimate - actual rate)
 
The cell errors (initial value of 29.6609 when all coefficients are zero) is our objective function, since it sums all model errors. Since we want the error between actual curve and our to-be-estimated curve to be as small as possible, this is minimization problem. Coefficient values should also have an option to have negative values and hereby, we set our non-negativity constraint to be false. Coefficients a, b1 and b2 are our changing variables in this model. When I run my own Solver model, I will get the following results. (If you run this example model on your own in Excel, there is some differences due to decimal roundings in my example):

maturity	rate	estimate	error		coefficients
0,08	0,19	0,166955	0,00		a	0,1446
0,25	0,27	0,211408	0,00		b1	0,2685
0,5	0,29	0,277517	0,00		b2	-0,0055
1	0,35	0,407683	0,00
2	0,51	0,659804	0,02		errors	0,0904
5	1,38	1,350482	0,00
10	2,46	2,282667	0,03
20	3,17	3,325987	0,02
30	3,32	3,274573	0,00

Program

Now, let us talk for a moment about the program and what do we want to have with it. Maybe the thing what you want right now is just to have a simple Solver routine to do some rough minimization. In that case, just use macro recorder and get yourself one. Personally, I always try to look for a design, what would be the most flexible and easily extendable for other similar tasks. Here is one possible solution example below. Remember to create reference to Solver (VB editor - Tools - References - Solver).

First we create ISolver interface in a Standard VBA Class Module (name = ISolver). This is interface, what all possible Solvers must implement. It has only one function - solve - what takes in parameter wrapper (parameters inside Dictionary data structure). If you are unfamiliar with this, check my posting http://mikejuniperhill.blogspot.fi/2013/05/handling-parameters-dynamically-with.html

Option Explicit
'
' interface to be implemented
Public Function solve(ByRef parameters As Scripting.Dictionary)
End Function
'

At this point, we could set up Solver addin (VB editor - tools - references - Solver) and Microsoft Scripting Library (VB editor - tools - references - Microsoft Scripting Runtime). Next, we create implementation for our interface. Since we need only unconstrained optimization model without non-negativity (or any other) constraints, we create Solver just for this simple purpose. It should be noted, that this Solver needs only 6 external parameters inside parameter wrapper (objective function, maxMinVal, valueOf, changingVariables, assumeNonNegative and userFinish). For some other, maybe bit more complicated Solver, the model could need more parameters. In that case, it would be easy to set all needed parameters inside parameter wrapper. Anyway, create the following implementation in a Standard VBA Class Module (name = Optimization_unconstrained).

Option Explicit
'
Implements ISolver
'
' implementation for ISolver interface
' solver for unconstrained optimization problems
Private Function ISolver_solve(ByRef parameters As Scripting.IDictionary)
    '
    Solver.SolverReset
    Solver.SolverOk _
        parameters.Item(PRM.objectiveFunction), _
        parameters.Item(PRM.maxMinVal), _
        parameters.Item(PRM.valueOf), _
        parameters.Item(PRM.changingVariables)
    '
    Solver.SolverOptions AssumeNonNeg:=parameters.Item(PRM.assumeNonNegative)
    Solver.SolverSolve parameters.Item(PRM.userFinish)
    Solver.SolverReset
End Function
'

Next, we need to set up Enumerator needed for our parameter wrapper. Create the following enumerator in a Standard VBA Module. Note, that this Enumerator is public for ALL modules and classes.

Option Explicit
'
' enumerator needed for parameter wrapper
Public Enum PRM
    '
    objectiveFunction = 1
    maxMinVal = 2
    valueOf = 3
    changingVariables = 4
    userFinish = 5
    assumeNonNegative = 6
End Enum
'

Finally, we set up our tester program in a Standard VBA Module. As can be seen in the program below, my objective function cell (sum of model errors) is H7 and my changing variables cells (coefficients a, b1, b2) are H3:H5. We give the addresses of these to our parameter wrapper. MaxMinVal are given in as an integer. UserFinish and Non-negativity assumption are given in as booleans.

Option Explicit
'
Public Sub tester()
    '
    ' define Excel ranges for objective function and variables
    Dim objectiveFunctionRange As Range: Set objectiveFunctionRange = Sheets(2).Range("H7")
    Dim changingVariablesRange As Range: Set changingVariablesRange = Sheets(2).Range("H3:H5")
    '
    ' create parameter wrapper and fill it
    Dim parameters As New Scripting.Dictionary
    '
    ' for objective function and variables, address of range is needed
    parameters.Add PRM.objectiveFunction, CStr(objectiveFunctionRange.Address)
    parameters.Add PRM.changingVariables, CStr(changingVariablesRange.Address)
    parameters.Add PRM.maxMinVal, CInt(2)
    parameters.Add PRM.userFinish, CBool(True)
    parameters.Add PRM.assumeNonNegative, CBool(False)
    '
    ' create solver model - in this case we need unconstrained optimization
    Dim xlSolver As ISolver: Set xlSolver = New Optimization_unconstrained
    xlSolver.solve parameters
    '
    ' release objects
    Set xlSolver = Nothing
    Set parameters = Nothing
End Sub
'

Afterthoughts

Time for some afterthoughts. The purpose of this posting was to show, how we could implement Solver routines in VBA. What do we achieved? I think we got quite flexible model. Now, if we ever need to make our model more complicated, we could write a new Solver implementation and plug it into our existing design instead of copy-pasting and replacing a lot of code. Pattern-wise, this example Solver design can be used in Strategy Design Pattern. Moreover, you can naturally re-use your created Solver design in some other VBA project.

Solver by Frontline for Excel is truly a great thing. However, there has always been one serious downer, at least for me: you always need to link your program with concrete ranges in Excel worksheet, because Solver itself operates directly with these ranges. I mean that you can not have everything happening inside your program. One such a tool (maybe not so well-known) what I have been woodshedding a bit, is Microsoft Solver Foundation. If you are interested to learn more about it, check out one of my recent posting about it http://mikejuniperhill.blogspot.fi/2013/06/using-ms-solver-foundation-and-c-in.html

I hope again that you have gained something new for yourself. Have a nice evening and great June there.
-Mike