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.
Excel's Solver is a strong tool to calculate and/or optimize complex models.

What buttons to press (how hard) to maximize profit?

How much ice cream to produce as a function of the weather forecast and day of the week?

How to mix raw materials to get a certain chemical composition at the lowest cost?
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, rightclick 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 rightclick 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
There are 3 types of value constraints in Solver, namely "< =", "=" and "> =".
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.
When calling Solver you could write:
If bRel1 Then
solveradd cellref:=rA1.Address, _
relation:=1, formulatext:=rB1.Address
End If
If bRel2 Then
solveradd cellref:=rA2.Address, _
relation:=2, formulatext:=rB2.Address
End If
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")
Set rA1 = rA1.Resize(Ubound(arARel1))
rA1.Formula = arARel1
The exercise is repeated for all other constraint columns.
Adjustable cells
Solver also needs to know the cells it can change to find a solution.
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
SolverSolve(False)
The last line, "SolverSolve(False)", will make Solver pop up with the same dialogue you see, when you use it manually.
As an alternative you can write:
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()
Dim dPrecision As Double
Dim iSolution As Integer
Dim bSolved As Boolean
Dim lTimes As Long
Dim sMsg As String
Dim sTarget As String
On Error GoTo ErrorHandle
Application.ScreenUpdating = False
Worksheets(2).Activate
SolverReset
If lRelation = 0 Then lRelation = 3
If lRelation = 3 Then
sTarget = Range("pctsum").Address
Else
sTarget = Range("price").Address
End If
If bRel1 Then
solveradd cellref:=rA1.Address, _
relation:=1, formulatext:=rB1.Address
End If
If bRel2 Then
solveradd cellref:=rA2.Address, _
relation:=2, formulatext:=rB2.Address
End If
If bRel3 Then
solveradd cellref:=rA3.Address, _
relation:=3, formulatext:=rB3.Address
End If
solveradd cellref:=rA4.Address, _
relation:=2, formulatext:=rB4.Address
If lRelation = 3 Then
SolverOk SetCell:=sTarget, MaxMinVal:=3, _
ValueOf:=dTargetValue, ByChange:=sAdjust
Else
SolverOk SetCell:=sTarget, MaxMinVal:=lRelation, ByChange:=sAdjust
End If
dPrecision = 0.000000001
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
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
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
dPrecision = dPrecision * 10
Case 6
MsgBox "Solver stopped at user’s request."
Exit Do
Case 7
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
If you download this zip compressed workbook, you can study a simple application that uses VBA and Solver:
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 rightclick 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).
External links:
