(windows XP, Excel 2003)



The outset:
I am running a complex solver problem (typically takes a couple of minutes
to hours) the results are then fed into the 20 or so cells solver may change.
These cells compute a graph in the same workbook, but on a different sheet.
(The presets of the Solver are fed in by a macro)
So far everything goes along fine.

Basically what I want to do is: Display the Graph while Solver is active (to
judge the progress he is making)

I tried many variants all of them failed:

First approach:

Sheets("Main Fit Chart").Activate
SolverSolve


This results in a runtime Error €œ1004€� Method €˜Range of object €˜_Global
failed. It would not be very helpful anyhow since Excel does not update the
cell values while Solver is running..



My second attempt was to create a loop like this one:

SolverOptions MaxTime:=10000, Iterations:=2, Precision:=0.0000001
€˜ Reducing the number of Iterations to 2 and doing this for a 1000 times:
For x = 1 To 1000
ScreenUpdating = True
Sheets("Main Fit Chart").Activate
ScreenUpdating = False
Sheets("Fitting").Select
SolverSolve
Next x



But surprisingly Solver seems more powerful than the ScreenUpdating = False
command, hence the graph is only displayed for a millisecond than the fitting
screen is magically activated and solver does his mystiques€¦

Thank you much,

Axel

Embed the chart on the sheet that hosts the Solver problem.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"axel" wrote in message
...
(windows XP, Excel 2003)



The outset:
I am running a complex solver problem (typically takes a couple of minutes
to hours) the results are then fed into the 20 or so cells solver may
change.
These cells compute a graph in the same workbook, but on a different
sheet.
(The presets of the Solver are fed in by a macro)
So far everything goes along fine.

Basically what I want to do is: Display the Graph while Solver is active
(to
judge the progress he is making)

I tried many variants all of them failed:

First approach:

Sheets("Main Fit Chart").Activate
SolverSolve


This results in a runtime Error "1004" Method 'Range' of object '_Global'
failed. It would not be very helpful anyhow since Excel does not update
the
cell values while Solver is running..



My second attempt was to create a loop like this one:

SolverOptions MaxTime:=10000, Iterations:=2, Precision:=0.0000001
' Reducing the number of Iterations to 2 and doing this for a 1000 times:
For x = 1 To 1000
ScreenUpdating = True
Sheets("Main Fit Chart").Activate
ScreenUpdating = False
Sheets("Fitting").Select
SolverSolve
Next x



But surprisingly Solver seems more powerful than the ScreenUpdating =
False
command, hence the graph is only displayed for a millisecond than the
fitting
screen is magically activated and solver does his mystiques.

Thank you much,

Axel

I knew this works but this is not possible in my case!

Any other ideas?

Best Axel


"Jon Peltier" wrote:

Embed the chart on the sheet that hosts the Solver problem.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"axel" wrote in message
...
(windows XP, Excel 2003)



The outset:
I am running a complex solver problem (typically takes a couple of minutes
to hours) the results are then fed into the 20 or so cells solver may
change.
These cells compute a graph in the same workbook, but on a different
sheet.
(The presets of the Solver are fed in by a macro)
So far everything goes along fine.

Basically what I want to do is: Display the Graph while Solver is active
(to
judge the progress he is making)

I tried many variants all of them failed:

First approach:

Sheets("Main Fit Chart").Activate
SolverSolve


This results in a runtime Error "1004" Method 'Range' of object '_Global'
failed. It would not be very helpful anyhow since Excel does not update
the
cell values while Solver is running..



My second attempt was to create a loop like this one:

SolverOptions MaxTime:=10000, Iterations:=2, Precision:=0.0000001
' Reducing the number of Iterations to 2 and doing this for a 1000 times:
For x = 1 To 1000
ScreenUpdating = True
Sheets("Main Fit Chart").Activate
ScreenUpdating = False
Sheets("Fitting").Select
SolverSolve
Next x



But surprisingly Solver seems more powerful than the ScreenUpdating =
False
command, hence the graph is only displayed for a millisecond than the
fitting
screen is magically activated and solver does his mystiques.

Thank you much,

Axel

It's possible if you want it badly enough. Why can't you change the
worksheet?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"axel" wrote in message
...
I knew this works but this is not possible in my case!

Any other ideas?

Best Axel


"Jon Peltier" wrote:

Embed the chart on the sheet that hosts the Solver problem.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"axel" wrote in message
...
(windows XP, Excel 2003)



The outset:
I am running a complex solver problem (typically takes a couple of
minutes
to hours) the results are then fed into the 20 or so cells solver may
change.
These cells compute a graph in the same workbook, but on a different
sheet.
(The presets of the Solver are fed in by a macro)
So far everything goes along fine.

Basically what I want to do is: Display the Graph while Solver is
active
(to
judge the progress he is making)

I tried many variants all of them failed:

First approach:

Sheets("Main Fit Chart").Activate
SolverSolve


This results in a runtime Error "1004" Method 'Range' of object
'_Global'
failed. It would not be very helpful anyhow since Excel does not update
the
cell values while Solver is running..



My second attempt was to create a loop like this one:

SolverOptions MaxTime:=10000, Iterations:=2, Precision:=0.0000001
' Reducing the number of Iterations to 2 and doing this for a 1000
times:
For x = 1 To 1000
ScreenUpdating = True
Sheets("Main Fit Chart").Activate
ScreenUpdating = False
Sheets("Fitting").Select
SolverSolve
Next x



