Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop Multiple Macros
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop Multiple Macros
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 -- HTH... Jim Thomlinson "VBA_Newbie79" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop Multiple Macros
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) "VBA_Newbie79" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop Multiple Macros
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. "Chip Pearson" wrote: 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) "VBA_Newbie79" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programmatically open XLT for edit and stop macros | Excel Programming | |||
linking of macros with other workbooks. need to stop it! | Excel Discussion (Misc queries) | |||
Stop 'Disable Macros' | Excel Programming | |||
How to stop macros in 2nd workbook? | Excel Programming | |||
How to stop macros in 2nd workbook? | Excel Programming |