Sub Procedure in Excel VBA

A Sub Procedure is an action that is written in VBA code. It starts with a Sub statement and ends with an End Sub statement. It is used to manipulate the data in an Excel sheet which includes a table present in an Excel sheet or a pivot table present in the Excel sheet. The name of the procedure should be followed by parentheses. Let’s learn why to use sub procedure:

• Converts large piece of codes into small parts so that the computer ignores all kind of complexities that arises because of large codes
• In a program, we have to access the database frequently. In this case, instead of writing the code again and again we can simply create a function to access the database. Reusability of code can be done.

Naming Rules of Sub Procedure

• It can start with a letter or an underscore but it cannot start with a number or a special character.
• It cannot contain any space in the name.
• The name of the subprocedure cannot be a keyword like Private, Sub, End, etc. 

Syntax

Sub name_procedure ()

End Sub

Let’s discuss different ways to create Sub Procedure:

Creating a Sub Procedure with Macro

Step 1: Select View in the Ribbon then click on Macros to select Record Macro

 

Step 2: Change the Macro name according to the user’s need and then select where we want to store the macro, currently, we are storing it in This Workbook.

 

Step 3: To view or run the macro press Alt+F8 and then click on Edit to open the VBA to write Sub Procedure.

 

Then VBA Window will appear with a Sub Procedure name of Test_Procedure

 

Creating a Sub Procedure in VBA window

Step 1: Press Alt+F11 in the main Excel window to the VBE window.

Step 2: Select Insert and then click on Procedure.

 

Step 3: Add Procedure dialog box will appear.

 

1. We can set the Sub Procedure name in the Name box, here the name of the Sub Procedure is Test_Procedure.
2. We have to select Sub in the Type to create a Sub Procedure.
3. We can select the scope of the procedure either Public or Private. The public will allow us to use a procedure in all the modules in the workbook whereas the private will not allow using a sub-procedure of one module in another module
4. If we want to make our local variables public so they will be accessible to the entire module then select the check box of All Local variables as Statics

Step 4: Click on Ok to get Sub Procedure.

 

Creating a Button to Run Macro

Step 1: Select the developer tab and click Insert drop-down list from the control box.

 

Step 2: Choose the command button from the Form Controls Box.

 

Step 3: Draw the command button in the excel sheet then an Assign Macro dialog box will appear where we can select the macro to assign the Button.

 

Step 4: Click on the Button to run the macro.

 

Step 5: Press “Alt+F11” to get the sub-procedure of Button1_Click().

 

Step 6: Subroutine will be created inside the sub-procedure. The following code will insert a value in the Range “B1”.

 

Types of VBA Subroutine

There are two types in Sub procedures:

• Private Sub procedure: The procedure can’t be accessed by other modules in the workbook.

 

• Public Sub procedure: A procedure of one module can be accessed by the procedure of another module in the workbook. In the following code, a Sub procedure Act1()  is present in Module 1.

 

Act1() can be called by another Sub procedure Act2() which is present in Module 2.