Complete Tutorial – Sub Procedure in VBA

Sub Procedure is one of the essential fundamentals to start learning VBA. Today, we are going to learn from scratch about what Sub Procedures are, How to create a Sub Procedure, Scope of Sub Procedure. This article also teaches us to pass parameters to a Procedure as well as to call one Sub Procedure from another Sub Procedure.

So let us begin.

What are Sub Procedures?

Sub Procedures contain the VBA block of code in the form of statements. A Sub Procedure does not return a value. We can pass parameters to a Sub Procedure.

However, every Sub Procedure has a unique task that it is intended to complete through VBA code.

• A Sub Procedure is enclosed between Sub and End Sub keywords.
• We can define the name of the Sub procedure after the Sub keyword.
• There is an opening and closing parenthesis indicating the beginning of the procedure code after it.

Here we have inserted a module and we have a Sub Procedure named ExcelUnlocked.

You cannot use space in the name of sub procedure. However, you can use an underscore instead.

Inserting a Sub Procedure

Follow these steps to write a Sub Procedure in VBA.

• Open VBE by pressing Alt and F11 keys together.
• Insert a new module from the Insert tab.

• Now again go to the Insert tab on the ribbon and click on Procedure.

• Consequently, this opens the Add Procedure dialog box. Enter the name of the Procedure as Sub. Make sure that the type is Sub and the Scope is Public.

• The checkbox at the end is to make all local variables within the Sub Procedure to declared as static variables by default. Leave it unchecked for now. Click on the Ok button.

This would insert the Sub Procedure where you can write the VBA code.

Scope of a Sub Procedure

Scope means in which part of the workbook we can access or call the Sub Procedure. A Sub Procedure can be Private or Public. Let us see what that means.

• Private Sub Procedure:- A Private Sub Procedure can be called only by the procedures of the current module in which it has been written. We cannot call a Private Sub Procedure from other modules of the VBA project.
• Public Sub Procedure:- A Public Sub Procedure can be called from any module of the VBA project, It has a Project level scope and it can be called from any module of our project.

Moreover, you can learn how to call public and private Sub Procedures in VBA from here.

Declaring Public and Private Sub Procedure

A Sub Procedure is Private by default. Following is the syntax to write a Private Sub Procedure.

Private Sub sub_name()
'code 
End Sub

It is optional to write Private. We can also write it as,

Sub sub_name()
'code
End Sub

Moreover, a Public Sub Procedure can be declared using the Public Keyword.

Public Sub sub_name()
'code
End Sub

Calling and Called Sub Procedure

We can run a Sub Procedure directly from the VBA editor. The other way is to execute a Sub Procedure from the other sub Procedure. When we call a Sub Procedure (A_Calling) from another Sub Procedure (B_Calling), then A_Called is the Called Sub Procedure while B_Calling is the calling Sub Procedure. For instance, look at this code below.

Sub B_Calling()
MsgBox "This is calling Sub Procedure"
A_Called      'calling the A_Called sub procedure
End Sub
Sub A_Called()       
MsgBox "This is called Sub Procedure"       'definition of A_Called sub procedure
End Sub

The highlighted line A_Called is calling the Sub Procedure using VBA. When you run the B_Calling Sub Procedure code from the VBA editor then,

• Move the cursor anywhere in between the B_Calling procedure. Press F8 to run the calling Sub Procedure line by line.
• The first line of code printing the message is executed and we get the message “This is the Calling Sub Procedure”
• B_Calling calls the A_Called Sub Procedure from the B_Calling Sub Procedure and then the control goes to A_Callled.
• Sub Procedure A_Called is executed. We get the message “This is the Called Sub Procedure”
• After the execution of A_Called is completed, the control shifts back to Sub Procedure B_Calling.

You can use the following workbook to practice the above code.

However, If you want to extend your learning then you can learn about passing parameters to a Sub Procedure or Function from here.

This brings us to an end.

Thank you for reading.