Beginners question: how to stop / abort execution of vba program

[Ardus Petus]

Use the End instruction
Just End, not End Sub or End Function

HTH
--
AP

"Rainer" <[email protected]> a écrit dans le message de news:
eH62M$%[email protected]...
> Hi,
>
> how can I abort / cancel a vba program immediately, without returning to
> the calling procedure?
> I already have a rather complex vba application with many sub's and
> functions.
> The only thing I found by now is the "stop" command, but it enters
> debugging mode, so this is not what I want.
> i want to have something like this:
>
> Sub Main
> CheckValues
> MsgBox "Completed successfully"
> End Sub
>
> Sub CheckValues
> If SomeVar = SomeValue Then
> MsgBox "Error"
> 'now stop it
> End If
> End Sub
>
>

[Rainer]

Hi,

how can I abort / cancel a vba program immediately, without returning to the
calling procedure?
I already have a rather complex vba application with many sub's and
functions.
The only thing I found by now is the "stop" command, but it enters debugging
mode, so this is not what I want.
i want to have something like this:

Sub Main
CheckValues
MsgBox "Completed successfully"
End Sub

Sub CheckValues
If SomeVar = SomeValue Then
MsgBox "Error"
'now stop it
End If
End Sub

[Jonathan West]

Hi Rainer

I would work it like this

Sub Main
If CheckValues() Then
MsgBox "Completed successfully"
End If
End Sub

Function CheckValues() As Boolean
If SomeVar = SomeValue Then
MsgBox "Error"
Exit Function
End If
CheckValues = True
End Function


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup



"Rainer" <[email protected]> wrote in message
news:eH62M$%[email protected]...
> Hi,
>
> how can I abort / cancel a vba program immediately, without returning to
> the calling procedure?
> I already have a rather complex vba application with many sub's and
> functions.
> The only thing I found by now is the "stop" command, but it enters
> debugging mode, so this is not what I want.
> i want to have something like this:
>
> Sub Main
> CheckValues
> MsgBox "Completed successfully"
> End Sub
>
> Sub CheckValues
> If SomeVar = SomeValue Then
> MsgBox "Error"
> 'now stop it
> End If
> End Sub
>
>

[Jonathan West]

"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> Use the End instruction
> Just End, not End Sub or End Function

In the VB6 newsgroups, that kind of advice can start a flamewar:-)

The problem with using End is well described in the VBA help file.

"Note The End statement stops code execution abruptly, without invoking
the Unload, QueryUnload, or Terminate event, or any other Visual Basic code.
Code you have placed in the Unload, QueryUnload, and Terminate events of
forms and class modules is not executed. Objects created from class modules
are destroyed, files opened using the Open statement are closed, and memory
used by your program is freed. Object references held by other programs are
invalidated.

The End statement provides a way to force your program to halt. For normal
termination of a Visual Basic program, you should unload all forms. Your
program closes as soon as there are no other programs holding references to
objects created from your public class modules and no code executing."


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

[Bob Phillips]

But VBA ain't VB6, and all the things you mention are probably exactly what
he wants to happen, everything cleared down. The host application is still
running.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jonathan West" <[email protected]> wrote in message
news:[email protected]...
>
> "Ardus Petus" <[email protected]> wrote in message
> news:[email protected]...
> > Use the End instruction
> > Just End, not End Sub or End Function
>
> In the VB6 newsgroups, that kind of advice can start a flamewar:-)
>
> The problem with using End is well described in the VBA help file.
>
> "Note The End statement stops code execution abruptly, without invoking
> the Unload, QueryUnload, or Terminate event, or any other Visual Basic
code.
> Code you have placed in the Unload, QueryUnload, and Terminate events of
> forms and class modules is not executed. Objects created from class
modules
> are destroyed, files opened using the Open statement are closed, and
memory
> used by your program is freed. Object references held by other programs
are
> invalidated.
>
> The End statement provides a way to force your program to halt. For normal
> termination of a Visual Basic program, you should unload all forms. Your
> program closes as soon as there are no other programs holding references
to
> objects created from your public class modules and no code executing."
>
>
> --
> Regards
> Jonathan West - Word MVP
> www.intelligentdocuments.co.uk
> Please reply to the newsgroup
>
>
>

[Jezebel]

Actually, it's more serious than that, on at least two counts --

1. Using End will cause the application to crash under some (hard to
predict) circumstances. Add-ins and class modules can be strangely affected
by the mere presence of the End statement (even if you don't call the
function that contains it).

2. As a matter of code design, a sub-procedure has no knowledge of the
context in which it is called. The calling procedure may well have some
cleaning up to do -- such as returning the app to visibility, re-enabling
things, etc.





