Sub Procedure in Excel VBA

Last Updated : 09 Sep, 2025

A Sub Procedure in VBA (Visual Basic for Applications) is a block of code that performs an action. It starts with a Sub statement and ends with an End Sub statement. Sub procedures are commonly used to manipulate data in Excel sheets, including working with tables or pivot tables.

The name of a sub procedure must always be followed by parentheses.

Why Use Sub Procedures?

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 Procedures

It can start with a letter or an underscore(_) but it cannot start with a number or a special character.
It cannot start with a number or special character.
No spaces are allowed 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 — Step1: Starting macro recording through the View > Macros > Record Macro option.”

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 — Step2: “Setting the macro name and selecting the storage location within the workbook.”

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

Clicking-edit — Step3: Using Alt+F8 to edit the recorded macro in the VBA editor.”

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

VBA-window-appears — Step3: Automatically generated Sub Procedure displayed in the VBA editor.”

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 — Step2: Using Insert > Procedure to create a new Sub procedure manually.”

Step 3: Add Procedure dialog box will appear.

Adding-procedure — Step3: Defining the Sub Procedure name, type, and scope in the Add Procedure dialog box.”

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.

Getting-sub-procedure — Step4: Click ok to get 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 — Step1: Opening Insert under the Developer tab to add a button.”

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

Choosing-command-button — Step2: Choosing the command button from Form Controls.”

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 — Step3: Drawing the command button and assigning a macro in the dialog box.”

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

Clicking-button — Step:4 Click the button to run the assigned macro.”

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

Clicking-alt+f11 — Step5: Press Alt+F11 to view the Button1_Click() sub-procedure.”

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.

Private-sub-procedure — Private Sub procedure: cannot be accessed by other modules.”

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.