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 we

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!


On Jan 2, 6:08*am, Dana DeLouis wrote:
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



axel wrote:
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- Hide quoted text -

- Show quoted text -