How to run Solver while viewing a graph? (not the sheet the solver problem is located in)

[holditall]

Problem: How to run Solver while viewing a graph?

(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…
If someone feels that he could help me I would most appreciated!
Thank you much,
Axel

 

[Domski]

Hi,

I've not ever tried to do what you are but looking at the Frontline Systems web site (Solver developers) it looks like you can control the solver dialogue using VBA: http://www.solver.com/suppstdvba.htm

Might be worth registering and seeing if any of their white papers point you in the right direction.

Dom

 

[holditall]

Hmm. I had been browsing this page, but could not find any white papers. Do you have a link?

Best Axel

 

[Domski]

I did say you had to register...right hand side of the page...To Learn More...

Dom

 

[Domski]

You might want to use the macro recorder. From the web site:

Using the Macro Recorder

If you want to set up the Solver model "from scratch" programmatically, one easy way to see how to use the Solver functions is to turn on the Macro Recorder (Tools Macro Record New Macro...) and then set up a Solver model interactively. Microsoft Excel will record a macro in VBA which calls the Solver functions to mimic the actions you perform. You can then edit and customize this macro, and incorporate it into your application.
____________________________________________________________

Looking at the VBA Help in Excel it looks like you can use the SolverOK function to kick off Solver without displaying the dialogue. You may well then be able to display your graph while it's doing it's stuff.

As I said I've never tried this and my experience with Solver is limited but hope it points you in the right direction. Sorry can't help more.

Dom

 

[Domski]

From private message:

I really appreciate your help, but I tried this before.. Macro Recorder does not record anything after the Solver is started, so thiis will not help.
It seems like the ScreenUpdate=False Application is for some reason dissabled in the Solver Function...

So, to my surprise it seems difficult to realise in my case...

a

Bit surprised about that. I just set up a quick solver problem on a new workbook and the macro recorder gave me the following code:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 30/12/2008 by 20004521
'
'
    SolverOk SetCell:="$C$6", MaxMinVal:=3, ValueOf:="118", ByChange:="$B$1:$B$5"
    SolverSolve
End Sub

Don't know if it makes any difference but what version of Excel are you using?

Dom

 

[holditall]

As I wrote: _after_ the solver ist startet. E.g. If you press any buttons during the "Reached Numbers of Maximum itterations.." alerts. etc. Setting up the Solver problem is recorded.

Anyhow this does not do anything about my problem.

Thanks A

 

[Domski]

So are you already using SolverOK to kick the solver off? Application.ScreenUpdating will not disable the appearance of messages. You can use:

Application.DisplayAlerts = False

...to prevent warning messages appearing. Make sure you turn them back on though!

If this still doesn't help maybe post a more complete version of your code.

Dom

 

[holditall]

Here you go, anyhow, neither
Application.EnableEvents = False
Application.DisplayAlerts = False
ScreenUpdating = False

do the job. Application.DisplayAlerts = False does not work with solver, Solver (true) does only toggle off the final MsgBox.

Still your answers have nothing to do with my actual problem,

Best Axel






Sub FitAllNonZerosMacro()
Windows("3__peak search V16.xls").Activate
Sheets("Fitting").Activate
ByChangeValues = ""
SolverReset
ScreenUpdating = False

' Load ChangeValues for all non Zero, non Formula containing cells
For x = 1 To 10
For y = 1 To 3
If Cells(y + 6, x + 2) > 0 Then
If Cells(y + 6, x + 2).HasFormula = False Then
ByChangeValues = ByChangeValues & Cells(y + 6, x + 2).Address & ","
End If
End If
Next y
Next x
For x = 1 To 3
y = 4
If Cells(y + 6, x + 2) > 0 Then
If Cells(y + 6, x + 2).HasFormula = False Then
ByChangeValues = ByChangeValues & Cells(y + 6, x + 2).Address & ","
End If
End If
Next x
For x = 1 To 2
y = 5
If Cells(y + 6, x + 2) > 0 Then
If Cells(y + 6, x + 2).HasFormula = False Then
ByChangeValues = ByChangeValues & Cells(y + 6, x + 2).Address & ","
End If
End If
Next x
x = 1
y = 6
If Cells(y + 6, x + 2) > 0 Then
If Cells(y + 6, x + 2).HasFormula = False Then
ByChangeValues = ByChangeValues & Cells(y + 6, x + 2).Address & ","
End If
End If

' cut off that crazy last "," grrrr....
lengthofthatidiote = Len(ByChangeValues)
ByChangeValues = Mid(ByChangeValues, 1, lengthofthatidiote - 1)


SolverOk SetCell:="$J$14", MaxMinVal:=2, ValueOf:="0", ByChange:=ByChangeValues



Application.EnableCancelKey = xlErrorHandler




' Enter lowerlimits
For x = 1 To 10
For y = 1 To 3
SolverAdd CellRef:=Cells(y + 6, x + 2).Address, Relation:=3, FormulaText:=Cells(y + 18, x + 2).Address
Next y
Next x
For x = 1 To 3
y = 4
SolverAdd CellRef:=Cells(y + 6, x + 2).Address, Relation:=3, FormulaText:=Cells(y + 18, x + 2).Address
Next x
For x = 1 To 2
y = 5
SolverAdd CellRef:=Cells(y + 6, x + 2).Address, Relation:=3, FormulaText:=Cells(y + 18, x + 2).Address
Next x
x = 1
y = 6
SolverAdd CellRef:=Cells(y + 6, x + 2).Address, Relation:=3, FormulaText:=Cells(y + 18, x + 2).Address
'Enter uperlimits
For x = 1 To 10
For y = 2 To 3
SolverAdd CellRef:=Cells(y + 6, x + 2).Address, Relation:=1, FormulaText:=Cells(y + 26, x + 2).Address
Next y
Next x
For x = 1 To 3
y = 4
SolverAdd CellRef:=Cells(y + 6, x + 2).Address, Relation:=1, FormulaText:=Cells(y + 26, x + 2).Address
Next x
For x = 1 To 2
y = 5
SolverAdd CellRef:=Cells(y + 6, x + 2).Address, Relation:=1, FormulaText:=Cells(y + 26, x + 2).Address
Next x
x = 1
y = 6
SolverAdd CellRef:=Cells(y + 6, x + 2).Address, Relation:=1, FormulaText:=Cells(y + 26, x + 2).Address


SolverAdd CellRef:="$C$13", Relation:=1, FormulaText:="$P$22"
SolverAdd CellRef:="$C$13", Relation:=3, FormulaText:="$P$23"

SolverOptions MaxTime:=10000, Iterations:=10000, Precision:=0.0000001

Application.EnableEvents = False
Application.DisplayAlerts = False


ScreenUpdating = True
Sheets("Main Fit Chart").Activate
ScreenUpdating = False
Sheets("Fitting").Select
SolverOptions (StepThru = False)
SolverSolve Userfinish:=True
SolverFinish KeepFinal:=1


End Sub