The Right Way to Use the Goto Statement in VBA
- The need for a branching statement in code
- The Goto branching statement
- Syntax of the Goto statement
- Sample Programs
The need for a branching statement in code
In a procedure, the program code or the application in which the code is running may encounter unexpected issues or may not work like you expect. To be on the safe side, we can transfer the program’s control to any specific line, thereby skipping the error prone block of code.
This kind of branching helps us skip any block of code or even re-run a block of code from any previous line if the expected action has not happened and the program steps need to be repeated.
The Goto branching statement
Goto is a popular branching statement available for use in most programming languages. In VBA, we can use this statement to move the program control to any line (forward or backward) within the same sub-procedure.
Syntax of the Goto statement
line can either be a label or a line number.
A label is a any word with a colon in front of any line of code. When a label is used in the Goto statement, the control is transferred to the line which starts with that label.
Validate age to see if it is realistic
This is a sample program that validates age. If the age is not realistic , the control is transferred to the line that has the label “Line_no1”.
Sub Goto_demo() Dim age Line_no1: age = InputBox("Enter your age") If age > 110 Then MsgBox "It is impossible. Try again" Goto Line_no1 End If 'Rest of the code can go here End Sub
Error display using Goto label
Sub Goto_demo1() ' handling error at any line in the code using this label On Error Goto I_handle_error ' declare variables Dim a, b ' initialize variables a = "Hello World" b = Split(a, " ") ' Try to display a value out of the array's upperbound MsgBox b(2) ' label and content I_handle_error: Debug.Print "There is an error " & Err.Number & vbCrLf & Err.Description End Sub
Using Goto 0 instead of the actual line number or a label
Goto 0 enables the normal debugging process.
You might wonder what difference this statement makes and why it’s even useful.
Imagine a scenario where “On Error Resume Next” is used at the beginning of the program to skip known errors that need to be ignored. But later after the 100th line of code, we suspect there might be some errors that need to be debugged.
So, in this case, we can use this “On error Goto 0” statement before that 100th line to enable the normal error handling and to prevent the compiler from ignoring the upcoming errors.
This statement essentially disables or reverses the effect of “on error resume next.”
Another example using Goto 0
As in the previous example, we first use “On error resume next” to ignore errors. The program ignores all errors until it encounters the “On Error Goto 0” statement. After this line, error are displayed as usual during runtime.
Sub Goto_demo2() ' skip the normal error handling process On Error Resume Next ' declare variables Dim a, b ' initialize variables a = "Hello World" b = Split(a, " ") ' Try to display a value out of the array's upperbound - error will be skipped Debug.Print b(2) ' Display a proper value Debug.Print b(0) ' enable the normal error handling On Error Goto 0 ' Try running the below error prone code. Debug.Print b(10) End Sub
Goto statement that re-runs a block of code
Sub chk_weight() ' declare variables Dim allwed_wt, current_wt, flag ' assign values in kg allwed_wt = 10 flag = 0 'set a label to start again if weight is not within limits ' CInt is used to convert the string to a number checkagain: current_wt = CInt(InputBox(" Please enter the weight of your baggage")) ' check if the weight is within limits ' display appropriate message to the passenger If current_wt <= allwed_wt Then MsgBox "The weight of your baggage is within the permitted limit. No further action is required." Else MsgBox "The weight of your baggage is more than the permitted limit. Please remove some baggage now as the current weight will be asked again." 'Goto the block of code that gets input and validates the weight of baggage Goto checkagain End If End Sub
Goto statement using a line number
Considering the example above, the label “Checkagain” can be replaced with the respective line number 89 as show in the image below. But as we keep maintaining code by adding and removing lines of code in our modules or procedures, the line numbers are subject to change.
Hence, it is not a good practice to use line numbers in your Goto statements. It is always advisable to create and use labels instead.
Goto is a very popular branching statement which is used in many programming languages in combination with conditions. If used wisely, this statement can help you save time, prevent unnecessary errors, capture or debug unknown errors, and keep your code organized.