Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when running Solver ("No return or halt function found...")
Hi,
When running the following Solver code, the error directly below comes up when I click on another application while Solver is running (ie - If I go to Internet Explorer while this code is running, after a few seconds, the error below comes up): "No return or halt function found on macro sheet" I then click on "OK", with is the only button within the error popup menu, and the code continues, seemingly without any damage being done. I did some research on this error, but only found an article saying that this error comes up in Excel2000 -- I, however, am using Excel2003. The article instructs that in order "To resolve this problem, obtain the latest service pack for Microsoft Office 2000". Since I'm using Excel2003, I'm assuming that the specific issue causing this error that this article refers to was resolved in Excel2003 and therefore an Excel2003 servicepack wouldn't address the issue I'm encountering -- is this a bad assumption? Any help is appreciated! Robert -- Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when running Solver ("No return or halt function found...")
hi, Robert !
I have some code using solver as the main tool to operate with, and... - when excel is the main window application, code finish in (say) 3 seconds - when excel is running in the background, code takes times 140 of its "normal" time (but NO error at all) you might want to revise your code for some of the solver options (maxtime, iterations, userfinish, etc.) hth, hector. __ OP __ When running the following Solver code, the error directly below comes up when I click on another application while Solver is running (ie - If I go to Internet Explorer while this code is running, after a few seconds, the error below comes up): "No return or halt function found on macro sheet" I then click on "OK", with is the only button within the error popup menu and the code continues, seemingly without any damage being done. I did some research on this error, but only found an article saying that this error comes up in Excel2000 -- I, however, am using Excel2003. The article instructs that in order "To resolve this problem obtain the latest service pack for Microsoft Office 2000". Since I'm using Excel2003 I'm assuming that the specific issue causing this error that this article refers to was resolved in Excel2003 and therefore an Excel2003 servicepack wouldn't address the issue I'm encountering -- is this a bad assumption? Any help is appreciated! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when running Solver ("No return or halt function found...")
I forgot the actual code in my original post -- I added it below (I don't
expect the actual code will help to determine what the issue is as I believe the issue has to do with simply selecting another app while the Solver code is running) ----------------------------- Hi, When running the following Solver code, the error directly below comes up when I click on another application while Solver is running (ie - If I go to Internet Explorer while this code is running, after a few seconds, the error below comes up): Error: "No return or halt function found on macro sheet" I then click on "OK", with is the only button within the error popup menu, and the code continues, seemingly without any damage being done. Actual Solver code: 'Set ranges to be used in Solver Sheets("Transaction Summary").Activate ..Range("L1").ClearContents 'Set commission range ActiveWorkbook.Names.Add Name:="payfclearcomm", _ RefersToR1C1:=.Range(.Range("L1").End(xlDown), .Range("L65536").End(xlUp)) 'Set binary range ActiveWorkbook.Names.Add Name:="payfclearbin", _ RefersToR1C1:=.Range(.Range("AA1").End(xlDown).Off set(0, 1), ..Range("AA65536").End(xlUp).Offset(0, 1)) 'Set month weight range ..Range("A1").End(xlDown).Offset(0, 28).Value = 1 If .Range("O1").End(xlDown).Offset(0, 12).Value < 1 Then With .Range(.Range("A1").End(xlDown).Offset(1, 28), ..Range("O1").End(xlDown).Offset(0, 12)) .Formula = _ "=IF(MONTH(RC[-16])<MONTH(R[-1]C[-16]),1+R[-1]C,R[-1]C)" .Formula = .Value End With Else End If ActiveWorkbook.Names.Add Name:="payfclearmonth", _ RefersToR1C1:=.Range(.Range("AA1").End(xlDown).Off set(0, 2), ..Range("AA65536").End(xlUp).Offset(0, 2)) 'Set sumif for Forward Balance total .Range("AD1").Formula = _ "=SUMIF(Linkpayholdclear!C[-23],Linkpayhold!R[1]C[-18],Linkpayholdclear!C[-28])" 'Set format of cell AD1 .Range("AD1").NumberFormat = "0.00000" 'Set commission sumproduct .Range("AE1").Formula = "=SUMPRODUCT(payfclearcomm,payfclearbin)" 'Set month weight sumproduct .Range("AF1").Formula = "=SUMPRODUCT(payfclearbin,payfclearmonth)" 'Solver execution Sheets("Transaction Summary").Activate SolverReset Application.DisplayAlerts = False SolverOk SetCell:="$AF$1", MaxMinVal:=2, ValueOf:="0", ByChange:="payfclearbin" SolverAdd CellRef:="payfclearbin", Relation:=5, FormulaText:="binary" SolverAdd CellRef:="$AE$1", Relation:=2, FormulaText:="$AD$1" SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _ :=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _ IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False SolverSolve userfinish:=True Application.DisplayAlerts = True I did some research on this error, but only found an article saying that this error comes up in Excel2000 -- I, however, am using Excel2003. The article instructs that in order "To resolve this problem, obtain the latest service pack for Microsoft Office 2000". Since I'm using Excel2003, I'm assuming that the specific issue causing this error that this article refers to was resolved in Excel2003 and therefore an Excel2003 servicepack wouldn't address the issue I'm encountering -- is this a bad assumption? Any help is appreciated! Robert -- Robert "robs3131" wrote: Hi, When running the following Solver code, the error directly below comes up when I click on another application while Solver is running (ie - If I go to Internet Explorer while this code is running, after a few seconds, the error below comes up): "No return or halt function found on macro sheet" I then click on "OK", with is the only button within the error popup menu, and the code continues, seemingly without any damage being done. I did some research on this error, but only found an article saying that this error comes up in Excel2000 -- I, however, am using Excel2003. The article instructs that in order "To resolve this problem, obtain the latest service pack for Microsoft Office 2000". Since I'm using Excel2003, I'm assuming that the specific issue causing this error that this article refers to was resolved in Excel2003 and therefore an Excel2003 servicepack wouldn't address the issue I'm encountering -- is this a bad assumption? Any help is appreciated! Robert -- Robert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when running Solver ("No return or halt function found..
Hi Dana,
I removed the "R1C1", but that didn't fix the error. What's wierd is that I just realized that as soon as I click on the button (which calls code that eventually calls the Solver code), if I then click on another application, Excel freezes up. I have no idea why this is happening. The code is lengthy, but it starts as follows - please let me know if you have any idea why this is happening. Private Sub transclearbyaff_Click() Dim cjmod As Integer Dim linkperfmod As Integer Dim strproceed As String Application.ScreenUpdating = False ThisWorkbook.Activate -- Robert "Dana DeLouis" wrote: RefersToR1C1:=.Range ...etc .Formula = "=IF(MONTH(RC[-16])<...etc Beginning with Excel 97, Solver needs the sheet to be in A1 Notation. As my initial guess, make sure you are not using R1C1 notation. You seem to have mixed them up, so this raises a little caution flag. -- HTH :) Dana DeLouis "robs3131" wrote in message ... I forgot the actual code in my original post -- I added it below (I don't expect the actual code will help to determine what the issue is as I believe the issue has to do with simply selecting another app while the Solver code is running) ----------------------------- Hi, When running the following Solver code, the error directly below comes up when I click on another application while Solver is running (ie - If I go to Internet Explorer while this code is running, after a few seconds, the error below comes up): Error: "No return or halt function found on macro sheet" I then click on "OK", with is the only button within the error popup menu, and the code continues, seemingly without any damage being done. Actual Solver code: 'Set ranges to be used in Solver Sheets("Transaction Summary").Activate .Range("L1").ClearContents 'Set commission range ActiveWorkbook.Names.Add Name:="payfclearcomm", _ RefersToR1C1:=.Range(.Range("L1").End(xlDown), .Range("L65536").End(xlUp)) 'Set binary range ActiveWorkbook.Names.Add Name:="payfclearbin", _ RefersToR1C1:=.Range(.Range("AA1").End(xlDown).Off set(0, 1), .Range("AA65536").End(xlUp).Offset(0, 1)) 'Set month weight range .Range("A1").End(xlDown).Offset(0, 28).Value = 1 If .Range("O1").End(xlDown).Offset(0, 12).Value < 1 Then With .Range(.Range("A1").End(xlDown).Offset(1, 28), .Range("O1").End(xlDown).Offset(0, 12)) .Formula = _ "=IF(MONTH(RC[-16])<MONTH(R[-1]C[-16]),1+R[-1]C,R[-1]C)" .Formula = .Value End With Else End If ActiveWorkbook.Names.Add Name:="payfclearmonth", _ RefersToR1C1:=.Range(.Range("AA1").End(xlDown).Off set(0, 2), .Range("AA65536").End(xlUp).Offset(0, 2)) 'Set sumif for Forward Balance total .Range("AD1").Formula = _ "=SUMIF(Linkpayholdclear!C[-23],Linkpayhold!R[1]C[-18],Linkpayholdclear!C[ -28])" 'Set format of cell AD1 .Range("AD1").NumberFormat = "0.00000" 'Set commission sumproduct .Range("AE1").Formula = "=SUMPRODUCT(payfclearcomm,payfclearbin)" 'Set month weight sumproduct .Range("AF1").Formula = "=SUMPRODUCT(payfclearbin,payfclearmonth)" 'Solver execution Sheets("Transaction Summary").Activate SolverReset Application.DisplayAlerts = False SolverOk SetCell:="$AF$1", MaxMinVal:=2, ValueOf:="0", ByChange:="payfclearbin" SolverAdd CellRef:="payfclearbin", Relation:=5, FormulaText:="binary" SolverAdd CellRef:="$AE$1", Relation:=2, FormulaText:="$AD$1" SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _ :=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _ IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False SolverSolve userfinish:=True Application.DisplayAlerts = True I did some research on this error, but only found an article saying that this error comes up in Excel2000 -- I, however, am using Excel2003. The article instructs that in order "To resolve this problem, obtain the latest service pack for Microsoft Office 2000". Since I'm using Excel2003, I'm assuming that the specific issue causing this error that this article refers to was resolved in Excel2003 and therefore an Excel2003 servicepack wouldn't address the issue I'm encountering -- is this a bad assumption? Any help is appreciated! Robert -- Robert "robs3131" wrote: Hi, When running the following Solver code, the error directly below comes up when I click on another application while Solver is running (ie - If I go to Internet Explorer while this code is running, after a few seconds, the error below comes up): "No return or halt function found on macro sheet" I then click on "OK", with is the only button within the error popup menu, and the code continues, seemingly without any damage being done. I did some research on this error, but only found an article saying that this error comes up in Excel2000 -- I, however, am using Excel2003. The article instructs that in order "To resolve this problem, obtain the latest service pack for Microsoft Office 2000". Since I'm using Excel2003, I'm assuming that the specific issue causing this error that this article refers to was resolved in Excel2003 and therefore an Excel2003 servicepack wouldn't address the issue I'm encountering -- is this a bad assumption? Any help is appreciated! Robert -- Robert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when running Solver ("No return or halt function found..
ActiveWorkbook.Names.Add Name:="payfclearbin", _
RefersToR1C1:=.Range(.Range("AA1").End(xlDown) ByChange:="payfclearbin" Hi. I'm having a problem with my news reader, so hopefully this isn't posted twice. These are probably not the issue, but I'll throw it out anyway. When you write code with .End(xlDown), there is no error check. Solver is limited to 200 changing cells. So, as it stands, one can not verify how many cells are set. Sheets("Transaction Summary").Activate Most likely not a problem here, but certain operations with solver will cause an error if the workbook, or worksheet name has a space in it. Try renaming your workbook/worksheet by removing any space characters. SolverAdd CellRef:="$AE$1", Relation:=2, FormulaText:="$AD$1" You are trying to set two complex functions equal to each other. In general, it is sometimes best to have a constant on the right hand side. Instead of f(x) = f(y) it can sometimes be better to re-write it as f(x) - f(y) = 0. SolverOk SetCell:="$AF$1", MaxMinVal:=2, ValueOf:="0", As a note, you can drop the "ValueOf" since it is ignored in a Min problem. Application.DisplayAlerts = False Try removing "Application.DisplayAlerts" for now while testing. Again, just some ideas. Good luck. -- HTH :) Dana DeLouis robs3131 wrote: Hi Dana, I removed the "R1C1", but that didn't fix the error. What's wierd is that I just realized that as soon as I click on the button (which calls code that eventually calls the Solver code), if I then click on another application, Excel freezes up. I have no idea why this is happening. The code is lengthy, but it starts as follows - please let me know if you have any idea why this is happening. Private Sub transclearbyaff_Click() Dim cjmod As Integer Dim linkperfmod As Integer Dim strproceed As String Application.ScreenUpdating = False ThisWorkbook.Activate |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when running Solver ("No return or halt function found..
Hi Rob, et al,
Has this problem been debugged (as I have exactly the same one with Excel 2003)? If I may, I also paste my code... Function Run_solver(target) Application.Calculation = xlManual If Not SOLVER.AutoOpened Then SOLVER.Auto_open SolverReset SolverOptions MaxTime:=120, Iterations:=32767, Precision:=0.0000001, _ AssumeLinear:=False, StepThru:=True, Estimates:=1, Derivatives:=1, _ SearchOption:=1, IntTolerance:=1, Scaling:=False, Convergence:=0.0001, _ AssumeNonNeg:=True SolverOk SetCell:="$G$5", MaxMinVal:=2, ValueOf:="0", ByChange:=Range("D2").Resize(Range("A1"), 1) ' A1 is less or equal to 100 SolverAdd CellRef:="$G$3", Relation:=2, FormulaText:=target SolverAdd CellRef:="$E$2", Relation:=2, FormulaText:="1" SolverAdd CellRef:="$G$5", Relation:=3, FormulaText:="0" Range("G2") = SolverSolve(True, showref:="SolverStepThru") Select Case Range("G2") Case 0, 1, 2 ' Solver found a solution SolverFinish 1 Case Else ' Keep results and move on, use 2 to keep original values SolverFinish 2 End Select End Function ----------------------------------------------- Function SolverStepThru(Reason As Integer) SolverStepThru = Reason 1 End Function "Dana DeLouis" wrote: ActiveWorkbook.Names.Add Name:="payfclearbin", _ RefersToR1C1:=.Range(.Range("AA1").End(xlDown) ByChange:="payfclearbin" Hi. I'm having a problem with my news reader, so hopefully this isn't posted twice. These are probably not the issue, but I'll throw it out anyway. When you write code with .End(xlDown), there is no error check. Solver is limited to 200 changing cells. So, as it stands, one can not verify how many cells are set. Sheets("Transaction Summary").Activate Most likely not a problem here, but certain operations with solver will cause an error if the workbook, or worksheet name has a space in it. Try renaming your workbook/worksheet by removing any space characters. SolverAdd CellRef:="$AE$1", Relation:=2, FormulaText:="$AD$1" You are trying to set two complex functions equal to each other. In general, it is sometimes best to have a constant on the right hand side. Instead of f(x) = f(y) it can sometimes be better to re-write it as f(x) - f(y) = 0. SolverOk SetCell:="$AF$1", MaxMinVal:=2, ValueOf:="0", As a note, you can drop the "ValueOf" since it is ignored in a Min problem. Application.DisplayAlerts = False Try removing "Application.DisplayAlerts" for now while testing. Again, just some ideas. Good luck. -- HTH :) Dana DeLouis robs3131 wrote: Hi Dana, I removed the "R1C1", but that didn't fix the error. What's wierd is that I just realized that as soon as I click on the button (which calls code that eventually calls the Solver code), if I then click on another application, Excel freezes up. I have no idea why this is happening. The code is lengthy, but it starts as follows - please let me know if you have any idea why this is happening. Private Sub transclearbyaff_Click() Dim cjmod As Integer Dim linkperfmod As Integer Dim strproceed As String Application.ScreenUpdating = False ThisWorkbook.Activate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"No RETURN() or HALT() function found on macro sheet." | Excel Worksheet Functions | |||
No RETURN() or HALT() function found on macro sheet | Excel Discussion (Misc queries) | |||
Beginner help! error no return() or halt() function found on macro sheet | Excel Programming | |||
"unexpected error" when running solver from a macro | Excel Programming | |||
"unexpected error" when running solver from a macro | Excel Programming |