VBA On…GoTo Statement

VBA On…GoTo Statement is one of the most useful statements. It is used to direct the execution of the code to a specific line or label within a procedure. In this blog post, we will explore the purpose, syntax, top 5 examples, important notes and remarks, and conclude with asking for feedback and views on the On…GoTo statement in VBA.

VBA On…GoTo Statement

Purpose of On…GoTo Statement

The primary purpose of the On…GoTo statement is to create a branching structure for a procedure. It allows the programmer to specify which line of code should be executed next, based on a particular condition. This statement is particularly useful in handling errors and debugging code as it helps to manage and control the flow of the program.

Syntax of On…GoTo Statement

The syntax of the On…GoTo statement is as follows:

    On expression GoTo label

The ‘expression’ can be a numeric value that is evaluated to determine the branch to be taken. This statement can also contain multiple ‘case’ clauses with different expressions and labels. In case no match is found for the expression, execution resumes at the ‘Else’ clause, if specified. If no match is found and there is no ‘Else’ clause, the program will exit the On…GoTo statement.

Examples of VBA On…GoTo Statement

1. Basic Example:

Sub BasicExample()
    Dim x As Integer
    x = 7
    On x GoTo label1
    MsgBox "This line will not be executed."
label1:
    MsgBox "This line will be executed."
End Sub

Explanation: In this example, the value of variable ‘x’ is 7. As per the On…GoTo statement, execution will move to the label ‘label1’. Therefore, the message box with the text “This line will be executed” will be displayed.

2. Error Handling:

Sub ErrorHandlingExample()
    Dim x As Integer
    x = 0
    On Error GoTo error_handler
    MsgBox 10 / x
exit_sub:
    Exit Sub
error_handler:
    MsgBox "Error: Can't divide by zero."
    GoTo exit_sub
End Sub

>Explanation: In this example, a division by zero error is expected due to the value of variable ‘x’. The ‘On…’ statement captures this error and moves execution to the error handler, ‘error_handler’. After displaying the error message, the program will exit the subroutine.

Sub MultipleCasesExample()
    Dim x As Integer
    x = 2
    On x GoTo label1, label2, label3
    MsgBox "This line will not be executed."
label1:
    MsgBox "This line will be executed first."
    GoTo exit_sub
label2:
    MsgBox "This line will be executed second."
    GoTo exit_sub
label3:
    MsgBox "This line will be executed third."
exit_sub:
    Exit Sub
End Sub

Explanation: In this example, the variable ‘x’ has a value of 2. As per the On…GoTo statement, multiple ‘case’ clauses are specified with different labels. The program will execute the code under the first matching ‘case’, which is ‘label1’, and then move to the ‘exit_sub’ label.

4. ‘Else’ Clause:

Sub ElseClauseExample()
    Dim x As Integer
    x = 7
    On x GoTo label1, label2, label3
    MsgBox "This line will not be executed."
label4:
    MsgBox "This line will be executed if no match is found."
    GoTo exit_sub
label1:
    MsgBox "This line will not be executed."
    GoTo exit_sub
label2:
    MsgBox "This line will not be executed."
    GoTo exit_sub
label3:
    MsgBox "This line will not be executed."
    GoTo exit_sub
exit_sub:
    Exit Sub
End Sub

Explanation: In this example, the ‘Else’ clause is specified, and the label ‘label4’ is used to display a message if no match is found for the ‘expression’. As there is no ‘case’ clause matching the value of variable ‘x’, the program will execute the code under the ‘Else’ clause and move to the ‘exit_sub’ label.

5. Nested Use of On…GoTo Statement:

Sub NestedExample()
    Dim x As Integer
    x = 1
    On x GoTo label1, label2
    MsgBox "This line will not be executed."
label1:
    MsgBox "This line will be executed first."
    GoTo exit_sub
label2:
    MsgBox "This line will not be executed."
    x = 2
    On x GoTo label3
    GoTo exit_sub
label3:
    MsgBox "This line will be executed second."
exit_sub:
    Exit Sub
End Sub

Explanation: In this example, the On…GoTo statement is nested within another one. First, the program will execute the code under the ‘case’ clause that matches the value of variable ‘x’. After that, the program will move to the nested On…GoTo statement and execute the code under the matching label. In this case, the value of variable ‘x’ is 1, so the program will execute the code under ‘label1’ and then move to ‘exit_sub’ label.

Important Notes & Remarks

• The VBA On…GoTo statement can only be used within a procedure, and it is not valid in class modules.
• It is considered a bad programming practice to use ‘GoTo’ statements as it may make the code difficult to understand, debug and maintain.
• The branching structure created by the On…GoTo statement can be replaced with other control structures like ‘If…Then’ or ‘Select Case’, which are more readable and maintainable.
• In case the program encounters an On…GoTo statement within a loop, it will exit the loop and move to the specified label. This can be avoided by enclosing the loop within a ‘Sub’ or ‘Function’ procedure.
• If the On…GoTo statement is not nested, the program will continue to execute the code after the line containing the statement. Therefore, labels used in the On…GoTo statement must be placed within the same sub or function.

Concluded Post & Feedback

In conclusion, the VBA On…GoTo statement is a powerful tool in VBA that allows developers to direct the flow of the program based on certain conditions. While it has its uses, it is important to use it sparingly and only when necessary. It is advised to use other control structures like ‘If…Then’ or ‘Select Case’ for better readability and maintainability of the code.
We hope that this guide on the On…GoTo statement has given you a better understanding of its purpose, syntax, and usage.

We would love to hear your feedback and views on the use of On…GoTo statement in VBA. Did you find it useful? Do you have any tips or suggestions to share? Let us know in the comments below!