Terminate execution of macro mid-procedure

[JLMitchell]

In addition to the data validation I specified on the cells my users must enter their parameters into, I have a few other things I need to check about their provided input. If their input is "invalid", as determined by my subsequent checks, I want to put up a message box, select the cell they need to change when they click okay on the message box, and stop execution of the macro immediately in my validation sub. I thought that "Exit" would do it, but when I tested it, I found that another programmer error was detected much further through my code after this "exit" occurred, which leads me to believe that the program continued execution and my "exit" didn't do what I thought. Can someone tell me how to immediately cause a macro to stop running in the middle of a sub?

Incidentally, I am VERY new to Excel VBA macros and have not yet picked up a book about it. I learned BASIC 20 years ago and have been a programmer in various languages since then, but I have been out of the industry for 8 years and only play with it now. I got inspired to play with this and taught myself what I could in the last 48 hours just looking on the web and playing with it hands-on, so this is likely a REALLY stupid question that anyone with ANY knowledge could answer. So please forgive me and I promise to buy at least one book on the subject soon! I refuse to use a "go to", which would be the easy way to force a change in the execution path but goes against everything I have ever been taught since my first experiences with BASIC!

Thanks!

 

[Zack Barresse]

Hello JLMitchell, welcome to the board!!

Can someone tell me how to immediately cause a macro to stop running in the middle of a sub?

Try using Exit Sub instead. That will end the routine, but not all running code, which is (in effect) what Exit does. Generally it's not good to just use End, as it's ramifications are so broad, but it does have it's purposes.

I refuse to use a "go to", which would be the easy way to force a change in the execution path but goes against everything I have ever been taught since my first experiences with BASIC!

Hoorah!! Cool garbanzo's!! You're far more ahead of a lot of people.

If the Exit Sub doesn't work for you, try posting your code and we can go through it. You can always insert a bookmark in your code (selecting offending line in VBE, pressing F9), then when you run it, it will stop and open the VBE so you can step through with F8.

HTH

 

[DominicB]

Good evening JLMitchell

Not a stupid question at all - everyone has to start somewhere. And you were pretty close anyway. The correct syntax is

Exit Sub

HTH

DominicB

 

[JLMitchell]

I have done OO coding in the past, but my strong suit has always been structured programming and my brain defaults to that, so this may be an artifact of my natural bent. I don't exactly want to Exit Sub b/c I don't WANT the rest of the program to execute, and as far as I can tell, subs don't return values I can check for to determine whether to run the next thing or not. I could hide a flag in my spreadsheet and check for that before executing anything else, but that gets messy too.

example:

sub proDriver()

call proValidateValues
call proDoOtherStuff
call proDoFinalStuff

end sub

If proValidateValues finds something wrong in my data, I don't want to call the other subs. If I hide a flag in shWork, I could do this:

sub proDriver()

call proValidateValues
if shWork.Range("A1").value = 0 then
call proDoOtherStuff
call proDoFinalStuff
end if

end sub

but I'd rather just have execution stop upon failure in the validation sub:

sub proValidateValues

if shEntry.Range("B1").value = "B" and shEntry.Range("B2").value >50 then
msgbox ("Total must be less than 50 for type B.")
shEntry.Range("B2").select
[Enter Command to Terminate Execution HERE]
end if

end sub

Does that make more sense? So what is that magic command I'm missing or is there one?

Thanks,
Jennifer

 

[Zack Barresse]

Well, if you used a Boolean type variable, you could just check it from each routine ran ...


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Dim</SPAN> blnEnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> proDriver()
    blnEnd = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Call</SPAN> proValidateValues
    <SPAN style="color:#00007F">If</SPAN> blnEnd <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">Call</SPAN> proDoOtherStuff
    <SPAN style="color:#00007F">If</SPAN> blnEnd <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">Call</SPAN> proDoFinalStuff
    <SPAN style="color:#00007F">If</SPAN> blnEnd <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> proValidateValues()
    <SPAN style="color:#00007F">Dim</SPAN> shEntry <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Set</SPAN> shEntry = Sheets(1)
    <SPAN style="color:#00007F">If</SPAN> shEntry.Range("B1").Value = "B" And shEntry.Range("B2").Value > 50 <SPAN style="color:#00007F">Then</SPAN>
        MsgBox ("Total must be less than 50 for type B.")
        shEntry.Range("B2").Select
        blnEnd = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> proDoOtherStuff()
    <SPAN style="color:#00007F">If</SPAN> 1 = 2 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#007F00">'do some stuff here.. blah blah</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        blnEnd = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Application.Quit
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> proDoFinalStuff()
    <SPAN style="color:#00007F">If</SPAN> 3 = 2 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#007F00">'do some more stuff here.. blah</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        blnEnd = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Application.ActivateMicrosoftApp xlMicrosoftMail
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Notice that you exit out of each sub routine and the main routine will still run. So just check for the boolean returning the type you're looking for and exit the main routine when found.

HTH

 

[JLMitchell]

Thanks. That'll do just fine!

Jennifer

 

[pfarmer]

Thanks. That'll do just fine!

Jennifer

I refer to these flags as semaphores. They may actually take various forms, possible even semaphore files so conditions are remembered between sessions.

As far as goto I tend to believe that the aversion to go tos is often one of age. My first experience basically was with a version of basic that left little choice in many cases. Other languages use similar jump concepts so it really comes down to situations. Many error checking routines essentially are nothing more then goto statements. In this case you need to look at code size, performance issues, etc. I would say avoid them in many if not most cases, but certainly use them if appropriate.

In any case you need to look closely at your entry routines as much as your exits. If for example you turn off screen updating, you toggle calculation to manual, or maybe you toggle your alerts, then you may need to consider turning all of these back on if you need to exit your sub.

Perry

 

[JLMitchell]

Good point about being sure to undo things you did at the beginning before you quit out of a routine or program.

On the "go to" thing, I must say that my first job out of college was supporting a 30 year old COBOL application that was riddled with go tos and virtually devoid of comments (written when processing power was FAR more expensive than people), and it was a nightmare for me and everyone else who worked to support it anytime there was a need to make a change to the program set. So I have seen first hand the chaos that can be brought about when care is not taken to minimize the number of exit points and to maintain a code sequence that can be followed by someone else trying to read it later. I'm sure that there may be a reasonable use for GO TO, and an "abandon ship" error handler is probably as good as any a place to use it. But in general, I think it is an easy out found mostly in programs without a good execution plan.

Jennifer