killing all procedures?

[Ouka]

Hi all,

How do you kill all procedures on an error?

I have a procedure that calls a series of other procedures. I have
error code in each of those sub procedures that will kill that sub
procedure if there is an error within that sub procedure, but the main
procedure thread still keeps firing off.




Code:
--------------------
Private Sub MasterList_click()

'procedure code here'

Call SubP1
Call SubP2

End sub


Private SubP1()

On Error Goto Handler1
Exit Sub
Handler1:
MsbBox "An error has occured, exiting procedure"

'sub procedure code here'

End Sub
--------------------



But SubP2 still fires off, as well as anything else downstream of SubP1
in MasterList even if SubP1 errors out and is killed properly.

So what can I insert into the error handler in SubP1 that will kill all
procedures entirely?

 

[Harald Staff]

You want the Sub to return something. That's a Function, not a Sub. Or
rather; a Sub is a Fubctionthat returns nothing. Try this, testrun
MasterList_click a few times until it makes sense:

Private Sub MasterList_click()
If P1 = False Then Exit Sub
If P2 = False Then Exit Sub
'and so on
End Sub

Private Function P1() As Boolean
Dim R As Long
On Error GoTo Handler1
R = 2 / (Second(Now) Mod 2)
P1 = True
MsgBox "Yo from P1"
Exit Function
Handler1:
P1 = False
MsgBox Error
End Function

Private Function P2() As Boolean
MsgBox "Yo from P2!"
End Function

HTH. Best wishes Harald

 

[Walt]

Hi Ouka,

From the Excel VBA Help system:

Statement: End
Description: Terminates execution immediately. Never required by
itself but may be placed anywhere in a procedure to end code execution,
close files opened with the Open statement and to clear variables.

Meaning this should do it:
Private SubP1()
On Error Goto Handler1
'sub procedure code here'
Exit Sub
Handler1:
MsbBox "An error has occurred, exiting procedure"
'EXIT code here, if any, besides the following End statement'
End <<<<<<<<< This shuts down VBA execution at this point
End Sub

NOTE: The closing of VBA opened files and clearing of variables may
require accommodation in your program flow.

Best Regards,
Walt

 

[Myrna Larson]

True, but not considered "good programming practice" by the purists, and it
has the drawback you note re open files, etc.

 

[Walt]

Hi Myrna,

I try to never say never, but there's usually a way to avoid the
necessity of violating a best practice - point taken. Given that Ouka
wants his main to quit on failure in SubP1, would something like this
be better?:

'Declare a public variable
Public OKGo as Boolean

'The Main
Private Sub MasterList_click()
'procedure code here'
OKGo = True
Call SubP1
If OKGo then Call SubP2
End sub

'The First Sub
Private SubP1()
On Error Goto Handler1
'sub procedure code here'
Exit Sub
Handler1:
MsbBox "An error has occurred, exiting procedure SubP1"
'EXIT code here, if any, besides the following OKGo setting'
OKGo = False
End Sub

Best Regards,
Walt

 

[Ouka]

Thanks all! Both ways worked like a charm. Just trying to decide whic
result I like better