Excel VBA Subroutine

Data Flair

3 years ago

FREE Online Courses: Knowledge Awaits – Click for Free Access!

What is a Subroutine in VBA?

A code snippet which performs the specified task provided in the code and does not return a result. This is known as subroutine in vba. Subroutines are widely used to maintain the code simpler and to break the larger code into smaller modules for better management. There is also an advantage that the subroutines can be recalled anywhere in the program for multiple times also.

For example, if the user has created an user interface for collecting the user input data, then the user can create a subroutine which clear the input value of the user in the text boxes. The call of subroutine is appropriate as the user does not want to return the results.

Why use Subroutines?

1. Break code into small modules

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.

2. Reusability of the code

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.

3. Subroutines and functions are self-documenting

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.

Rules of naming Subroutines and Functions

Before naming the subroutines and functions, follow the rules:

A letter or an underscore in the beginning of a name is a good start.
Special characters or numerics such as 1, !, @, &, ., # are not encouraged in the subroutine function name.
The name of the sub routine should not be a reserved keyword as the compiler uses it for other specific tasks.
There should not be blank spaces in the function name.

The user will need to enable the Developer tab in Excel to work along in VBA.

How to Enable the Developer Tab?

To enable the developer tab in Excel:

Under the file tab, choose the options from the left side of the panel.
Select the customize ribbon label from the left side panel of the dialog box.
Select the developer checkbox and finally press the OK button.

Syntax

Private Sub SubRoutine_Name (ByVal argument_1 As Integer, ByVal argument_2 As Integer)
    //code 
End Sub

Syntax explanation

Private Sub SubRoutine_Name(..):

Here the sub declares a subroutine named “SubRoutine_Name” and starts the body of the subroutine. To specify the scope of the subroutine, the keyword “private” is used.

ByVal argument_1 As Integer, ByVal argument_2 As Integer):

This declares two parameters of integer data type named argument_1 and argument_2

End Sub:

This line of code represents the end of the subroutine body.
Now lets see how to program and execute this Sub Procedure.

How to Call Sub in VBA?

The below steps are the processes on how to call sub in vba.

Step 1: Designing the user interface and setting up the properties as per the requirement.

Design the user interface by setting the name properties to btnDisplayAddition and the caption to DataFlair_SubRoutine.

Your GUI appears as follows

Step 2: Adding the subroutine module in the program

By clicking on the view code option under the developer tab, the user can open the code window.
Add the following subroutine code in the VBE editor

Code:

Private Sub displayAddition(ByVal a As Integer, ByVal b As Integer)
MsgBox a + b
End Sub

Code Explanation:
Private Sub displayAddition – This declares a private subroutine “DataFlair_SubRoutine” that accepts two integer parameters.

ByVal a As Integer, ByVal b As Integer – This declares two parameter variables and those are ‘a’ and ‘b’.

MsgBox a + b – This calls the MsgBox built-in function to display the sum of the two variables which are passed as parameters.

Step 3: Address the client event code to button.

By clicking on the view code option under the developer tab, the user can open the code window.

Add the following code in the code editor for the click event of the btnDisplay_Addition command button.

Code:

Private Sub btnDisplay_Addition( )
    displayAddition 10, 20
End Sub

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.

Press the save button to save the changes and close the editor window.

Step 4: Application Testing for subroutine.

To test the application, the user has to follow the steps:

Step 1: Click on the Developer tab.

Note: Ensure the design mode is off. If it is on, then the application will not respond.

Step 2: Click on the Design Mode again to turn it off(if they were on).

Step 3: Place the cursor on the newly created button.

The following output appears.

Calling Procedures

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.

Code:

Function findSqr(Num As Double)
  Num = 2
  Sqr Num
  MsgBox (Num)
End Function

Public Sub Sqr(Num As Double)
MsgBox (Num)
End Sub

Code Explanation: To calculate the square of a number, the “Sqr” sub procedure is called.

When the user runs the code, the following output appears.

VBA Call/ Run Sub from another Subroutine:

Lets see how to call a sub procedure from within another sub procedure in VBA.
Let’s create two subroutines:

Subroutine-1:

Sub DataFlair_Routine1()
DataFlair_Routine2
End Sub

Subroutine-2:

Sub DataFlair_Routine2()
   MsgBox "You have opened one of the new articles from the website!"
End Sub

Explanation: When we run the sub procedure of DataFlair_Routine1(), it calls the DataFlair_Routine2.

Hope you observed that the message box of the DataFlair_Routine2 function appears in the DataFlair_Routine1 function.
Note: The user can call multiple sub procedures from another sub procedure and there is no limit or restrictions for it.

