A subroutine program can contain multiple thousand source code lines in it. The user can make use of subroutines and break down the program into smaller modules, which helps to decrease the complexity of the code.<\/p>\n
2. Reusability of the code<\/strong><\/p>\n Instead of writing the code every time, the user can call the subroutine program as per the requirement in the module. If the program contains the repetitive codes then the user can create a function or subroutine which helps in reusing the code at different modules.<\/p>\n 3. Subroutines and functions are self-documenting<\/strong><\/p>\n They are referred to as self documenting as the user can say what the program performs just by looking at the name of the sub routine.<\/p>\n Before naming the subroutines and functions, follow the rules:<\/p>\n The user will need to enable the Developer tab in Excel to work along in VBA.<\/p>\n To enable the developer tab in Excel:<\/p>\n Syntax<\/strong><\/p>\n Syntax explanation<\/strong><\/p>\n Private Sub SubRoutine_Name(..):<\/strong><\/p>\n Here the sub declares a subroutine named \u201cSubRoutine_Name\u201d and starts the body of the subroutine. To specify the scope of the subroutine, the keyword \u201cprivate\u201d is used.<\/p>\n ByVal argument_1 As Integer, ByVal argument_2 As Integer):<\/strong><\/p>\n This declares two parameters of integer data type named argument_1 and argument_2<\/p>\n End Sub:<\/strong><\/p>\n This line of code represents the end of the subroutine body. The below steps are the processes on how to call sub in vba.<\/p>\n Step 1:<\/strong> Designing the user interface and setting up the properties as per the requirement.<\/p>\n Your GUI appears as follows<\/p>\n Step 2:<\/strong> Adding the subroutine module in the program<\/p>\n Code:<\/strong><\/p>\n Code Explanation:<\/strong> ByVal a As Integer, ByVal b As Integer – This declares two parameter variables and those are \u2018a\u2019 and \u2018b\u2019.<\/p>\n MsgBox a + b – This calls the MsgBox built-in function to display the sum of the two variables which are passed as parameters.<\/p>\n Step 3:<\/strong> Address the client event code to button.<\/p>\n Add the following code in the code editor for the click event of the btnDisplay_Addition command button.<\/p>\n Code:<\/strong><\/p>\n The first part of the code lines are subroutine code and the second part of the code lines are the click event code which calls the subroutine from the command button.<\/p>\n Press the save button to save the changes and close the editor window.<\/p>\n Step 4:<\/strong> Application Testing for subroutine.<\/p>\n To test the application, the user has to follow the steps:<\/p>\n Step 1: Click on the Developer tab.<\/p>\n Note: Ensure the design mode is off. If it is on, then the application will not respond.<\/p>\n Step 2: Click on the Design Mode again to turn it off(if they were on).<\/p>\n Step 3: Place the cursor on the newly created button.<\/p>\n The following output appears.<\/p>\n The user can make a call from a function to invoke a procedure in the code. The user cannot do it in the same way they did it for function as subroutine does not return a value.<\/p>\n Code:<\/strong><\/p>\n Code Explanation:<\/strong> To calculate the square of a number, the \u201cSqr\u201d sub procedure is called.<\/p>\n When the user runs the code, the following output appears.<\/p>\n Lets see how to call a sub procedure from within another sub procedure in VBA. Explanation: When we run the sub procedure of DataFlair_Routine1(), it calls the DataFlair_Routine2.<\/p>\n Hope you observed that the message box of the DataFlair_Routine2 function appears in the DataFlair_Routine1 function. The user can also use the \u2018Call\u2019 keyword in front of the procedure name. It makes the code easier to understand and it also has no effect on how the code runs and where the code is stored.<\/p>\n Code:<\/strong><\/p>\n Save and run the code, the following output appears:<\/p>\n Hope you observed that there is no effect even when we added the keyword \u2018Call\u2019 in front of the subroutine calling statement.<\/p>\n The user can also call a subroutine with arguments.<\/p>\n Code:<\/strong><\/p>\n Save and run the code, the following output appears:<\/p>\n Hope you observed that the user name is also shown in the message box.<\/p>\n When the user names the arguments, then it\u2019s not necessary to pass the arguments in the same order to the subroutines.<\/p>\n Code:<\/strong><\/p>\n Save and run the code, the following output appears:<\/p>\n Hope, you observed that the arguments were accepted even when they were not passed in order.<\/p>\n The user can use a sub to execute another sub also. Let\u2019s see a sample of it.<\/p>\n Code:<\/strong><\/p>\n A Sub can be used to execute another Sub:<\/p>\n Let\u2019s see a sample where the sub accepts arguments also. When the user runs the code, the following output appears.<\/p>\nRules of naming Subroutines and Functions<\/h3>\n
\n
How to Enable the Developer Tab?<\/h3>\n
\n
![\"developer](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/09\/dev-tab.png\")
<\/a><\/p>\nPrivate Sub SubRoutine_Name (ByVal argument_1 As Integer, ByVal argument_2 As Integer)\r\n \/\/code \r\nEnd Sub \r\n<\/pre>\n
\nNow lets see how to program and execute this Sub Procedure.<\/p>\nHow to Call Sub in VBA?<\/h3>\n
\n
<\/a><\/p>\n\n
Private Sub displayAddition(ByVal a As Integer, ByVal b As Integer)\r\nMsgBox a + b\r\nEnd Sub\r\n<\/pre>\n
\nPrivate Sub displayAddition – This declares a private subroutine \u201cDataFlair_SubRoutine\u201d that accepts two integer parameters.<\/p>\n\n
Private Sub btnDisplay_Addition( )\r\n displayAddition 10, 20\r\nEnd Sub\r\n<\/pre>\n
<\/a><\/p>\n<\/a><\/p>\nCalling Procedures<\/h3>\n
Function findSqr(Num As Double)\r\n Num = 2\r\n Sqr Num\r\n MsgBox (Num)\r\nEnd Function\r\n\r\nPublic Sub Sqr(Num As Double)\r\nMsgBox (Num)\r\nEnd Sub\r\n<\/pre>\n
<\/a><\/p>\nVBA Call\/ Run Sub from another Subroutine:<\/h3>\n
\nLet’s create two subroutines:<\/p>\nSubroutine-1:<\/h4>\n
Sub DataFlair_Routine1()\r\nDataFlair_Routine2\r\nEnd Sub\r\n<\/pre>\n
Subroutine-2:<\/h4>\n
Sub DataFlair_Routine2()\r\n MsgBox \"You have opened one of the new articles from the website!\"\r\nEnd Sub\r\n<\/pre>\n
<\/a><\/p>\n
\nNote: The user can call multiple sub procedures from another sub procedure and there is no limit or restrictions for it.<\/p>\nUsing the Call Statement:<\/h3>\n
Subroutine-1:<\/h4>\n
Sub DataFlair_Routine1()\r\nCall DataFlair_Routine2\r\nEnd Sub\r\n<\/pre>\n
Subroutine-2:<\/h4>\n
Sub DataFlair_Routine2()\r\n MsgBox \"You have opened one of the new articles from the website!\"\r\nEnd Sub\r\n<\/pre>\n
<\/a><\/p>\nCalling a Subroutine with Arguments<\/h3>\n
Subroutine-1:<\/h4>\n
Sub DataFlair_Routine1()\r\nDataFlair_Routine2(\u201cAkkshaya\u201d)\r\nEnd Sub\r\n<\/pre>\n
Subroutine-2:<\/h4>\n
Sub DataFlair_Routine2(username As String)\r\n MsgBox \"You have opened one of the new articles from the website,\" & \" & username\"\r\nEnd Sub\r\n<\/pre>\n
<\/a><\/p>\nCalling a Sub with Named Arguments<\/h3>\n
SubRoutine – 1:<\/h4>\n
Sub DataFlair_Routine1()\r\nDataFlair_Routine2 strInfo:=\"Have Fun reading it!\", username:=\"Akkshaya\"\r\nEnd Sub\r\n<\/pre>\n
SubRoutine – 2:<\/h4>\n
Sub DataFlair_Routine2(username As String, strInfo As String)\r\n MsgBox \"You have opened one of the new articles from the website,\" & \" & username & vbCrLf & strInfo\"\r\nEnd Sub\r\n<\/pre>\n
<\/a><\/p>\nSub in a Sub:<\/h3>\n
A Sub can be used to execute another Sub:\r\n Sub sub_menu()\r\n sub_def1\r\n sub_def2\r\n End Sub\r\n Sub sub_def1()\r\n MsgBox \"It is a sub which executes a sub\"\r\n End Sub\r\n Sub sub_def2()\r\n MsgBox \"It is a sub executing this sub also\"\r\n End Sub\r\n<\/pre>\n
\nCode:<\/strong><\/p>\nSub sub_def()\r\n sub_arg(100)\r\n End Sub\r\n Sub sub_arg(x as Integer)\r\n MsgBox x\r\n End Sub\r\n<\/pre>\n