Trigger Excel Solver without VBA

[murthy_v_s]

Hi all:

I am fairly new to programming and I wanted to know if there is a way to trigger excel solver with out excel VBA code.
Brief Problem Outline: The values in column ?X? of my worksheet are my constraints which I want to vary and see how different projects (with Max NPV objective) get selected. All this is to do sensitivity analysis.

Thanks.

[Factor3]

Just to clarify, you DON'T want to use solver via VBA, you want to manually access it via Excel Right?

That being the case, Go to:
Tools > Add-Ins...

Under Add-Ins there should be a check box next to "Solver", click the box so that there is a check mark there, and then click "OK".

The Solver is now loaded. To utilize the Solver, go under Tools> Solver, and there you can set your goals & constraints (i.e. "target cell" equal to cell with ending NPV, check the button next to Max, and in the box that says "By Changing Cells:" and then list the dependent variables you want to include in your sensitivity analysis... you can also include constraints so that dependent variable cells don't go below a certain level, or above a certain level, etc.).

[murthy_v_s]

Thanks for getting back to me. I have framed my question incorrectly. What I meant was to trigger solver automatically (either by VBA macro or User Defined Function) whenever the constraints are changed. I was more interested in doing this process by user defined function.

Regards.

[Zack Barresse]

Cross-Post: http://www.theofficeexperts.com/foru...ead.php?t=7626

Please read: http://www.excelguru.ca/node/7

[JonPeltier]

What I meant was to trigger solver automatically (either by VBA macro or User Defined Function) whenever the constraints are changed. I was more interested in doing this process by user defined function.

Rather than a UDF, which will in general not change anything but the cell containing the UDF, use a Worksheet_Change event to fire Solver.

First turn on the macro recorder while you run Solver on the existing constraints, to record the Solver code. Then set up a Worksheet_Change event which detects whether one of the constraints is the changed cell. If so, have it run the Solver code.