Open In App

Sub Procedure in Excel VBA

Improve
Improve
Like Article
Like
Save
Share
Report

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

Selecting-record-macros

 

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.

Storing-macro-name

 

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

Clicking-edit

 

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

VBA-window-appears

 

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.

Clicking-procedure

 

Step 3: Add Procedure dialog box will appear.

Adding-procedure

 

  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.

Getting-sub-procedure

 

Creating a Button to Run Macro

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

Clicking-insert-button

 

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

Choosing-command-button

 

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.

Assign-macro-tab

 

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

Clicking-button

 

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

Clicking-alt+f11

 

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

Subroutine-created

 

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.
Private-sub-procedure

 

  • 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.
Public-sub-procedure

 

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

Sub-procedure-in-module-2

 


Last Updated : 16 Nov, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads