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.
- We can set the Sub Procedure name in the Name box, here the name of the Sub Procedure is Test_Procedure.
- We have to select Sub in the Type to create a Sub Procedure.
- 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
- 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.