How do you conditionally stop a sub (macro) using a separate sub

[hkeiner]

I haven't been able to figure how to do something that is probably quite simple.

I want to include a "testing" sub within a sub (parent macro) to check for certain error conditions before allowing the parent macro to continue executing any other subs (Sub1, Sub2, etc.) that follow the testing sub. I have tried to consruct a "testing" sub that stops if certain error conditions occur, but this does not stop the parent Macro from continuing. I figure that I am either (1) calling the testing sub incorrectly on the parent macro, (2) I am constructing the testing sub incorrectly, or (3) I need to use a testing function instead of a testing sub.

The reason I want to do it this way is that the testing code in the testing sub is quite long and extensive and I want to peform the tests in other macros (all on the same module in this case) without repeating the testing code text in each individual macro.

Thanks in advance for any help




sub ParentMacro ()
TestingSub
Sub1
Sub2
Sub3
End Sub

sub TestingSub ()
On Error GoTo ErrorMsg
' Procedures that may or may not result in an error
Exit Sub
ErrorMsg:
MsgBox "Errors occured. The parent macro will now stop."
Exit Sub
End Sub

[fumei]

"I have tried to consruct a "testing" sub that stops if certain error conditions occur, but this does not stop the parent Macro from continuing. "

You do not understand how Subs work. You need to read up on Scope.

Once the "parent" Sub is executed, it MUST continue, regardless of what happens in any subroutine. That being said, you CAN have:

1. a public variable that the subroutine sets that is read (after the subroutine terminates) by the "parent". Its value can cause the parent do an either/or instruction.


Public Sure As Boolean

Sub Parent()
TestingSub
If Sure = True Then
Sub1
Sub2
Sub3
Else ' Sure = False
Exit Sub
End If
End Sub

Sub TestingSub()
On Error GoTo MyErrorSet
' yadda yadda your stuff
Sure = True
Exit Sub
MyErrorSet:
Sure = False
End Sub

Now in TestingSub, if there is an error the boolean variable Sure = False. TestIngSub terminates, and focus is passed back to Parent. Parent check the boolean variable.

If it is true, then Sub1, Sub2, Sub3 are executed (assuming they do not have similar instructions). If Sure = False, then Parent terminates.


2. Doing essentially the same thing (as the same thing is required!), but use a Function.


Sub Parent()
If TestingFunction Then
Sub1
Sub2
Sub3
End If
End Sub

Function TestIngFunction() As Boolean
' procedures to determine true or false
' this could be instructions that produce an error
On Error Goto Wrong
' stuff
TestingFunction = True
Exit Function
Wrong:
TestingFunction = False
End Function

In this case the line:

If TestingFunction Then

means if TestingFunction - a boolean return value - is True, do Sub1, Sub2 etc.

[fumei]

BTW:

"The reason I want to do it this way is that the code in the testing sub is quite long and extensive and I want to reuse the code in other macros (on the same module) rather than repeating it in each individual macro. "

Good! That is absolutely the way to go. This is a best practice.

[fumei]

Second BTW:

Which to use? A Sub or a Function? It depends.

Functions, unlike Subs return a value - always. This is significant, and is the main point of Functions. This returned value can be any data type; a boolean True/False, a number, a string.

If it is a specific value that determines the instructional flow of the Parent, I would suggest using a Function. Remember a function itself can (and usually does) carry out instructions, so you can have a number of instructions that could end up with a string, or a number, or a True/False condition. Something that causes an error is basically a True/False condition.

The difference, at least in the situation you are describing, is that for you to use a Sub to determine the flow of the Parent, you need something with a value that the Parent can test to see it it goes Left, or Right (so to speak). THAT means a testable variable.

And as that testable variable must be in Scope - read up on Scope! - it must be readable from BOTH the parent sub and the sub-sub. THAT means it must be Public.

There is definitely a place for Public variables, but generally speaking it is good practice to not use them unless you have to. In this situation, you do not have to.

[hkeiner]

Thanks for "public variable" tip. It works great.

I do need to get a better grasp of Scope, particulary when it comes to using multiple subs (within a parent sub) for handling errors. The below "guide" I found on an online VBA tutorial gave me the idea that the proper location of any ON ERROR statements was all that was needed. Perhaps I don't yet understand Scope well enough or I don't understand the below guide well enough.

Anyways, I think that your "public variable" method should be able to handle all of the error control situations that I will need to deal with.

Thanks again.



Error Handling With Multiple Procedures
Every procedure need not have a error code. When an error occurs, VBA uses the last On Error statement to direct code execution. If the code causing the error is in a procedure with an On Error statement, error handling is as described in the above section. However, if the procedure in which the error occurs does not have an error handler, VBA looks backwards through the procedure calls which lead to the erroneous code. For example if procedure A calls B and B calls C, and A is the only procedure with an error handler, if an error occurs in procedure C, code execution is immediately transferred to the error handler in procedure A, skipping the remaining code in B.

[fumei]

Scope.

Any variable or object is viable within the scope it is declared in.


Sub Yadda_1()
MsgBox strWhatever
End Sub

Sub MakeThis()
Dim strWhatever
strWhatever = "Blah Blah Blah"
End Sub


The variable strWhatever is in scope ONLY within the procedure MakeThis. Once MakeThis terminates, the variable is destroyed. So, even if you execute MakeThis (and set the variable with a value), if you execute Yadda_1, you will get an error. VBA has no idea what strWhatever is.


Public strWhatever As String

Sub Yadda_1()
MsgBox strWhatever
End Sub

Sub MakeThis()
Dim strWhatever
strWhatever = "Blah Blah Blah"
End Sub


Now, if you execute MakeThis first, the Public variable is set, and still in Scope when you execute Yadda_1.

However, if you execute Yadda_1 first, there is no error, but there is no value either.

strWhatever is in Scope for as long as the module it is in is available. That is why variables declared in the code module of a userform are NOT in Scope outside of the userform. Declare Public variable in a standard module.

While connected, Scope and error-handling are not the same.