Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error - "out of stack space"
Good day,
I have the following code, which works fine but gives me the "out of stack space error". Not being a programmer i am not sure how to solve this problem. Could somebody please heeeeeelp. Sub OnKTL() Do Until ActiveCell.Offset(0, -7) = "" ' No more LCS part numbers If ActiveCell.Offset(0, -4) = "" Then ' Is Part on the KTL ActiveCell.Offset(1, 0).Select ' No - Move 1 cell down" Else If ActiveCell.Offset(0, 6) = "" Then ' Is there a GPS PO ? ActiveCell = "No purchase order" ActiveCell.Offset(1, 0).Select OnKTL Else If ActiveCell.Offset(0, 7) = "" Then ' Is there a SAP PO ? If ActiveCell.Offset(0, -5) < ActiveCell.Offset(0, -3) Then ActiveCell = "LCS AI lower than PO AI" ActiveCell.Offset(1, 0).Select OnKTL Else If ActiveCell.Offset(0, -5) ActiveCell.Offset(0, -3) Then ActiveCell = "PO AI lower than LCS AI" ActiveCell.Offset(1, 0).Select OnKTL Else If ActiveCell.Offset(0, -5) = ActiveCell.Offset(0, -3) Then ' Are the AI's the same ActiveCell = "OK" ActiveCell.Offset(1, 0).Select OnKTL End If End If End If Else If ActiveCell.Offset(0, -5) = ActiveCell.Offset(0, -3) Then ' Are the AI's the same ActiveCell = "OK" ActiveCell.Offset(1, 0).Select OnKTL Else If ActiveCell.Offset(0, -5) < ActiveCell.Offset(0, -3) Then ActiveCell = "LCS AI lower than PO AI" ActiveCell.Offset(1, 0).Select OnKTL Else If ActiveCell.Offset(0, -5) ActiveCell.Offset(0, -3) Then ActiveCell = "PO AI lower than LCS AI" ActiveCell.Offset(1, 0).Select OnKTL Else If ActiveCell.Offset(0, -5) = ActiveCell.Offset(0, -3) Then ' Are the AI's the same ActiveCell = "OK" ActiveCell.Offset(1, 0).Select OnKTL End If End If End If End If End If End If End If Loop ActiveWorkbook.Save Message End Sub Tempy *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error - "out of stack space"
Hi Tempy,
The problem is that you've written a recursive procedure that calls itself more times than VBA will allow. That's the easy part to answer. The difficult question is why your recursion gets out of control. Unfortunately there's no simple answer to that. You simply have to step through the procedure line by line as it's running and try to figure out why it continues to recurse when it should come back out. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "Tempy" wrote in message ... Good day, I have the following code, which works fine but gives me the "out of stack space error". Not being a programmer i am not sure how to solve this problem. Could somebody please heeeeeelp. Sub OnKTL() Do Until ActiveCell.Offset(0, -7) = "" ' No more LCS part numbers If ActiveCell.Offset(0, -4) = "" Then ' Is Part on the KTL ActiveCell.Offset(1, 0).Select ' No - Move 1 cell down" Else If ActiveCell.Offset(0, 6) = "" Then ' Is there a GPS PO ? ActiveCell = "No purchase order" ActiveCell.Offset(1, 0).Select OnKTL Else If ActiveCell.Offset(0, 7) = "" Then ' Is there a SAP PO ? If ActiveCell.Offset(0, -5) < ActiveCell.Offset(0, -3) Then ActiveCell = "LCS AI lower than PO AI" ActiveCell.Offset(1, 0).Select OnKTL Else If ActiveCell.Offset(0, -5) ActiveCell.Offset(0, -3) Then ActiveCell = "PO AI lower than LCS AI" ActiveCell.Offset(1, 0).Select OnKTL Else If ActiveCell.Offset(0, -5) = ActiveCell.Offset(0, -3) Then ' Are the AI's the same ActiveCell = "OK" ActiveCell.Offset(1, 0).Select OnKTL End If End If End If Else If ActiveCell.Offset(0, -5) = ActiveCell.Offset(0, -3) Then ' Are the AI's the same ActiveCell = "OK" ActiveCell.Offset(1, 0).Select OnKTL Else If ActiveCell.Offset(0, -5) < ActiveCell.Offset(0, -3) Then ActiveCell = "LCS AI lower than PO AI" ActiveCell.Offset(1, 0).Select OnKTL Else If ActiveCell.Offset(0, -5) ActiveCell.Offset(0, -3) Then ActiveCell = "PO AI lower than LCS AI" ActiveCell.Offset(1, 0).Select OnKTL Else If ActiveCell.Offset(0, -5) = ActiveCell.Offset(0, -3) Then ' Are the AI's the same ActiveCell = "OK" ActiveCell.Offset(1, 0).Select OnKTL End If End If End If End If End If End If End If Loop ActiveWorkbook.Save Message End Sub Tempy *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error - "out of stack space"
Hello Rob,
Thanks for your answer, but as i said i am not a programmer could you perhaps explain it in laymans terms ? This procedure has to loop down about 4000 lines and i get the error at about 1500 lines. Is there not perhaps a way to "reset" the procedure after a certain amount of loops or perhaps another way that i am not aware of ? Tempy *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Out of Stack space - run time error 28 | Excel Discussion (Misc queries) | |||
"Out of Stack Space" Macro Error | Excel Discussion (Misc queries) | |||
how do I type "itis" without Excel putting a space "it is"? | Excel Worksheet Functions | |||
Run time error 28: Out of stack space | Excel Programming | |||
Run Time Error "28" - Out of stack space? | Excel Programming |