@sonic8 For sure you're much better than me in this. So instead of answering your points, I prefer to ask you a question.
Can you explain the benefits of using individual functions when you don't plan to reuse them? (instead of my jump labels)
Thanks.
There is an old principle in problem analysis that is particularly import for programming in ANY language, ABSOLUTELY not limited to VBA. It is called "divide and conquer." This principle is old enough that Julius Caesar used it and explained it in the
Commentarii de Bellum Gallicum (Caesar's "Gallic Wars"). That method allowed him to divide and conquer what was Gaul and is now mostly France. TWO THOUSAND YEARS ago. (It is probably an older principle but after a couple of thousand years it gets harder to find the older references.)
The old method that I was taught is that when you deal with problems, you can try to build a huge, monolithic solution, or you can step back and try to divide the problem into various parts. As you sub-divide the problem into progressively smaller steps, you reach the point where many of the steps become trivially easy to code with no further analysis. At that point, the isolated function becomes easy to read and easy to document in whatever way you need to document it. AND whether you will re-use that code elsewhere or not, you will minimize the trouble it gives you.
The idea of a "pool" of public variables at the top of a given module surely works - but makes debugging FAR more difficult, since you have what we USA types call a "free for all" going in, where some code touches something without regard to whatever other code might touch it. The MOMENT that the touch becomes inappropriate, you have Hell to pay in tracing down which routine improperly touched the variable. Forcing the routines to limit themselves to using only the input parameters makes it easier to assure that you DON'T make the inappropriate touches - because remember you divided the problem up into easily understood and totally isolated parts. This structure leads to all kinds of side-effects because the "PUBLIC pool" is
equivalently ALWAYS referenced ByRef, never ByVal, so CANNOT be protected against errors.
The difference between GOSUB and straight-out subroutine calls is that a GOSUB simply CANNOT support parameters, so you have no choice but to use "PUBLIC pool" variables and CANNOT constrain those touches.
Here is where things can "go south" on you in a heartbeat... I cannot find any references that suggest that a GOSUB creates a "frame" context. The GOSUB appears to be in the same stack context as its caller. Which means that either
(a) your error handler for the GOSUB's caller must not only handle its own errors, but must also handle errors for ALL of the GOSUB targeted routines, or
(b) each GOSUB routine that needs error handling must declare its own handler BUT when that routine returns to the caller, that calling code must reassert the proper handler. Otherwise, you would have the handler declared in the GOSUB B call handling errors in the GOSUB A context. Yes, this can be done - but is a nightmare of spaghetti code.
If you formalize those GOSUBs into actual subs, they build a proper call frame. Inside that proper context, you can declare a handler if you wish. But the significant part is cleanup. The return from a formal subroutine simply dissolves the call frame on the stack and in so doing, reverts to the prior (caller's) handler automatically. Dissolving the stack frame ALSO dissolves any local variables you might have declared. This is the only time I know where a declared variable actually ceases to exist, and it does so in the name of program cleanup steps.
