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.
For this, I will have to add the logical test 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 F8 key to start the proceedings.
As of now, k value is zero.
To change the k value to 1 press 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 VBA 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 exit 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 Sub Procedure
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. Below code is one of 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 label as
ErrorHandler: Exit Sub
So as soon as code encounters an error it will push code to jump to the label and label has the “Exit Sub” statement, so 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 inform the user about 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 sub procedure in excel vba when an error occurs in the code with example and downloadable excel sheet. You can learn more about VBA from following articles-
- VBA On Error GoTo Statement
- Use ScreenUpdating in VBA
- Delete Sheet in Excel VBA
- VBA Message Box (Yes/No)
- IFERROR with VLOOKUP
- Break For Loop in VBA
- Break Points in Excel VBA
- Do Until Loop in Excel VBA