The On – GoTo 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 – GoTo 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 – GoTo Statement

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

The On – GoTo statement is like the GoTo 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 – GoTo 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 – GoTo 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 – GoTo 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 – GoTo 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 – GoTo 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 – GoTo 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 – GoTo Statement in Action

The sample code below shows the On – GoTo 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 – GoTo statement (line #11) jumps control to the labelled statement, line3 (line #19). Why? Since the On – GoTo 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 #21);
• Prints the result to the Immediate window using the Debug.Print statement (line #22);
• Since there’s an Exit statement (line #23) after line #22, the Sub procedure ends immediately.

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 – GoTo statement’s expression return value to ‘2’. That would jump control to the labelled statement, line2 (line #13) rather than line3 (line #19).

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 – GoTo and GoTo statements.

The purpose of the Exit statement at the end of the labelled statement, line2, should be obvious. Without it the series of lines starting at the labelled statement, line3, would run by default. That’s a bug because we only want those statements to run on a certain condition.