Thread: Solver

Hi,
I have to solve a problem by solver tool:
The height, width and length of a block are changing parameters and the aim is to minimize the volume. There are several constraints too.
Presently, I have to run the solver every time I change an entry data to check the effect on the results. Now, I want the solver to automatically solve the optimization problem and show the results every time I change any of the constraints.
Can anybody help me with this?
Thanks.

Perhaps you could use a macro like this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range

Set r = Range("E11:G11")

If Intersect(Target, r) Is Nothing Then Exit Sub

SolverReset

' Add your setup values for Solver problem

SolverSolve

Application.EnableEvents = True
End Sub

This macro asumes that your constraints are in cells E11, F11 and G11, change to suit your needs. Right click the sheet tag and click "View code" and incert code.

Don't forget to set a reference to Solver!

To reference the Microsoft Excel Solver add-in for macros in your workbook, use the following steps:
Open your workbook.
On the Tools menu, point to Macro, and then click Visual Basic Editor.
On the Tools menu, click References.
In the Available References list, click to select the Solver.xls check box, and then click OK.

Note If you do not see Solver.xls in the Available References list, click Browse. In the Add Reference dialog box, locate and select the Solver.xla file, and then click Open. The Solver.xla file is typically found in the C:\Program Files\Microsoft Office\Office\Library\Solver subfolder.
You are now ready to use the Microsoft Excel Solver functions in a VBA macro.

As you are using Excel 2010 the Solver.xla file may be in an other location.

HTH

Alf

Thank you very much Alf.
That will work. I decided to uses a bottom for running the macro.