VBA Exit Sub or Function

Associated Files Download Links

In VBA, you can exit a Sub or Function, by using the Exit Sub or Exit Function commands.

When the execution of the code comes to Exit Sub or Exit Function, it will exit a Sub or Function and continue with any other code execution.

If you want to learn how to exit a Sub on Error, click on this link: VBA On Error Exit Sub

If you want to learn how to end a whole code execution, click on this link: VBA End

 

Exit a Sub in VBA

You will see on the example what happens when we use the Exit Sub command in a Sub. We created a Sub ExitSub, which has the Exit Sub command inside. The Sub CallExitSub calls this Sub. Here is the code:

 

In the ExitSub, we first enter the For Loop if the value of i is less than 10:

After that we check if the value of i is equal to 5, using the If command. If the value is 5, we want to exit the Sub and return the Message box with the value of i:

If the condition is not met, the following statement increases i by 1 and enters in the For loop again:

 

In the CallExitSub, we first call the Sub ExitSub:

After that we return the Message box:

If you run the CallExitSub, it will first call the ExitSub. If you execute this code in the debug mode, you will see that it will go through the loop 5 times. In the 5th iteration, the value of the variable i becomes 5 and the code enters in the If body. Now the Sub ExitSub is exited and returned to the CallExitSub. The next line is MsgBox “Exit Sub”:

vba exit sub

Image 1. VBA Exit Sub

 

As you can see, the ExitSub is exited right after Exit Sub command, so the MsgBox “The value of i is” & i will be never executed.

 

Exit a Function in VBA

Exiting a function in VBA is similar to exiting a Sub, just the command is Exit Function. In the example, we created the ExitFunc which returns an integer. The Sub CallExitFunction calls this function. Here is the code:

In the ExitFunc, we first enter the For Loop if the value of i is less than 10:

After that we check if the value of i is equal to 5, using the If command. If the value is 5, we assign the value of i to the function result and exit the function:

If the condition is not met, the following statement increases i by 1 and enters in the For loop again:

 

In the CallExitFunction, we first call the function ExitFunc: To do that we have to declare the variable intFunc type integer and assign the result of the ExitFunc function to it:

After that we return the Message box with the value of intFunc:

 

If you run the CallExitFunction, it will first call the function ExitFunc. If you execute this code in the debug mode, you will see that it will go through the loop 5 times. In the 5th iteration, the value of the variable i becomes 5 and the code enters in the If body. Now the value of the ExitFunc becomes i and the function is exited and returned to the CallExitFunction. The next line is MsgBox “The value of intFunc is ” & intFunc:

vba exit function

Image 2. VBA Exit the function

[yuzo]