Run time error - "out of stack space"

[Tempy]

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

 

[Rob Bovey]

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]

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