Excel VBA Exit 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

Examples

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 excelLogical Test In ExcelA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical test.read more as IF k = 6 Then Exit Sub.

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.

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.

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.

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 VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more like below.

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

This article is a guide to VBA Exit Sub Procedure. Here, we discuss how to exit the VBA sub procedure when an error occurs in the code with an example and downloadable Excel sheet. You can learn more about VBA from the following articles: –