premium solver for excel

[rad_melb]

Has anybody used premium solver for Excel (I am using V7.1 by frontline systems)? If you have, you will be familiar with the input box called 'by changing variable cells'

My question:
Is it possible to run a macro each time one of the 'by changing variable cells' changes in value. The macro ultimately affect the 'Set cell' (e.g. the cell you are trying to maximize) and hence it is essential that I have the macro in there.

Thanks..

 

[Jon Peltier]

Use a Worksheet_Change event procedure to detect the change to the cells, and from this procedure call the code that runs Solver.

 

[rad_melb]

thanks jon. I have never used a worksheet change event procedure .. but I will look into it.

My macro takes a few minutes to run. Since I am using solver along with the macro and the change event procedure I'm guesing it will take a very long time to solve?

 

[Jon Peltier]

How are these cells changing value? If it's a user changing tham, Worksheet_Change will do it. If they change because a formula updates, then Worksheet_Calculate does it. Of course, if these are cells that Solver changes, they won't hold formulas.

You will want to make sure the macro doesn't fire continuously as the cells are updated, so put this before

Application.EnableEvents = False

and this after the body of your solver procedure

Application.EnableEvents = True