Using Excel's Solver with VBA macros

This page shows how to control Excel's Solver using VBA macros, and you can download a sample VBA application, that uses Solver.

You name it. Solver has many uses, and if it is a recurrent task, it can be a big advantage to make an application with VBA macros. An application can also be used by others that don't know much about Solver.

I work at a cement plant and have made an application for raw mix proportioning, i.e. how much to use of different raw materials and fuels to get the desired quality. You can download a much simpler version here.

The workbook is zip compressed. To unzip, right-click and select "decompress all" or whatever Windows suggests.

The workbook was made in Excel 2003, so you probably need to change the references to Solver. See below: Installing Solver.

Besides macros to control Solver there are also examples on how to use Ranges, Arrays and UserForms with ListBoxes, ComboBoxes, TextBoxes, OptionButtons and CommandButtons.

To ensure that TextBoxes get numeric values only, there is a modified version of the class module described on this page. The user controls the application with a right-click popup menu.

Installing Solver

Solver is not installed automatically, when you install Excel - it is an option. To use Solver it must be referenced as an AddIn in Excel's "spreadsheet part," and if you want to use it with VBA, it must also be referenced here.

The VBA reference is set from "References" in the VBA editor's Tools menu. In Excel 2003 it looks like this:

If Solver is not on the list, you have to click "Browse" and find the folder "Solver" buried as a subdirectory to "Microsoft Office" in the Programs folder.

Click on "File types" and select Excel files (xls + xla or xlsm + xlam). Select "solver.xla", or "solver.xlam" if you use Excel 2007 or newer.

Make a model

Before you use Solver, you must make a model with one or more target or objective cells and one or more adjustable cells, that will influence the values of the target cells.

Here is a very simple example. Type "2" in cell A2. Type "=A1+A2" in cell A3 and start Solver from the menu.

Make A3 your target cell, the value must be 4 by adjusting cell A1. In the Danish version of Excel 2003 it looks like this:

Click "Solve", and Solver inserts 2 in cell A1. You don't need to have a target cell - you could also make "A3 = 4" a constraint.

Not surprisingly Solver is normally used for more complex stuff, and when it starts smelling of equations with several unknowns, it pays off to make Solver your friend.

Beware of If functions in cells - if there are too many nested IFs, Solver may suffer from indigestion.

I strongly recommend that you make a model and test it by using Solver manually, before you use VBA. If it doesn't work using Solver manually, it will not work with VBA.

Calling Solver using VBA

When you call Solver using a macro, you need to do several things, just like when you use it manually. Solver must know the target cell, the adjustable cells, the ranges with constraints, and you can also give Solver orders about solution method, solution precision etc.

Let's start by looking at the pieces one by one, and at the end we'll put them together in a complete VBA procedure.

Constraints

When you call Solver using VBA it is smart to have your constraints in sets of 2 columns. For instance cell A1 to A8 can contain references to cells that must be less than or equal to values, cell references or formulas in column B1 to B8.

For instance cell A1 can have the formula "= M27", and then the value of cell M7 must be less than or equal to cell B1.

For obvious reasons M27 must have a formula that will change the cell value, if one or more adjustable cells change.


'< = constraints
If bRel1 Then
   solveradd cellref:=rA1.Address, _
             relation:=1, formulatext:=rB1.Address
End If

'Equal to (=) constraints
If bRel2 Then
   solveradd cellref:=rA2.Address, _
             relation:=2, formulatext:=rB2.Address
End If

'> = constraints
If bRel3 Then
   solveradd cellref:=rA3.Address, _
             relation:=3, formulatext:=rB3.Address
End If

The key element here is "relation:=1" (and 2 and 3) that tells Solver, what kind of constraint we are dealing with ("< =", "=" or "> =").

"bRel1", "bRel2" and "bRel3" are flags of the data type Boolean that I have set = True, if there are any constraints of the said type.

rA1, rB1 etc. are range variables and could for instance be A1:A8 and B1:B8. By using ranges and ".Address", my columns with constraints can have a dynamic number of rows. You can also write cell addresses separated by commas, but I think the dynamic range is better.

There is nothing to keep you from having more column sets with the same relation (1, 2 or 3), as long as each set has a line as above.

Just before I call Solver, I delete any old constraints and insert those that are active. Typically I store constraints in arrays, and then it is very fast to insert them like this, where rA1 is a range and arARel1 is my array:

Set rA1 = Range("A1")
'Resize rA1 to the same number of rows as in the Array
Set rA1 = rA1.Resize(Ubound(arARel1))
'Copy the array to the range
rA1.Formula = arARel1

