VBA Exit Sub

Exit Sub statement exits the sub procedure earlier than the defined lines of VBA codes. First, we need to apply a logical test to exit the sub procedure.

Let us construct this in simple terms.

Sub MacroName() '... 'Some code here '... Exit Sub 'Exit the Sub without executing further lines of code below '... 'This code will be ignored '... End Sub

Example #1

For a better example, look at the below code.

Code:

Sub Exit_Example1()

Dim k As Long

For k = 1 To 10

Cells(k, 1).Value = k

Next k

End Sub

The above code will insert serial numbers from 1 to 10 in cells A1 to A10.

Now, we want to insert only 5 serial numbers. As soon as the variable "k" value becomes 6, we want to exit the sub.

We will have to add the logical test in excel as IF k = 6 Then Exit Sub.

Code:

Sub Exit_Example1()

Dim k As Long

For k = 1 To 10

If k = 6 Then Exit Sub

'As soon as k value becomes 6 it will ignore all the codes and exit

Cells(k, 1).Value = k

Next k

End Sub

Now, run the code line by line. Finally, press the F8 key to start the proceedings.

As of now, the k value is zero.

To change the k value to 1, press the F8 key again.

So, the k value is 1. Our code keeps running and will insert 1 to cell A1. Like this, keep running the loop until the value of k becomes 6.

Now, the value of k is 6. The line of code is about to execute our logical test to exit the subprocedure. If we press the F8 key one more time, it will straight go the entire sub procedure only.

As we can see, it has highlighted the word “Exit Sub.” Upon pressing the F8 key, it will exit the sub procedure without going to the word “End Sub.”

Example #2 - On Error Exit the Subprocedure

We can also exit the sub procedure when we get the error values. For example, consider the below data for dividing the number1 from 2.

Below is the code to get the division of two numbers.

Code:

Sub Exit_Example2()

Dim k As Long

For k = 2 To 9
Cells(k, 3).Value = Cells(k, 1).Value / Cells(k, 2).Value
Next k

End Sub

As we know, we cannot divide any number by zero. So, if we attempt to do that, we will get the error "Run-time error '11': Division by zero."

To avoid this, as soon as we encounter any error, we will immediately mention my macro to exit the sub procedure. The below code is one such case.

Code:

Sub Exit_Example2()

Dim k As Long

For k = 2 To 9

On Error GoTo ErrorHandler

Cells(k, 3).Value = Cells(k, 1).Value / Cells(k, 2).Value

Next k

ErrorHandler:
             Exit Sub
End Sub

In the above example, we have mentioned the statement "On Error Goto ErrorHandler." Here, the word "ErrorHandler" is the label we have assigned. As you can see at the bottom of the code, we have mentioned the brand as:

ErrorHandler:
             Exit Sub

As soon as the code encounters an error, it will push the code to jump to the label, and the brand has the "Exit Sub" statement so that it will exit the subprocedure.

Now, we will run the code. First, it will calculate the division until it finds an error.

As you can see in cell C7, it has encountered an error as "Division by zero," so it has exited the subprocedure. However, exiting the sub procedure is always dangerous without informing the user. Therefore, we can include a small message box to notify the user of the error.

Code:

Sub Exit_Example2()

Dim k As Long

For k = 2 To 9

On Error GoTo ErrorHandler

Cells(k, 3).Value = Cells(k, 1).Value / Cells(k, 2).Value

Next k

ErrorHandler:

             MsgBox "Error has Occured and the error is:" & vbNewLine & Err.Description

             Exit Sub

End Sub

The above code will show the error message and then exit the subprocedure. While running code, if an error occurs, it will show the message box in VBA like below.

It is more of a reliable way of exiting the Sub procedure.