VBA GoTo Statement

VBA GoTo statement is a powerful tool within the VBA programming language that allows for flow control in code execution. It allows the programmer to specify a specific line of code to jump to, instead of following the linear flow of the program. This can be useful in certain scenarios where it is necessary to jump across lines of code or to execute a particular block of code multiple times.

In this blog post, we will explore the syntax, examples, important notes and remarks, and conclude by asking for your feedback and views on the GoTo statement.

VBA GoTo Statement

Syntax of GoTo Statement in VBA

The syntax of the GoTo statement in VBA is as follows:

GoTo {label | line number | 0 | -1 | -2 | (line number)}

The keyword GoTo is followed by a target location, which can be specified using a label, line number, or a specific line of code within parentheses. The target location can also be set to 0, -1, or -2, which will transfer control to the beginning of the current procedure, the previous line, or the next line respectively.

Examples of VBA GoTo Statement

Let’s take a look at the top 5 examples of using the GoTo statement in VBA.

Example 1: Jump to a Label

Sub example1()
    Dim num As Integer
    num = InputBox("Enter a number:")
    
    If num > 10 Then GoTo label1
    MsgBox "Number is less than 10."
    
    label1:
    MsgBox "Number is greater than or equal to 10."
End Sub

In this example, we use the GoTo statement to jump to a label (label1) in case the user enters a number greater than 10. The program will then display a dialog box with the message “Number is greater than or equal to 10.”

Example 2: Jump to a Specific Line of Code

Sub example2()
    Dim num As Integer
    num = InputBox("Enter a number:")
    
    If num < 0 Then GoTo (7)
    MsgBox "Number is greater than or equal to 0."
    
    (7):
    MsgBox "Number is less than 0."
End Sub

In this example, we use the parentheses syntax to specify a specific line of code to jump to. If the user enters a negative number, the program will jump to line 7 and display the message “Number is less than 0.”

Example 3: Use a Loop with GoTo

Sub example3()
    Dim i As Integer
    
    i = 1
    loopStart:
    If i > 5 Then GoTo loopEnd
    MsgBox "Current value of i is " & i
    i = i + 1
    GoTo loopStart
    
    loopEnd:
    MsgBox "Loop has ended."
End Sub

In this example, we use the GoTo statement along with a label to create a loop that will execute 5 times. The variable ‘i’ is incremented by 1 in each iteration and displayed in a message box. Once ‘i’ becomes greater than 5, the program will jump to the label ‘loopEnd’ and display the final message.

Example 4: Use GoTo to Handle Errors

Sub example4()
    On Error GoTo errorHandler
    
    Dim num1, num2, result As Integer
    
    num1 = InputBox("Enter the first number:")
    num2 = InputBox("Enter the second number:")
    
    result = num1 / num2
    MsgBox "The result is: " & result
    
    Exit Sub
    
    errorHandler:
    MsgBox "Error: " & Err.Number & " - " & Err.Description
End Sub

In this example, we use the GoTo statement to handle errors that may occur during the execution of the code. If an error occurs, the program will jump to the ‘errorHandler’ label and display a message with the error number and description using the built-in VBA error handling functions.

Example 5: Use GoTo in Select Case Statements

Sub example5()
    Dim fruit As String
    fruit = InputBox("Enter a fruit:")
    
    Select Case fruit
        Case "Apple"
            GoTo message
        Case "Banana"
            GoTo message
        Case "Orange"
            GoTo message
        Case "Mango"
            GoTo message
            
        message:
        MsgBox "You have entered a " & fruit
        Case Else
            MsgBox "Invalid input."
    End Select
End Sub

In this example, we use the GoTo statement to jump to a common message in a Select Case statement. This avoids repeating the same code for each case and makes it easier to maintain and modify in the future.

Important Notes & Remarks

It is important to use the GoTo statement sparingly and only when necessary. Overusing it can make the code difficult to read and debug. Also, the use of GoTo can sometimes lead to unpredictable results, especially when used inside loops or with error handling. It is recommended to use structured programming techniques like loops and conditional statements to achieve the same result whenever possible.

In conclusion, the VBA GoTo statement is a powerful tool for flow control, but it should be used with caution and only when necessary. We have explored its syntax, top 5 examples, and important notes and remarks. We would love to hear your feedback and views on the GoTo statement. Have you used it in your code? Do you have any tips or best practices to share?

Let us know in the comments below. Keep coding with VBA and stay tuned for more informative posts.