How to run Solver while viewing a graph?

[axel]

(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

 

[Jon Peltier]

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

- Jon

 

[axel]

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

Any other ideas?

Best Axel

 

[Leith Ross]

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

 

[Jon Peltier]

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

- Jon

 

[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

 

[Dana DeLouis]

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]

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]

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]

Thanks again!

I am still extremely puzzeled because my solver macro behaves very strangely
indeed.

Sometime everything works quite fine, including the "live update" and
sometimes the macro still does it's job, but no output whatsoever is
displayed. (As if screenupdate was toggled off, which is not the case and as
you pointed out pointless in the solver model anyhow).

I run exactley the same macro without changing a line, and sometimes it
works sometime it doesnot...

On top sometime it just proceedes regardless what I am doing in other
windows (eg. an odd Internet explorer Window) sometimes it works until i
click on anything (outside Excel). Then it cancels and displays an error
message: "No Return() or Halt) dunction found on macro sheet.


Again the revisded code:


Sub FitAllNonZerosMacro()
starttime = Time

Windows("3__Spectrum_Fitter.xls").Activate
Sheets("Fitting").Activate
ByChangeValues = ""

SolverReset

loadminiumrestraints
loadmaximumrestraints
LoadChangeValuesforallnonZero


SolverOptions MaxTime:=16000, Iterations:=25000, Precision:=0.00000001,
IntTolerance:=0.00000001
SolverOptions StepThru:=True
SolverOptions Scaling:=True


Range("T:V").ClearContents
Cells(2, 21) = Time
Cells(3, 20) = 0
answer = SolverSolve(True, "ShowTrial")
SolverFinish KeepFinal:=1

End Sub



Function ShowTrial(Reason As Integer)
ShowTrial = False
Iterationcounter = Cells(3, 20)
Iterationcounter = Iterationcounter + 1
Cells(3, 20) = Iterationcounter
Cells(3 + Iterationcounter, 22) = Range("J13")
Cells(3 + Iterationcounter, 21) = Time - Cells(2, 21)
End Function



I guess the Solver model as good as it might be is just poorly implemented
in Excel. (I Run Excel 2003 SP3 with Windows Vista)

Maybe there is a hotfix or Update for the Solver somewhere? (Could not find
it)

Happy new year!

Axel

 

[Dana DeLouis]

Hi. Just some ideas. Since we don't actually see it listed in your
code directly, does

LoadChangeValuesforallnonZero

actually use the SolverOk function?

SolverOk SetCell:="A1", MaxMinVal:=3, ValueOf:="0", ByChange:="B1"

The reason I question it is that if you are "Loading" it from a saved
location, the other restraints would have already been loaded with the
same command.

One technique is to pause the macro just before the SolverSolve command.
Then, switch to your spreadsheet, and pull up Solver. Is your Solver
form filled in properly at this point?


For now, I would try to debug part of the program with something like
the following.

Debug.Print "= = = = = = = = = = "
Answer = SolverSolve(True, "ShowTrial")
Select Case Answer
Case 0 To 2
Debug.Print "Answer ok: " & answer
SolverFinish KeepFinal:=1
Case Else
Debug.Print "Answer NOT ok:" & answer
End Select
Debug.Print "= = = = = = = = = = "
Debug.Print

I would include a debug here as well...

Function ShowTrial(Reason As Integer)
Debug.Print "ShowTrial: " & Reason

Sometime everything works quite fine...

From experience, this can sometimes be a flag that you are using
discontinuous functions in your spreadsheet model.
Is your model using functions like Max(), Min(), IF(), Abs(), etc?
If you are, you will have to re-write the problem to avoid these functions.

- - -
HTH :>)
Dana DeLouis

 

[xydata]

Hi fellow Excel gurus,

I have a few spreadsheets that get that same error "No Return() or Halt
().... blah blah" I've searched for years trying to solve it and
mostly find references to the autosave being on, however my
spreadsheets did not have that option on. Today, I oddly enough
started commenting out certain macros, saving and reopening to see if
the error comes up. The commented macros that finally stopped the
error were:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Cancel = True
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
As Boolean)
Cancel = True
End Sub

I switched to the "ThisWorkbook" options for the same macros and all
is well. Go figure!

Hope it sticks!