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

Thanks for your help!

Yes ScreenupDate is unnecessary and Fals ist the right value for True (-;

But I am still not achieving what I want since... i does not run the fitting
procedure, but does not fill in the actual values in the excel Sheet after
each itteration. I suppose I have to ask Solver in the

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


"' Do Stuff" line to do so. But I cannot figure out how!

Thanks!

Axel



"Dana DeLouis" wrote:

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

Hi. I am not sure what the problem is.
But "in general" what you do in the routine is to copy your target cell
to some other location to view later.

I use Range Names a lot With Solver, and I usually use "Target" for the
Target Cell name.

(One technique is to set a public variable for the Row (R))

For example...

Function ShowTrial(Reason As Integer)
'Do Stuff, like...
'Copy Best Solution so far...

Cells(R,5) = [Target]
R = R + 1
ShowTrial = False
End Function


I believe the problem you were having earlier with the Chart is that
Solver will not work across multiple sheets.
Solver keeps track of the current sheet (Where the model/Solver is) so
that it doesn't get confused. It is always checking to make sure you
didn't change the active sheet.

HTH
Dana DeLouis



axel wrote:
Thanks for your help!

Yes ScreenupDate is unnecessary and Fals ist the right value for True (-;

But I am still not achieving what I want since... i does not run the fitting
procedure, but does not fill in the actual values in the excel Sheet after
each itteration. I suppose I have to ask Solver in the

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


"' Do Stuff" line to do so. But I cannot figure out how!

Thanks!

Axel



"Dana DeLouis" wrote:

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