"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> But VBA ain't VB6, and all the things you mention are probably exactly
> what
> he wants to happen, everything cleared down. The host application is still
> running.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Jonathan West" <[email protected]> wrote in message
> news:[email protected]...
>>
>> "Ardus Petus" <[email protected]> wrote in message
>> news:[email protected]...
>> > Use the End instruction
>> > Just End, not End Sub or End Function
>>
>> In the VB6 newsgroups, that kind of advice can start a flamewar:-)
>>
>> The problem with using End is well described in the VBA help file.
>>
>> "Note The End statement stops code execution abruptly, without invoking
>> the Unload, QueryUnload, or Terminate event, or any other Visual Basic
> code.
>> Code you have placed in the Unload, QueryUnload, and Terminate events of
>> forms and class modules is not executed. Objects created from class
> modules
>> are destroyed, files opened using the Open statement are closed, and
> memory
>> used by your program is freed. Object references held by other programs
> are
>> invalidated.
>>
>> The End statement provides a way to force your program to halt. For
>> normal
>> termination of a Visual Basic program, you should unload all forms. Your
>> program closes as soon as there are no other programs holding references
> to
>> objects created from your public class modules and no code executing."
>>
>>
>> --
>> Regards
>> Jonathan West - Word MVP
>> www.intelligentdocuments.co.uk
>> Please reply to the newsgroup
>>
>>
>>
>
>

[Bob Phillips]

"Jezebel" <[email protected]> wrote in message
news:[email protected]...
> Actually, it's more serious than that, on at least two counts --
>
> 1. Using End will cause the application to crash under some (hard to
> predict) circumstances. Add-ins and class modules can be strangely
affected
> by the mere presence of the End statement (even if you don't call the
> function that contains it).


I have never seen that, and would be interested in hearing of such
circumstances.


> 2. As a matter of code design, a sub-procedure has no knowledge of the
> context in which it is called. The calling procedure may well have some
> cleaning up to do -- such as returning the app to visibility, re-enabling
> things, etc.


That's just a question of good/bad coding, nothing to do with End. It would
be just as easy to let code finish in the normal manner without doing those
things, so and resetting would be no more, no less, applicable to a forced
end as a non-forced end. Excel/VBA won't do it for you.

[Jezebel]

>
> I have never seen that, and would be interested in hearing of such
> circumstances.

Google will find you plenty.



>
> That's just a question of good/bad coding, nothing to do with End. It
> would
> be just as easy to let code finish in the normal manner without doing
> those
> things, so and resetting would be no more, no less, applicable to a forced
> end as a non-forced end. Excel/VBA won't do it for you.
>

If process A calls process B, and process B uses an End statement, then the
remainder of process A does not run. This is *bad* coding, and *everything*
to so with End.

[Jezebel]

Using End in a VBA application also clears any global variables. Any class
objects referenced by those variables are destroyed without notice, and
without the firing the class's Terminate event.

[Bob Phillips]

I know, but I would assume that anyone wanting to use End would be wanting
just that effect. As to your previous point, as I said previously, it is bad
coding, but not End causing the problem, that problem will occur if the code
is not self-tidying, with or without End.

Your advice about Google doesn't help me much, what search criteria do I
use. End and Excel crash gets millions, but none that I can see about being
caused by an End statement (Problem is that End is ubiquitous, End If, End
Sub, etc.)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jezebel" <[email protected]> wrote in message
news:[email protected]...
> Using End in a VBA application also clears any global variables. Any class
> objects referenced by those variables are destroyed without notice, and
> without the firing the class's Terminate event.
>
>
>

[Karl E. Peterson]

Bob Phillips wrote:
>> Using End in a VBA application also clears any global variables. Any
>> class objects referenced by those variables are destroyed without
>> notice, and without the firing the class's Terminate event.
>
> I know, but I would assume that anyone wanting to use End would be
> wanting just that effect.

Similarly, one who wants a car to stop has the option of using the most
convenient brick wall or bridge abutment. They will, indeed, achieve their
objective.
--
Working without a .NET?
http://classicvb.org/

[Jean-Guy Marcil]

Karl E. Peterson was telling us:
Karl E. Peterson nous racontait que :

> Bob Phillips wrote:
>>> Using End in a VBA application also clears any global variables. Any
>>> class objects referenced by those variables are destroyed without
>>> notice, and without the firing the class's Terminate event.
>>
>> I know, but I would assume that anyone wanting to use End would be
>> wanting just that effect.
>
> Similarly, one who wants a car to stop has the option of using the
> most convenient brick wall or bridge abutment. They will, indeed,
> achieve their objective.

I, for one, prefer driving into trees, but I must admit that walls are more
convenient, especially in the city.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
[email protected]ISTOO
Word MVP site: http://www.word.mvps.org

[Karl E. Peterson]

