Solver Macro (2002)

Topic

New Reply

I ran Excel’s Solver and set a target cell, the constraints and the variable cells, and it all ran perfectly.

However, I would now like users to be able to change the variable cells and for the Solver to recalculate automatically.
I don’t want users to have to type and re-input all the constraints etc.

Does anyone know of vba code to do this.

Thanks

Robert

Reply | Quote

Replies

Calculating a solution uses a lot of resources, so I don’t think it is a good idea to let the Solver recalculate automatically each time one of the input cells is changed. Instead, I’d do the following:
– Set a reference to the SOLVER in Tools | References… in the Visual Basic Editor. If you’ve already set up the Solver, the references should be near the top of the list, so you only need to tick its check box and click OK.
– Create the following simple macro:

Sub SolveIt
SolverSolve True
End Sub

– Create a command button from the Forms toolbar on the worksheet and assign the SolveIt macro to it.

The user can click the command button after having changed one or more input cells.

Reply | Quote

I would never have thought to reference the Solver Add-in
That works perfectly. Thanks as always, Hans.

Regards

Robert

Reply | Quote