Nested Macros: Scope: how to exit all?

Question

I current have 2 Macros:

The second macro is called within the first to perform a task. However I have logic within the second macro that states if my variable LastRow < 3 then exit the sub. This of course takes us immediately back into macro 1. What I desire here is to then exit immediately macro 1 as well. The way I have attempted to do this is by making LastRow public within both macros.. so when we exit back into macro 1, we have:

sub macro1()
application.run("macro2")
    if LastRow < 3 then
    exit sub
end sub

where macro 2()

sub macro1()

    Static LastRow As Long

        if LastRow < 3 then
        exit sub
else do something
end if
    end sub

I believe I may the issue may be that Static is not giving macro 1 access to variable LastRow.

whats the best way to proceed?

Regards!

Answer 1

You could use End statement in this way:

sub macro2()

    Static LastRow As Long

    if LastRow < 3 then
        '...here is End
        End
    else 
        'do something
    end if
end sub

However, End has some disadvantages you should be aware of. Let me cite them base on MSDN:

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.

When executed, the End statement resets all module-level variables and all static local variables in all modules. To preserve the value of these variables, use the Stop statement instead. You can then resume execution while preserving the value of those variables.

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.

Answer 2

You could use a Function instead of a Sub and return a Boolean for example.

Function macro2() As Boolean
'returns false if the last row is 2 or less, true otherwise

    LastRow As Long

    if LastRow >= 3 then
        macro2 = True
        'do something
    end if
End Function

Then in your first macro:

sub macro1()
    if Not macro2 Then Exit Sub
end sub