Adjustable cells

In the command to Solver this is done with cell addresses separated by commas, but if the cells can change from time to time, you can use a String variable that stores the addresses (e.g.: "A27,A28,A29,F30").

The command line can look as follows, where "sAdjust" is a String variable with the adjustable cells' addresses, and "sTarget" is a String variable with the address of the target cell. "dTargetValue" is my variable for the exact target value.

Of course you can write a number instead of using a variable like dTargetValue, if the target value never changes.

"MaxMinVal:=3" is standard for finding an exact value. If changed from 3 to 1, Solver will maximize the value of the target cell, and "2" will minimize. Instead of the numbers 1, 2 or 3 it can be a variable that gets its value somewhere else.

SolverOk SetCell:=sTarget, MaxMinVal:=3, _
         ValueOf:=dTargetValue, ByChange:=sAdjust

Of course you must omit "ValueOf:=xxxx", if you maximize or minimize a target cell

Maybe you don't need or have a target cell, but you cannot do without the command line, because it contains the addresses of the adjustable cells.

In that case you can use a dummy. For instance you can write "1" in cell X65000, set X65000 as your target cell and write "ValueOf:=1".

There is nothing to stop you setting constraints for adjustable cells, I do that all the time - fixed values, relative values, intervals.

Solver settings

Just like when using Solver manually, VBA can change Solver's settings - max number of iterations, max time, precision etc. If you omit a parameter, Solver will just use the default value. Settings can look like this:

solverOptions MaxTime:=32760, Iterations:=32760, _
Precision:=0.0000001, AssumeLinear:=False, _
StepThru:=False, Estimates:=1, Derivatives:=1, _
SearchOption:=1, IntTolerance:=2, Scaling:=True, _
Convergence:=0.0001, AssumeNonNeg:=False
'Finally we call Solver:
SolverSolve(False)

The last line, "SolverSolve(False)", will make Solver pop up with the same dialogue you see, when you use it manually.

iSolution = SolverSolve(True)

Then Solver will return a value to your variable (here called "iSolution"), and from the value you can decide what to do next. You can see an example later.

You can change a lot of parameters, but I'll skip them - also because there can be differences from version to version.

The whole VBA procedure with the Solver call

We have now been through the most important elements in a Solver call, and it is time to see a procedure, where the elements are pieced together.

Some of the variables in the following procedure were declared on module level and got their values before we got to this point.

They are some of the flags (Boolean), ranges and the String variable containing the addresses of the adjustable cells.

Private Sub MixSolve()
'Defines the problem and calls Solver
Dim dPrecision As Double  'Variable for solution precision
Dim iSolution As Integer  'Solver's return value
Dim bSolved As Boolean    'Flag for solved or not
Dim lTimes As Long        'Counter
Dim sMsg As String        'Used in message box
Dim sTarget As String     'Variable for target cell address

On Error GoTo ErrorHandle

Application.ScreenUpdating = False
Worksheets(2).Activate

'It is important to reset Solver so it
'doesn't use old parameters.
SolverReset

'Here lRelation is a public variable,
'which got its value elsewhere.
'The value determines if the target
'cell should be maximized, minimized
'or have a specific value.
'1 = max, 2 = min, 3 = exact value.
If lRelation = 0 Then lRelation = 3
If lRelation = 3 Then
   sTarget = Range("pctsum").Address
Else
   sTarget = Range("price").Address
End If

'=< constraints
'bRel1, bRel2 and bRel3 are flags for
'relation 1, 2 or 3 constraints or not.
'The flags have been set True or false earlier.
'rA1, rB1, rA2, rB2 etc. are ranges,
'for for the sets of constraint columns.
If bRel1 Then
    solveradd cellref:=rA1.Address, _
              relation:=1, formulatext:=rB1.Address
End If

'Equal to (=) constraints
If bRel2 Then
    solveradd cellref:=rA2.Address, _
              relation:=2, formulatext:=rB2.Address
End If

'>= constraints
If bRel3 Then
    solveradd cellref:=rA3.Address, _
              relation:=3, formulatext:=rB3.Address
End If

'Chemical constraints
solveradd cellref:=rA4.Address, _
          relation:=2, formulatext:=rB4.Address

'lRelation 3 is standard
If lRelation = 3 Then
   'Target cell, target value and adjustable cells
   SolverOk SetCell:=sTarget, MaxMinVal:=3, _
         ValueOf:=dTargetValue, ByChange:=sAdjust
