How to continuously update cell values during iterations while running Solver from macro

Hi

I'm writing a macro which uses the Excel Solver add-in, and I want to see the development of the variables in the excel sheet during the iterations (they are linked to graphs).

I can make this work by running the solver manually and tick off the “Show iteration results”-option, but then I need to close the dialogue box for each iteration. Is there a way to update the results through a macro, and disable the dialogue box at the same time?

Perhaps something like this?

To test rum macro "SolvLoop" but before you do so you need to set a reference to Solver in Visual Basic.

"Developer" tab -> "Visual Basic" -> 2Tools" -> "References" and tick box marked "Solver"

Alf

Thanks for the reply Alf, but I only have to solve one optimization problem. And I want to see how the decision variables develops throughout the optimization routine.

I want to see how the decision variables develops throughout the optimization routine

Hmm interesting question. Perhaps one could set max time to a small value that should stop solver and showing the decision variable at that time, copy values reset Solver, paste back copied values and kickstart sover for another run. How to get rid of the pop up screen (Solver reach max time limit bla bla bla) I dont know really. I've in a similar situation tried to send "Alt s" as pressing "Alt + s" will "delete" the pop up screen. But it does not seem that this command works inside the solver macro

Please Login or Register  to view this content.

One could also play around a bit with max number of iterations this depends a bit on the complexety of the problem you wish solver to solve myself I've found it easier to adjust number of seconds.

I also think you should contact the makers of Solver and ask for their advice. Their support department is quite helpfull at least with problems regarding the Excel 2010 Solver.

http://www.solver.com/

If they do give you a solution could you please post it in this thread as this is rather usefull knowledge.

Alf

Thanks again, I've also tried to change both iteration numbers and time limit, but neither seems to work. It's like the solver get stuck on the same solution for every iteration.

I have, however found a solution on how to get rid of the pop up. When I launch the Solver i use:

Please Login or Register  to view this content.

where Results is a figure discribing the status of the solution. The important bit here is "ShowTrial" which is a userdefined function that tells what to do with the pop up, or so I believe. Any way, it works for this purpose.

Please Login or Register  to view this content.

Hi

I contacted the support departmen at Frontline Systems and this if what they told me:

Basically, we define a function that gets called whenever the maxtime limit is reached, and the action taken depends on the return value of this function:

Please Login or Register  to view this content.

So, here, in the subroutine "myfb", I set maxtime = 9 and solve the problem by calling SolverSolve (I pass on UserFinish = true to not display solver solution dialog, and set ShowRef equal to the function "showTRial"); the function showTRial will get called when maxtime is reached, and since this just returns 0, it will stop the solution process without displaying the dialog.

This is partly what you already found out. How to incorporate this in a loop? And how to capture to the partly finished "solution" and then let solver continue solving the problem? I do wonder could you upload the file that you are working with or if it contains sensitive information post a mock up file?

Alf

I've attached the optimization problem

???? You sure you did that as I can't find it. Lost in cyber space probably.

Alf

Sorry, there was something wrong with the file I attached initially, so I had to remove it. Will be right back

Here it is

Ok got it. Will have a go at it and see if I can do anything at all.

Alf