Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
Use the End instruction
Just End, not End Sub or End Function HTH -- AP "Rainer" a écrit dans le message de news: ... 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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
"Ardus Petus" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
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" wrote in message ... "Ardus Petus" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
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" wrote in message ... 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" wrote in message ... "Ardus Petus" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
"Jezebel" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
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. |
#9
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
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. |
#10
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
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" wrote in message ... 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. |
#11
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
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/ |
#12
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
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 ISTOO Word MVP site: http://www.word.mvps.org |
#13
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
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/ |
#14
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
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" wrote in message ... 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" wrote in message ... 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. |
#15
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
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" wrote in message ... 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" wrote in message ... 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 |
#16
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners
|
|||
|
|||
Beginners question: how to stop / abort execution of vba program
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Beginners Question | Excel Discussion (Misc queries) | |||
Beginners Question | Excel Discussion (Misc queries) | |||
Stop Execution | Excel Programming | |||
Program execution prob | Excel Programming | |||
stop execution | Excel Programming |