How to stop/exit a process once started via VBA (i.e., Exit Sub)?

[EssKayKay]

I’m not quite sure how to explain this but here it goes.

I have a question on "Exist Sub". I added an Exit Sub line to my code as a test to halt the process at a certain point. However, I noticed that after triggering the Exit Sub, all future routines are also halted; not just the action in question. All I wanted to do was stop the process of that portion of the routine. In order for other “Change” actions to be triggered I have to close the worksheet and reopen it. Is it possible to “reset” the process after triggering an Exit Sub command?

The problem may be that the processes I am exiting are in the either the WorksheetChange or the WorksheetSelectionChange subroutine. Is there a way to simply stop the current process yet leave all future operations function as is? I was thinking something like GoTo but I was unsuccessful doing so (I assume I did something wrong).

I don't expect a specific answer to my situation but maybe point me to some other resource.

Thanks,
Steve

 

[Micron]

IMO GoTo's , beyond error handling are just a no-no, so there's that. To answer your question specifically would require more knowledge of your process(es) and how code is executed.

Know that in Excel vba (more predominately) one event can trigger another and another, so it can be important to disable events being called by some other event. Also, know that code execution might follow a stack: proc1 does something, and maybe calls proc2. Thus proc2 usually takes over and proc1 is suspended. If proc2 calls proc3, proc3 runs until it is done, then execution returns to the calling routine in the stack (proc2), which when finished, returns to proc1 which carries on from there.

Then there is the scenario where one event calls another in an endless loop: A selection change event selects another cell. OK, now that selection triggers another selection change event, which triggers another selection change event, which.... In that case, you may need to disable events, and in some cases, set a global variable in order to stop other code from executing.

So if none of that helps, if all of your "future routines" are nested in that one sub/function

All I wanted to do was stop the process of that portion of the routine

then none of them will work if you exit that procedure. In that case, you may need to use IF blocks to control code flow, or you may need to use a "parent" sub to pass processing to other procedures and Exit from those, thus returning to the parent procedure for further processing. I hope some of that helps if you consider all the above.

 

[EssKayKay]

I might have found a workaround for one of my problems. I receive two 1004 Errors after a specific action (editing a protected cell). I added two SendKeys {"Enter"} commands. I realize this is much frowned upon but I don't know what else to do, plus it's working.

 

[EssKayKay]

IMO GoTo's , beyond error handling are just a no-no, so there's that. To answer your question specifically would require more knowledge of your process(es) and how code is executed.

Know that in Excel vba (more predominately) one event can trigger another and another, so it can be important to disable events being called by some other event. Also, know that code execution might follow a stack: proc1 does something, and maybe calls proc2. Thus proc2 usually takes over and proc1 is suspended. If proc2 calls proc3, proc3 runs until it is done, then execution returns to the calling routine in the stack (proc2), which when finished, returns to proc1 which carries on from there.

Then there is the scenario where one event calls another in an endless loop: A selection change event selects another cell. OK, now that selection triggers another selection change event, which triggers another selection change event, which.... In that case, you may need to disable events, and in some cases, set a global variable in order to stop other code from executing.

So if none of that helps, if all of your "future routines" are nested in that one sub/function

then none of them will work if you exit that procedure. In that case, you may need to use IF blocks to control code flow, or you may need to use a "parent" sub to pass processing to other procedures and Exit from those, thus returning to the parent procedure for further processing. I hope some of that helps if you consider all the above.

Thank you Micron. You pretty much told me what I already expected - not what I wanted to hear but I totally understand. This project has mushroomed exponentially since I first started it months ago. I'm getting real close. Maybe some of these issues we'll just have to live with.

Again, thank you for your concern and input - much appreciated.

 

[Alex Blakenburg]

after triggering the Exit Sub, all future routines are also halted;

If at the start of your Event code you have a statement such as:
Application.EnableEvents = False
It will stop all other events triggering until you turn it back on by:
Application.EnableEvents = True

So any Sub that turns off things like EnableEvents, ScreenUpdating, Caculations etc should have code that turns them back on prior to exiting the Sub regardless of whether you use and Exit Sub, or End Sub or the macro crashes (error handling should goto the label where you turn it back on)

I have to close the worksheet and reopen it. Is it possible to “reset” the process after triggering an Exit Sub command?

If you get stuck eg you are stepping through the macro and bail out early, a quick and dirty would be to copy the line below into the immediate window and hit enter.
Application.EnableEvents = True

 

[EssKayKay]

If at the start of your Event code you have a statement such as:
Application.EnableEvents = False
It will stop all other events triggering until you turn it back on by:
Application.EnableEvents = True

So any Sub that turns off things like EnableEvents, ScreenUpdating, Caculations etc should have code that turns them back on prior to exiting the Sub regardless of whether you use and Exit Sub, or End Sub or the macro crashes (error handling should goto the label where you turn it back on)


If you get stuck eg you are stepping through the macro and bail out early, a quick and dirty would be to copy the line below into the immediate window and hit enter.
Application.EnableEvents = True

Thank you Alex. I will look into this. It sounds promising. This old guy will have to do some reading on this. Hopefully this works out for me.

 

[Micron]

To elaborate on error handling, this is how I usually do it

Return a code based off post code

I have a list of codes that relate to post codes, e.g. if a post code contains B2, B3, B10 etc then the code is B 1-1 if it contains B1, WS10, WV4 etc then the code is B 1-2 and so. I'm trying to get it so when I type in the post code into the first cell, the next cell automatically populates...

www.mrexcel.com