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
For a better example, look at the below 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 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 excel 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. 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 one more time.
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.
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.
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.
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 as Run Time Error ‘11’: Division By Zero.
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.
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, 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.
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.
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 then exit the subprocedure. While running code, if an error occurs, it will show the message box in VBA like below.
This is more of a reliable way of exiting the Sub Procedure.
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-