VBA Exit Sub

Excel VBA Exit Sub Procedure

Exit Sub statement exits the subprocedure earlier than the defined lines of VBA codes. However, to exit the subprocedure we need to apply some sort of logical test.

Let’s 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

You can download this VBA Exit Sub Excel Template here – VBA Exit Sub Excel Template

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
exit sub example 1.1

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

exit sub example 1.2

Now I want to insert only 5 serial numbers, and as soon as the value of the variable “k” becomes 6, I want to exit the sub.

I 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.

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
exit sub example 1.3

Now run the code line by line. Press the F8 key to start the proceedings.

exit sub example 1.8

As of now, the k value is zero.

exit sub example 1.4

To change the k value to 1, press the F8 key one more time.

exit sub example 1.5

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

exit sub example 1.6

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

exit sub example 1.7

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

Example #2 – On Error Exit the Subprocedure

We can also exit the subprocedure when we get the error values. For example, consider the below data of dividing the number1 from number 2.

example 2.1

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
example 2.3

As we know, we cannot divide any number by zero. So if we attempt to do that, we will get the error as Run Time Error ‘11’: Division By Zero.

example 2.2

To avoid this as soon as we encounter any error, I will mention my macro to exit the subprocedure with immediate effect. 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
example 2.4

In the above example, I have mentioned the statement “On Error Goto ErrorHandler.” Here the word ErrorHandler is the label I have assigned. If you can see at the bottom of the code, I have mentioned the brand as

ErrorHandler:
             Exit Sub

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

Now I will run the code; it will calculate the division until it finds an error.

exit sub example 2.5

As you can see in the cell C7, it has encountered an error as “Division by Zero,” so it has exit the subprocedure. Without informing the user exiting the subprocedure is always a dangerous thing. To notify the user of the error, we can include one small message box.

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
example 2.6

The above code will show the error message 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.

example 2.7

This is more of a reliable way of exiting the Sub Procedure.

Recommended Articles

This has been a guide to VBA Exit Sub Procedure. Here we discuss how to exit the VBA subprocedure when an error occurs in the code with an example and downloadable excel sheet. You can learn more about VBA from the following articles-

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>