Stop Multiple Macros

[Guest]

I currently have multiple macros that run in a hierarchal manner. The first
one calls the second macro, which calls the third macro, etc. There are
certain If statements in each macro that should end that macro and all macros
running before it.

Using Exit Sub with the If statement only ends the macro that the If
statement is located in. The previous macros then pick up where they left
off. I need a statement that will communicate to all running macros that if
"this" is true in this macro then end all running macros.

I investigated VBA help and came up with using End in the place of Exit Sub,
but it apparently closes all opened files and I don't want that to happen.

Any advice for a newbie?

 

[Guest]

End is a really bad idea. It clears all global varaibles and stops ALL
execution. My recommendation is to turn your subs into functions that return
boolean flags indicating if the executions should continue or not. Something
like this (Change the 1 = 1 and 1 = 2 to see what happens)...

Public Sub MainProcedure()
If A = True Then
MsgBox "A Worked"
If B = True Then
MsgBox "B worked"
Else
MsgBox "B Failed"
End If
Else
MsgBox "A Failed"
End If
End Sub

Public Function A() As Boolean
If 1 = 2 Then
A = True
MsgBox "All is well with A"
Else
A = False
End If

End Function

Public Function B() As Boolean
If 1 = 1 Then
B = True
MsgBox "All is well with B"
Else
B = False
End If
End Function

 

[Chip Pearson]

Modify your procedures from Subs to Functions and have each return a result
indicating whether execution should continue or terminate. If the code
should continue, return True, and if code should terminate, return False.
E.g., code like the following:

Sub TopProc()
Dim B As Boolean
''
' some code
''
B = Proc1()
If B = False Then
Exit Sub
End If
''
' more code
''
End Sub
''''''''''''''''''''''''''''''''''''''
Function Proc1() As Boolean
Dim B As Boolean
''
' code
''
B = Proc2()
If B = False Then
Proc1 = False
Exit Function
End If
''
' more code
''
Proc1 = True
End Function
''''''''''''''''''''''''''''''''''''''
Function Proc2() As Boolean
Dim B As Boolean
''
' code
''
B = Proc3()
If B = False Then
Proc2 = False
Exit Function
End If
''
' mode code
''
Proc2 = True
End Function
''''''''''''''''''''''''''''''''''''''
Function Proc3() As Boolean
''
' code
''
If SomeError = True Then
Proc3 = False
Else
Proc3 = True
End If
''
' more code
''
Proc3 = True
End Function



--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Well, you can't fight the professionals. I'll look into the function
approach. Thank you both for your help. It is very much appreciated.