Jean-Guy Marcil wrote:
> Karl E. Peterson was telling us:
> Karl E. Peterson nous racontait que :
>
>> Bob Phillips wrote:
>>>> Using End in a VBA application also clears any global variables.
>>>> Any class objects referenced by those variables are destroyed
>>>> without notice, and without the firing the class's Terminate event.
>>>
>>> I know, but I would assume that anyone wanting to use End would be
>>> wanting just that effect.
>>
>> Similarly, one who wants a car to stop has the option of using the
>> most convenient brick wall or bridge abutment. They will, indeed,
>> achieve their objective.
>
> I, for one, prefer driving into trees, but I must admit that walls
> are more convenient, especially in the city.

They're also more abrupt, hastening the desired outcome! Trees sorta bend,
eh?
--
Working without a .NET?
http://classicvb.org/

[Jezebel]

Bob, really, this has been debated and resolved in times *long* past. Do a
Google on something like VBA +"End statement" to get chapter and verse on
it.



"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
>I know, but I would assume that anyone wanting to use End would be wanting
> just that effect. As to your previous point, as I said previously, it is
> bad
> coding, but not End causing the problem, that problem will occur if the
> code
> is not self-tidying, with or without End.
>
> Your advice about Google doesn't help me much, what search criteria do I
> use. End and Excel crash gets millions, but none that I can see about
> being
> caused by an End statement (Problem is that End is ubiquitous, End If, End
> Sub, etc.)
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Jezebel" <[email protected]> wrote in message
> news:[email protected]...
>> Using End in a VBA application also clears any global variables. Any
>> class
>> objects referenced by those variables are destroyed without notice, and
>> without the firing the class's Terminate event.
>>
>>
>>
>
>

[Dave Peterson]

I don't recall ever seeing a post that mentioned that excel crashed when "End"
(by itself) was used.

Maybe you could search google and share one example that caused excel to crash.

I searched google for:
VBA "end statement" crash
and got 7 hits.

I didn't see anything in those 7 threads that supported your position. There
were mentions that workbooks/addins may crash--but not the excel application
itself.

But I did see a quote from Tom Ogilvy that was nice:

To purveyors of the END statement it will be said: "Bother your neighbors,
especially those who are clients and coworkers, and you will be punished;
leave others untroubled by your vice and you will be viewed with disapproval
by those who would write code but left alone."



Jezebel wrote:
>
> Bob, really, this has been debated and resolved in times *long* past. Do a
> Google on something like VBA +"End statement" to get chapter and verse on
> it.
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> >I know, but I would assume that anyone wanting to use End would be wanting
> > just that effect. As to your previous point, as I said previously, it is
> > bad
> > coding, but not End causing the problem, that problem will occur if the
> > code
> > is not self-tidying, with or without End.
> >
> > Your advice about Google doesn't help me much, what search criteria do I
> > use. End and Excel crash gets millions, but none that I can see about
> > being
> > caused by an End statement (Problem is that End is ubiquitous, End If, End
> > Sub, etc.)
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Jezebel" <[email protected]> wrote in message
> > news:[email protected]...
> >> Using End in a VBA application also clears any global variables. Any
> >> class
> >> objects referenced by those variables are destroyed without notice, and
> >> without the firing the class's Terminate event.
> >>
> >>
> >>
> >
> >

--

Dave Peterson

[Bob Phillips]

Well I looked an didn't see any saying how Excel crashes. Which is exactly
my point, attributing erroneous 'facts' to support an argument.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jezebel" <[email protected]> wrote in message
news:[email protected]...
> Bob, really, this has been debated and resolved in times *long* past. Do a
> Google on something like VBA +"End statement" to get chapter and verse on
> it.
>
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> >I know, but I would assume that anyone wanting to use End would be
wanting
> > just that effect. As to your previous point, as I said previously, it is
> > bad
> > coding, but not End causing the problem, that problem will occur if the
> > code
> > is not self-tidying, with or without End.
> >
> > Your advice about Google doesn't help me much, what search criteria do I
> > use. End and Excel crash gets millions, but none that I can see about
> > being
> > caused by an End statement (Problem is that End is ubiquitous, End If,
End
> > Sub, etc.)
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Jezebel" <[email protected]> wrote in message
> > news:[email protected]...
> >> Using End in a VBA application also clears any global variables. Any
> >> class
> >> objects referenced by those variables are destroyed without notice, and
> >> without the firing the class's Terminate event.
> >>
> >>
> >>
> >
> >
>
>

[Ten98]

I have to say I've been using End to end my VBA macros for about 14 years, and never, ever come across any issues in doing so.

Obviously if you have a userform open, close it before you use END, or if you have anything that needs to be closed down, data connections, file handlers etc, close it down before you use End.

The claim that it causes crashes is utterly false.