VBA Exit Sub

Introduction to VBA Exit Sub

Exit Sub seems like ending the subcategory by its name. But in reality, Exit Sub is used to jump out of the sub procedure altogether without letting the complete code getting completed. When we apply Exit Sub in between any code then only code before Exit sub gets run completely in VBA Excel. The code after Exit sub will get skipped and it will jump the entire code after to that. Exit Sub usually works better with Do-While Loop.

How to Apply Exit Sub Using VBA?

Let’s see the examples of Exit Sub in Excel VBA.

Example #1 – VBA Exit Sub

Step 1: To apply Exit Sub we need a module. For that, go the VBA window. Click on Insert menu tab, we will get the list, from there select Module as shown below.

Step 2: After that, a newly opened Module, write the subcategory in the name of VBA Exit Sub or in any other name as shown below.

Code:

Sub VBA_ExitSub1()

End Sub

Step 3: Define a variable Dim with any name, let’ say A. And give it Integer function to it as shown below.

Code:

Sub VBA_ExitSub1()

Dim A As Integer

End Sub

Step 4: Now open a Do While Loop as shown below.

Code:

Sub VBA_ExitSub1()

Dim A As Integer
Do

While

End Sub

Step 5: In between Do While we write a simple code of comparison. Let’s DO addition of variable A with +1. Which means DO Addition for A with its own value +1. And run it till we get the +1 value for variable A.

Code:

Sub VBA_ExitSub1()

Dim A As Integer
Do
A = A + 1

While

End Sub

Step 6: Now how much we want to add +1 to A will be defined in Loop While condition. Which says DO Loop will run following the condition of A + 1 while A is less than 100.

Code:

Sub VBA_ExitSub1()

Dim A As Integer
Do
A = A + 1

Loop While A < 100

End Sub

Step 7: Now we will need a message box (Which is optional) to see the output of Do While loop. For that insert MsgBox and give it to the variable A.

Code:

Sub VBA_ExitSub1()

Dim A As Integer
Do
A = A + 1

Loop While A < 100
MsgBox A

End Sub

Step 8: Now compile the code by pressing the F8 key to see if there is any bug in the code or not. Later run the entire code by pressing the Play button located below the menu bar as shown below. We will see the message box has given us the output as 100. Which means a value of A is less than 100 and formula which we defined in DO Loop says is the value of A is A + 1. So the answer is coming as 100.

Step 9: Now to apply the Exit Sub in performed Do While loop we need to add Exit Sub statement before Do Loop start or after we defined variable A as shown below.

Code:

Sub VBA_ExitSub1()

Dim A As Integer
Exit Sub
Do
 A = A + 1

Loop While A < 100
MsgBox A

End Sub

Now again compile the code and run, if no error found.

We will observe that we have not found any output. Which is because the code run is completed but it has skipped off to Do While loop completely giving no output in a message box.

Example #2 – VBA Exit Sub

Let’s consider another example. In this example, we will see how Exit Sub works on skipping the font color change. For we have some text at cell C3 as shown below. As we can see the text is in default blank color.

Step 1: For this open a new module and give it Sub-Category in the name of VBA Exit sub or in any other suitable name as per your need.

Code:

Sub VBA_ExitSub2()

End Sub

Step 2: Select the range cell which has the text. Here our range cell will be cell C3.

Code:

Sub VBA_ExitSub2()

Range("C3").Select

End Sub

Step 3: As we need to change the font color, so in the next line of code select the Font and Color function simultaneously as shown below.

Code:

Sub VBA_ExitSub2()

Range("C3").Select
Selection.Font.Color =

End Sub

Step 4: Now choose the color of font which we want to change. And the selection of color will be starting with vb as per VBA setting. Here, we are selecting Red as shown below.

Code:

Sub VBA_ExitSub2()

Range("C3").Select
Selection.Font.Color = vbRed

End Sub

Step 5: Now compile the entire the code in one go as the code is quite small and run it. We will see the color of the font at cell C3 is now changed to Red color from default Black.

Step 6: Now to apply the Exit Sub here in font color change code, put the statement of Exit Sub before the Range cells selection line of code or between range cell selection (1^st) and Font color (2^nd) as shown below.

Code:

Sub VBA_ExitSub2()

Range("C3").Select

Exit Sub

Selection.Font.Color = vbRed

End Sub

Step 7: Now again run the code. This time we will notice that the font color of text at cell C3 doesn’t get changed to Red color as it changed earlier before placing Exit Sub statement.

Step 8: We can convert the added Exit Sub statement into text by inserting a single inverted quote as shown below. By doing this, it will get converted into text. After that, the color of that line will be get changed to Green color.

Code:

Sub VBA_ExitSub2()

Range("C3").Select

'Exit Sub

Selection.Font.Color = vbRed

End Sub

Pros of VBA Exit Sub

• It saves time in re-writing a code again and again.
• We can skip the portion of code which we don’t want to run.
• It is applicable in all types of functions.
• This is quite useful when working on big lines of code where we need to skip or jump some certain line of code.

Things to Remember

• VBA Exit Sub works with all kind of codes and function but it works better with all kind of Loop, especially DO-WHILE Loop.
• It jumps out from the portion of code and running only that much code which is before Exit Sub.
• Exit and End Sub, both are different.
• It skips and jumps out of the code without running it completely. And half run code doesn’t give any proper output.

Recommended Articles

This is a guide to VBA Exit Sub. Here we discuss how to use Excel VBA Exit Sub along with few practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA While Loop
2. VBA Remove Duplicates
3. VBA Data Types
4. VBA Sleep