Sub (NEXT) calls the sub (ETC), which calls sub (NEXT)
The subs just loop through a range of cells flagging errors.
All fine & dandy but occasionally I get a runtime 28 error, usually happens around the 100th loop.
I believe it is because of the recursive calling of subs. Any ideas on how to fix it?
Thanks in advance for your assistance.
without seeing any code, no ;-)
Josie
if at first you don't succeed try doing it the way your wife told you to
Please Login or Register to view this content.
remove the call to NEXT from ETC, or change do stuff to do other stuff
or if you'd like some actual help, post some actual code
As I said above, the code runs properly for at least 100 iterations, then it throws a runtime 28 error.
i.e. the problem is not with the do stuff code.
The problem is with the recursive calling, as I understand it.
How do you clear the "Call stack", if that is possible?
the only way to clear the call stack is to end your code. the rest of the code might have given some clue why you think you need to set up an infinite loop which will always lead to a stack space problem. you say it only happens occasionally which means that something must be exiting the loop but you have not shown us what
It is not an infinite loop.
There is a list of values in worksheet 1, usually 120
The sub Next gets the value of a cell in worksheet1 and finds the record related to that cell in worksheet 2.
The sub ETC checks the record from worksheet2 for errors (if it finds an error then it simply adds 1 to a counter x) , if no errors are found (x=0) it goes to the next value from the list in worksheet 1 and does exactly the same thing.
Even when I combine Next and ETC into a single sub I get the same runtime28 error, again , after the code runs through about 100 values.
EDIT: if it does find an error it exits the loop.
Please Login or Register to view this content.
Last edited by anrichards22; 09-17-2017 at 01:50 AM.
what little code you have posted is an infinite loop. since you don't seem to want to post the actual code I can't help you. good luck
As Joseph has said. Share your code with us by attaching the workbook and noting exactly how you start the macro running and which sheet is active when you start it.
Otherwise it's impossible to comment.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
May be you shall not call next in etc at all?
As ypu presented in post #3 the control is returned to next anyway after etc is finished.
So may be such approach would do the job:
Please Login or Register to view this content.
Please note that our https://www.excelforum.com/forum-rul...rum-rules.html require that even such code like:
shall be presented in code tagsPlease Login or Register to view this content.
So please edit your post #7 accordingly
Last edited by Kaper; 09-15-2017 at 09:44 AM.
Best Regards,
Kaper
Perhaps this will help: https://msdn.microsoft.com/en-us/lib...(v=vs.60).aspx The first bullet point makes specific mention of "recursive procedures" like you have here. They mention pulling up the Calls or the Call Stack dialog in debug mode to see how many procedure calls are on the stack. A thread over at stackoverflow suggested anywhere from 1000 to 5000 calls (seems machine/OS environment dependent) before getting the stack overflow error.
It seems to me that you may need to spend some time with your loop and see if you have it structured for efficient recursion. I don't see how we can help you with that process without seeing your code.
Perhaps these will give some considerations: https://docs.microsoft.com/en-us/dot...ive-procedures
http://www.cpearson.com/excel/recursiveprogramming.aspx
http://www.tushar-mehta.com/publish_..._recursion.htm
https://en.wikipedia.org/wiki/Recurs...puter_science)
Originally Posted by shg
As I explained above, it is NOT an infinite loop.
The sub NEXT iterates through a range of values in a worksheet.
The sub ETC simply checks for errors and if none are found it calls NEXT which gets the record for the next value.
There is a FINITE number of values that NEXT can iterate through.
May be you shall not call next in etc at all?
As ypu presented in post #3 the control is returned to next anyway after etc is finished.
So may be such approach would do the job:
Please Login or Register to view this content.
Please note that our https://www.excelforum.com/forum-rul...rum-rules.html require that even such code like:
shall be presented in code tagsPlease Login or Register to view this content.
So please edit your post #7 accordingly
The issue that I have is that the ETC code is the one that flags for errors.
On further research I believe the problem I have is that ETC does not end.
Next calls ETC which calls NEXT which calls ETC and eventually the stack is full.
If I put the NEXT and ETC code in the same sub it still ends up calling itself and eventually runs out of stack space...
I took your advice and rewrote the loop, as a normal loop in a single sub and it works fine.
Whilst I have solved my problem, it would be useful to learn more about this stack space problem for future reference.
Is it bad coding to have recursive calling?
your actual code may not be an infinite loop (although the stack space error suggests otherwise and you essentially admit as much in post #13) but the code you posted certainly is. there are no exits or conditions in anything that you have actually provided
As I explained 3 times to you...The NEXT code loops through a range of values. Part of the 'do stuff' code exits the NEXT sub when it reaches the last value.
The runtime 28 error is a result of the recursive calling in my code, yes it effectively creates an infinite loop but as I have explained 3 times to you, has nothing to do with the 'do stuff' part of the code. You have so far completely ignored my query, or refused to provide any meaningful advice, instead insisting on seeing code which has no relevance to the problem.
The line of code in the ETC sub that calls NEXT is creating the runtime error because it is highlighted when the code bugs out.
I took Kaper's advise and rewrote my loop to overcome the recursive calling error.
The solution turned out to be relatively simple, so thank you Kaper!
Please Login or Register to view this content.
Instead of having x as a variable in ETC, everytime the ETC codes finds an error it adds 1 to Range("A6") , then I simply test the value in Range("A6") to let the code continue. Problem solved.
Just to be clear. I was not being ungrateful for the help offered.
I tested the code myself, as I presented it earlier in the thread, with the all of the 'do stuff' commented out, so I was 100% certain that the 'do stuff' code was NOT the cause of the error.
As was pointed out, it was an infinite loop, my query related to how best to overcome this.
I am still none the wiser about runtime error 28, but like always, learning by trial and error is usually a good way to learn VBA coding.
If my responses caused any offence, I apologise. I have learned much from many contributors on this website and I am grateful that people take time out of their day to help novice coders like myself.
Glad to hear that
and thanks for marking thread Solved amd for reputation point :-)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks