The On – GoSub statement | VBA Jump Statements

VBA programs execute code-lines in a sequential manner. However, situations often arise where you need to skip ahead or circle back to a code-line. That is where jump statements like the On – GoSub statement come into play. They are of immense importance as they allow the non-sequential transfer of control (i.e., program execution).

Introducing the On – GoSub Statement

Unlike the GoSub statement, the On – GoSub statement conditionally jumps control to one of several subroutines. These subroutines are specified by labelled statements. Moreover, the line label or line number it transfers control to depends on the return value of a numeric expression.

The On – GoSub statement is like the GoSub statement in many ways. One of those is that it must be in the same Sub, Function, or Property procedure as its labelled statements.

The header image above illustrates the On – GoSub statement’s syntax. Moreover, the flowchart below shows its logic flow.

Salient Points on Usage

There are several vital things to note about the On – GoSub statement:

• Both line labels and numbers can be simultaneously used in the statement. Also, you can use up to 255 such line labels or numbers. Moreover, you can split the resulting lengthy statement across multiple lines. You would do that with the line continuation character.
• The expression’s return value controls which line label or number runs. E.g. a value of ‘3’ means the third line label or number in the statement runs. However, if the value is ‘0’, then control passes to the code-line right after the On – GoSub statement. The same thing happens if the value exceeds the number of line labels or numbers listed in the statement. Further, an error occurs if the expression’s return value is negative or greater than 255.
• Sometimes the labelled statement appears after the On – GoSub statement. If so, control jumps forward, skipping any intermediate lines between the two statements. Clearly, this risks omitting critical lines, so take extra care to avoid that.
• Other times the labelled statement appears before the On – GoSub statement. If so, control jumps backward, repeating all lines between the two statements. Clearly, this risks an infinite loop, so pay due diligence to prevent that. That’s usually done by adding If or Select Case statements to execute an exit strategy.

The On – GoSub statement serves as a backward compatibility enabler. It is perfectly valid and not necessarily considered bad practice. However, you should use more structured and flexible constructs for conditional multiple-choice branching. Coding constructs like the Select Case statement come to mind.

The On – GoSub Statement in Action

The sample code below shows the On – GoSub statement in use. It also illustrates the salient points discussed above.

The Sub procedure in the sample code above does the following:

• Declares a Byte variable, byt_Tracker, to hold a tracking number (code window line #7);
• Sets byt_Tracker’s value to ‘2’ in the labelled statement, line1 (line #9);
• The On – GoSub statement (line #11) jumps control to the labelled statement, line3 (line #21). Why? Since the On – GoSub statement’s expression returns a value of ‘1,’ the line label in position #1 in the label list (i.e., line3) gets control;
• Triples byt_Tracker‘s value to ‘6’ (line #23);
• Prints the result to the Immediate window using the Debug.Print statement (line #24);
• The Return statement (line #25) jumps control to the line right after the On – GoSub statement. That would be the Exit Sub statement (line #13);
• The Exit Sub statement, right before both subroutines, immediately ends the Sub procedure.

The Exit statement, right before all subroutines in the Sub procedure, is crucial. Without it, the subroutine at line2 (line #15) would run after the tripling. However, that subroutine’s Return statement (line #19) throws a runtime error since it was not invoked by an On – GoSub statement.

In the sample code above, you could have doubled byt_Tracker’s value instead of tripling it. All you would need to do is change the On – GoSub statement’s expression return value to ‘2’. That would jump control to the labelled statement, line2 (line #15) rather than line3 (line #21).

The Sub procedure in the sample code above either doubles or triples byt_Tracker’s value. But it doesn’t do both in a single run. That conditional execution is the main difference between the On – GoSub and GoSub statements.