Else  'In this case: Minimize price (relation 2)
   SolverOk SetCell:=sTarget, MaxMinVal:=lRelation, ByChange:=sAdjust
End If

'The required precision. If Solver doesn,t find a solution,
'the requirement is made less strict up to 6 times.
dPrecision = 0.000000001

'Start the loop. We loop until there is a solution or we
'have tried 7 times.
Do Until bSolved
   lTimes = lTimes + 1
   If lTimes = 7 Then
      MsgBox "Solver didn't find a solution" & vbNewLine & _
      "despite having reduced demand for precision 6 times."      
      Exit Do
   End If
   'Parameters for Solver
   solverOptions MaxTime:=32760, Iterations:=32760, _
   Precision:=dPrecision, AssumeLinear:=False, _
   StepThru:=False, Estimates:=1, Derivatives:=1, _
   SearchOption:=1, IntTolerance:=2, Scaling:=True, _
   Convergence:=0.0001, AssumeNonNeg:=False
   'Call Solver.
   'The following line returns Solver's answer as
   'a value (integer), which we use for further
   'action. The alternative is to write:
   'SolverSolve(False), and the Solver's dialogue
   'will pop up, as if you ran Solver manually.
   iSolution = SolverSolve(True)
      
   Select Case iSolution
      Case 0
         bSolved = True
         If lTimes = 1 Then
            MsgBox "Solver found a solution."
         Else
            If lTimes = 2 Then
               sMsg = " time"
            Else
               sMsg = " times"
            End If
            MsgBox "Solver found a solution, when" & vbNewLine & _
            'demand for precision had been reduced" & vbNewLine & _
            lTimes - 1 & sMsg & "."
         End If
         Exit Do
      Case 1
         bSolved = True
         MsgBox "Solver has converged to the" & vbNewLine & _
         "current solution. Beware that the" & vbNewLine & _
         "it may not be the best solution."
         Exit Do
      Case 2
         bSolved = True
         MsgBox "Solver cannot improve the solution." & vbNewLine & _
         "All constraints are satisfied."
         Exit Do
      Case 3
         MsgBox "Stop chosen when the maximum"  & vbNewLine & _
         "iteration limit was reached."
         Exit Do
      Case 4
         MsgBox " The Objective Cell values" & vbNewLine & _
         "do not converge. You could try" & vbNewLine & _         
         "(another) constraint for flow" & vbNewLine & _         
         "or total production."
         Exit Do
      Case 5
         'Solver could not find a feasible
         'solution. We reduce demand for precision and try again."
         dPrecision = dPrecision * 10
      Case 6
         MsgBox "Solver stopped at user’s request."
         Exit Do
      Case 7
         'This should never happen, as we are
         'using a non linear model ...
         MsgBox "The linearity conditions required"  & vbNewLine & _
         "by this LP Solver are not satisfied."
         Exit Do
      Case 8
         MsgBox "The problem is too large."
         Exit Do
      Case 9
         MsgBox "Solver encountered an error value" & vbnewline _
         " in a target or constraint cell." & vbNewLine & _
         "At times it is necessary to close" & vbNewLine & _
         "and restart Excel, but it can also" & vbNewLine & _
         "be a chemical constraint that cannot" & vbNewLine & _
         "be satisfied thus causing division" & vbNewLine & _
         "by zero."
         Exit Do
      Case 10
         MsgBox "Maximum time limit was reached."
         Exit Do
      Case 11
         MsgBox "Not enough memory"
         Exit Do
      Case 12
         MsgBox "Solver.dll is used by another Excel program."
         Exit Do
      Case 13
         MsgBox "Error in model."
         Exit Do
   End Select
Loop

BeforeExit:
Application.ScreenUpdating = True

Exit Sub
ErrorHandle:
MsgBox Err.Description & " Procedure MixSolve, module SolverCode"
Resume BeforeExit
End Sub

The exercise is to mix raw materials to a desired chemical composition. It can also find the cheapest mix, that satisfies the given constraints.

(The prices in the spreadsheet are completely random numbers, that you can change as you please.)

Mathematically it is just weighted ratio calculation involving an equation with some unknowns.

Besides using Solver it uses Ranges, Arrays and UserForms for input. The user controls the application from a right-click popup menu.

It is just as taste of how to do raw mix proportioning using Solver with VBA macros, but the core functionality is OK and might serve as inspiration.

If you want more detailed information, you can find it at solver.com or Microsoft (links below).