Using the Call Statement:

The user can also use the ‘Call’ 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.

Code:

Subroutine-1:

Sub DataFlair_Routine1()
Call DataFlair_Routine2
End Sub

Subroutine-2:

Sub DataFlair_Routine2()
   MsgBox "You have opened one of the new articles from the website!"
End Sub

Save and run the code, the following output appears:

Hope you observed that there is no effect even when we added the keyword ‘Call’ in front of the subroutine calling statement.

Calling a Subroutine with Arguments

The user can also call a subroutine with arguments.

Code:

Subroutine-1:

Sub DataFlair_Routine1()
DataFlair_Routine2(“Akkshaya”)
End Sub

Subroutine-2:

Sub DataFlair_Routine2(username As String)
   MsgBox "You have opened one of the new articles from the website," & " & username"
End Sub

Save and run the code, the following output appears:

Hope you observed that the user name is also shown in the message box.

Calling a Sub with Named Arguments

When the user names the arguments, then it’s not necessary to pass the arguments in the same order to the subroutines.

Code:

SubRoutine – 1:

Sub DataFlair_Routine1()
DataFlair_Routine2 strInfo:="Have Fun reading it!", username:="Akkshaya"
End Sub

SubRoutine – 2:

Sub DataFlair_Routine2(username As String, strInfo As String)
   MsgBox "You have opened one of the new articles from the website," & " & username & vbCrLf & strInfo"
End Sub

Save and run the code, the following output appears:

Hope, you observed that the arguments were accepted even when they were not passed in order.

Sub in a Sub:

The user can use a sub to execute another sub also. Let’s see a sample of it.

Code:

A Sub can be used to execute another Sub:

A Sub can be used to execute another Sub:
   Sub sub_menu()
        sub_def1
        sub_def2
    End Sub
    Sub sub_def1()
        MsgBox "It is a sub which executes a sub"
    End Sub
    Sub sub_def2()
        MsgBox "It is a sub executing this sub also"
    End Sub

Let’s see a sample where the sub accepts arguments also.
Code:

Sub sub_def()
     sub_arg(100)
 End Sub
 Sub sub_arg(x as Integer)
     MsgBox x
 End Sub

When the user runs the code, the following output appears.

Multiple Arguments:

Code:

 Sub sub_def()
      Dim fruit As String
      Dim cost As Integer

      fruit= “Mango”
     Cost = 45
     Call sub_argument (fruit, cost) 
  End Sub
  Sub sub_argument(fruit as String, cost as Integer)
      MsgBox(“Fruit cost is:”,cost)
End Sub

Code Explanation: Here, the fruit and cost are two different arguments. When the user wants more than one argument into a sub, the user can use the call instruction before the name of the sub they want to call.

When the user runs the code, the following output appears.

Note: The user can execute a sub with multiple arguments by omitting the call instruction and the parentheses also.

Types of Sub

There are two types of sub available and they are Private and Public:

Public Sub
Private Sub

Public Sub:

The public sub creates a public procedure which an user can access by any procedure of any module and it is displayed in the macros.

Private Sub:

The private sub creates a private procedure which an user can access only by procedures of the same module and it is not displayed in the macros.

Note: A sub which is not defined is considered as a public sub in VBA.

Key Differences between Excel sub and Function

Sub	Function
Sub performs a task but it does not provide an output value.	It performs a task and it also provides an output value.
It can be called anywhere and multiple times in the module.	The functions in the module are referred to by a variable.
We cannot use the sub as formulas in the spreadsheet directly.	We can use the functions as formulas in the spreadsheet directly.
The user must enter the values before to get the result of the sub.	It can perform repetitive tasks and it can also return a value.
VBA sub can be executed by the excel users.	VBA functions cannot be executed by the excel users.

Pros of Excel VBA Call Sub

When the user uses subroutines in the code, it saves a lot of time by not repeating the same code over and over again.
It stores in the same excel worksheet and thereby it reduces the size of the file as well.

Things to Remember

To see the sequential run of multiple modules of code, make use of the message box.
Press F8 to compile the bigger codes as this may help the user to identify the error part.
The file should be saved in the Macro Enabled Excel Format and this retains the written code.
The Call keyword before the End statement will run the first code and the sub procedure will run the second code.

Note:

The user can execute the macro of another sub procedure without using the keyword “Call” also.
Mentioning the name of the macro will be more than efficient.
To make the code better and understandable, make use of the call keyword always.

Summary:

Subroutine performs a specific task without returning a value after execution of the code.
Reusability of code and managing the code are easy if the user uses subroutine.
It breaks down the larger code to smaller modules.