VBA Exit Sub Statement

Exit Sub statement in VBA allows you to exit a subroutine at any point in the code.

As soon as your VBA code encounters an Exit Sub statement, it stops executing the rest of the code and brings it out of that subroutine.

In this article, I’ll tell you everything you need to know about the Exit Sub statement and how to use it the right way in your VBA code.

Using Exit Sub Statement in VBA

To use the Exit Sub statement in VBA, simply enter the text Exit Sub in a new line in the code.

Below is an example:

Sub CheckInput()
    
    Dim inputValue As Integer
    inputValue = InputBox("Enter a number")
    
    If inputValue < 0 Then
        MsgBox "Negative numbers are not allowed!"
        Exit Sub ' Exit sub if number is negative
    End If
    
    MsgBox "You entered: " & inputValue
    
End Sub

Sub CheckInput()        Dim inputValue As Integer    inputValue = InputBox("Enter a number")        If inputValue < 0 Then        MsgBox "Negative numbers are not allowed!"        Exit Sub ' Exit sub if number is negative    End If        MsgBox "You entered: " & inputValue    End Sub

In the above code, I use an input box to ask the user for a number, and if the number is negative, Exit Sub is used to exit the subroutine.

While this is a very basic example, it gives you an idea that you can use Exit Sub with If conditions or Loops to make your code more efficient.

Note: If you want to use the Exit Sub functionality within a Function, you need to use Exit Function instead

Error Handling with Exit Sub in VBA

Exit Sub can be a useful tool for error-handling in your VBA codes.

You can use it in combination with On Error statements, where, as soon as your code encounters an error, it would be taken to the On Error statement, which can then be used to exit the subroutine.

Sub ErrorHandling()
    
    On Error GoTo ErrorHandler
    
    ' Your code goes here

    Exit Sub

ErrorHandler:
    
    ' Handling the error
    MsgBox "An error occurred: " & Err.Description
  
End Sub

Sub ErrorHandling()        On Error GoTo ErrorHandler        ' Your code goes here    Exit SubErrorHandler:        ' Handling the error    MsgBox "An error occurred: " & Err.Description  End Sub

In the above VBA code, I have the On Error GoTo ErrorHandler statement followed by the code that I want to execute.

If the code runs without any error, Exit Sub is used to then exit from that subroutine. If there is an error, the code jumps to the ErrorHandler procedure and shows a message box.

Early Termination of Code with Exit Sub

Using Exit Sub can improve the performance of your code as it allows you to exit a subroutine if a specific condition is met.

Below is an example where Exit Sub allows us to exit the subroutine when a specific criterion is met, saving us time and processing that would otherwise have been used in going through the entire code:

Sub FindFirstNegativeCell()
    Dim cell As Range
    For Each cell In ActiveSheet.UsedRange
        If cell.Value < 0 Then
            MsgBox "First negative value found in cell " & cell.Address
            Exit Sub
        End If
    Next cell
    MsgBox "No negative values found."
End Sub

Sub FindFirstNegativeCell()    Dim cell As Range    For Each cell In ActiveSheet.UsedRange        If cell.Value < 0 Then            MsgBox "First negative value found in cell " & cell.Address            Exit Sub        End If    Next cell    MsgBox "No negative values found."End Sub

The above code is used to find the first negative value in a range of cells and it uses Exit Sub to exit the code as soon as it finds the first negative value.

Exit Sub vs End Sub

While both Exit Sub and End Sub can be used to exit the subroutine, they are not the same.

Here are the differences:

In this article, I covered everything you need to know about the VBA Exit Sub statement. I hope this article was useful.

Other Excel articles you may also like:

• Learn All About Excel VBA
• Excel VBA MsgBox [Message Box] – All You Need to Know!
• Create New Sheet Using VBA in Excel (Sheets.Add)
• Rename Files Using VBA
• Comments in Excel VBA (Add, Remove, Block Commenting)
• Excel VBA Select Case Statement
• Make VBA Code Pause or Delay (Using Sleep / Wait Commands)