Excel Vba Stop Code From Continuing

Sub procedures are nothing but functions that can be run directly in a VBA module. You're not required to call a sub procedure anywhere else within the module (just like with a function). But they are also a part of the module that can be run directly using the F5 key (unlike a function).

Situations to Stop Execution

There may be situations where a program's input is insufficient to run it completely. In this case, it is a good idea to display a message to the user on a msg box rather than running it completely until the end, which would be a waste of time. For example, if user inputs a string/character in the place of a number for age, the program can display an error message stating that the input is an invalid format and stop execution instead of running it until the end.

Exit Sub Statement

Here is the VBA statement that forces the program's control to the "End sub" statement of the sub procedure. Syntax:

Exit Sub

How Does Exit Sub Work?

Imagine there are 50 lines of code in a sub procedure and "Exit sub" is the statement in the 30^th line wrapped in a condition. During runtime, if the condition is met, the control is transferred to the "Exit sub" statement which in turn transfers the control to the "End sub" statement of the same procedure. The other 20 lines of code are skipped without being executed.

Example 1: A Simple Example

In a sub procedure we have written some simple code to print numbers from 1 to 50 in a loop.

After printing each number, we check if the printed number is 25. If yes, we want to exit the entire procedure and not print anything further.

Sub print_numbers()  For i = 1 To 25  Debug.Print i  If i = 25 Then     Exit Sub  End If Next i  End Sub        

Output:

In the same example, let us print the numbers after the condition.

Sub print_numbers()  For i = 1 To 25    If i = 25 Then     Exit Sub  End If    Debug.Print i Next i  End Sub        

Now the output will contain numbers only until 24 as the counter/iterator variable value is 25 when the condition is met, and sub procedure is exited before printing the value.

You can try out similar programs and execute them using the debugging controls like breakpoints and F8 key to see how the control flows during the execution.

Example 2: A Bigger Example

This is another program with exit sub in two areas. Before looking deep into the area of usage, let us understand what the program does.

This is a program which rewards a bicycle for female children over 10 years old who have a sister. In order to check these criteria, it receives some information from the user. The inputs received are age, gender of the user, and whether the candidate has a sister or not.

If any input/input format is found invalid, a message box is displayed, and the sub procedure is exited. Now speaking of the two areas where exit sub is used:

1. If the gender is not in the expected single character format, procedure exits.
2. If the age is not in the numeric format, the program exists. For this, the received age value is converted to a number and validated for the format.

Sub exitsub_if_demo()  ' declare variables Dim str_age, int_age, sister, gender  ' get the age from the user str_age = InputBox("Please enter your age")  ' convert age to an integer int_age = CInt(str_age)  'validate that the input is a number- Level 1 If IsNumeric(int_age) Then      ' Level -2 validate if age is greater than 10     If int_age >= 10 Then              'receive input - sister from te user         sister = InputBox("Do you have a sister? ( Enter only 'Yes' or 'No' ) ")          ' Level -3 - validate if the candidate has a sister         If sister = "Yes" Then                          ' receive input on gender from user             gender = InputBox("Enter your gender? ( Enter only 'M' for Male or 'F' for Female) ")                          ' check if the input for gender is a valid single character.             If gender <> "M" And gender <> "F" Then                 MsgBox "Your input is invalid. Please try again! "                 Exit Sub             End If              ' Level - 4 - validate if the candidate is a female             If gender = "F" Then                 ' display success msg                 Debug.Print "Congratulations! You will receive a cycle! Stay back to bag the reward! "             Else                 ' Either not a girl child / invlid input                 Debug.Print "Sorry! This reward program is only for Girl children!"             End If         Else             ' Either not having a sister / invalid input             Debug.Print "Since you do not have a sister, you are not eligible for the gift."         End If     Else         ' Age is less than 10 yrs. Hence not eligible.         Debug.Print "You are less than 10 yrs old and hence not eligible for the gift. "     End If Else      Debug.Print "Age is invalid. Please try again. "          Exit Sub End If  End Sub        

Now let us run this program with different combinations of input.

When we input age=6, the program stops printing the statement below. It does not proceed further.

Output

You are less than 10 years old and hence not eligible for the gift.

When age=11, and answer to question "Do you have a sister?" is "No," the program stops displaying the statement below and does not execute the rest of the code.

Output

Since you do not have a sister, you are not eligible for the gift.

If input age=15, "Do you have a sister?" = "Yes" and "Gender" = "M"

Output:

Sorry! This reward program is only for girl children!

And finally, here is the positive flow in which the program runs completely with all expected inputs.

Input values age=14, "Do you have a sister" = "Yes," "Gender" = "F"

Output

Congratulations! You will receive a bicycle! Stay back to bag the reward!

Did you notice something in the above program? The nested if conditions are designed in such a way that the program stops as it prints a statement. So, the "Exit Sub" statement is mostly not required here. However, in the place of validating the gender input by the user, it does play a vital role.

Example 3: Exit Sub with On Error Statement

This is a program where the well-known "On error" statement is used to redirect the program's control to an error handling label in case an error is encountered.

Here's a division program where we get the dividend and divisor from the user and calculate the quotient. The program will run smoothly until the normal exit sum before the label Errorhandler as long as zero is not entered as an input. In the event the user enters "0" as a divisor, the control hits errorhandled in order to handle the "division by zero" error as instructed in the "On error" statement.

Sub onerrorexitsub()  ' declare an integer variable Dim num1, num2, num3 As Integer   ' Type the on error state so that whereever an error is encountered during execution, the control 4ris redirected to the specified label On Error GoTo Errorhandler  'receive the two numbers to be used for division  num2 = InputBox("Enter the dividend of the division problem") num3 = InputBox("Enter the divisor of the division problem")   ' try a division by zero - this is demo to create an error num1 = Cint ( num2 ) / Cint ( num3 ) ' Display num1 to check if it has some error value now. This statement will take the control to the label in case the value has an error. Debug.Print  "The result of the division is - quotient = " &; num1 Exit Sub Errorhandler:     Debug.Print  "There was an error, so exited the Sub procedure"     Exit Sub End Sub        

Input values:

If num2=45 and num3=5, the output statement is

Output:

The result of the division is – quotient = 9

Explanation:

No error was encountered, so program ran smoothly until the first exit sub statement and not any further.

Input values:

If num2=13 and num3=0, the output statement is

Output:

There was an error, so exited the Sub procedure

Explanation:

A division by zero error was encountered. So, the control was moved to the errorhandled that displayed this output statement and exited the sub procedure.

Conclusion

In bigger programs with a lengthy flow of actions and in loops & conditions, the "Exit sub" statement is very useful.

This statement helps the control avoid executing unnecessary lines of code when not required — because it might cause unexpected outcomes in case of underlying coding bugs.

Also, it acts as a great timesaver.

sangeranswashe.blogspot.com

Source: https://software-solutions-online.com/vba-onerror-exit-sub/

Share this post