But surprisingly Solver seems more powerful than the ScreenUpdating =
False
command, hence the graph is only displayed for a millisecond than the
fitting
screen is magically activated and solver does his mystiques.

Thank you much,

Axel

The Solver routine might be very clever mathematically but the rest is badly
scripted.
Ok I tried to implement it in the Worksheet.. still than i have to use the:

SolverOptions StepThru:=True

answer = SolverSolve(True, "ShowTrial")

Function ShowTrial(Reason As Integer)
ShowTrial = True
ScreenUpdating = True

End Function


To get him fill in the values trick. First of all there is a bug in the
solver function which does not allow filenames with spaces to be handled
(which was the case). After finding that out, still the function above does
not work. It simply stops after the first itteration.

Puzzeled..

A





"Jon Peltier" wrote:

It's possible if you want it badly enough. Why can't you change the
worksheet?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"axel" wrote in message
...
I knew this works but this is not possible in my case!

Any other ideas?

Best Axel


"Jon Peltier" wrote:

Embed the chart on the sheet that hosts the Solver problem.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"axel" wrote in message
...
(windows XP, Excel 2003)



The outset:
I am running a complex solver problem (typically takes a couple of
minutes
to hours) the results are then fed into the 20 or so cells solver may
change.
These cells compute a graph in the same workbook, but on a different
sheet.
(The presets of the Solver are fed in by a macro)
So far everything goes along fine.

Basically what I want to do is: Display the Graph while Solver is
active
(to
judge the progress he is making)

I tried many variants all of them failed:

First approach:

Sheets("Main Fit Chart").Activate
SolverSolve


This results in a runtime Error "1004" Method 'Range' of object
'_Global'
failed. It would not be very helpful anyhow since Excel does not update
the
cell values while Solver is running..



My second attempt was to create a loop like this one:

SolverOptions MaxTime:=10000, Iterations:=2, Precision:=0.0000001
' Reducing the number of Iterations to 2 and doing this for a 1000
times:
For x = 1 To 1000
ScreenUpdating = True
Sheets("Main Fit Chart").Activate
ScreenUpdating = False
Sheets("Fitting").Select
SolverSolve
Next x



But surprisingly Solver seems more powerful than the ScreenUpdating =
False
command, hence the graph is only displayed for a millisecond than the
fitting
screen is magically activated and solver does his mystiques.

Thank you much,

Axel

still the function above does not work.


Hi. Depending on your Version, try setting the return value to False.
Yes...it's another bug.

ScreenUpdating = True

With Solver, there is no need to try to adjust ScreenUpdating.
Solver turns it back on anyway, as it needs it on to work.


Function ShowTrial(Reason As Integer)
'Do Stuff...
ShowTrial = False
End Function


HTH
Dana DeLouis



axel wrote:
The Solver routine might be very clever mathematically but the rest is badly
scripted.
Ok I tried to implement it in the Worksheet.. still than i have to use the:

SolverOptions StepThru:=True

answer = SolverSolve(True, "ShowTrial")

Function ShowTrial(Reason As Integer)
ShowTrial = True
ScreenUpdating = True

End Function


To get him fill in the values trick. First of all there is a bug in the
solver function which does not allow filenames with spaces to be handled
(which was the case). After finding that out, still the function above does
not work. It simply stops after the first itteration.

Puzzeled..

A





"Jon Peltier" wrote:

It's possible if you want it badly enough. Why can't you change the
worksheet?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"axel" wrote in message
...
I knew this works but this is not possible in my case!

Any other ideas?

Best Axel


"Jon Peltier" wrote:

Embed the chart on the sheet that hosts the Solver problem.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"axel" wrote in message
...
(windows XP, Excel 2003)



The outset:
I am running a complex solver problem (typically takes a couple of
minutes
to hours) the results are then fed into the 20 or so cells solver may
change.
These cells compute a graph in the same workbook, but on a different
sheet.
(The presets of the Solver are fed in by a macro)
So far everything goes along fine.

Basically what I want to do is: Display the Graph while Solver is
active
(to
judge the progress he is making)

I tried many variants all of them failed:

First approach:

Sheets("Main Fit Chart").Activate
SolverSolve


This results in a runtime Error "1004" Method 'Range' of object
'_Global'
failed. It would not be very helpful anyhow since Excel does not update
the
cell values while Solver is running..



My second attempt was to create a loop like this one:

SolverOptions MaxTime:=10000, Iterations:=2, Precision:=0.0000001
' Reducing the number of Iterations to 2 and doing this for a 1000
times:
For x = 1 To 1000
ScreenUpdating = True
Sheets("Main Fit Chart").Activate
ScreenUpdating = False
Sheets("Fitting").Select
SolverSolve
Next x



But surprisingly Solver seems more powerful than the ScreenUpdating =
False
command, hence the graph is only displayed for a millisecond than the
fitting
screen is magically activated and solver does his mystiques.

Thank you much,

Axel

Hello Axel,

You cross-posted this question yesterday on the ExcelFourm site...

'Problem: How to run Excel Solver while viewing a graph?'
(http://tinyurl.com/9obtrs)

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45112