VBA Sub

Last Updated :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

Excel VBA Sub Procedure

SUB in VBA is also known as a subroutine or a procedure that contains all the code. It is an inbuilt statement in VBA, and when used, it automatically gives us the statement of the end sub. The middle portion one may use for coding, the sub statement can be both public and private, and the subprocedure's name is mandatory in VBA.

Sub means Sub Procedure in VBA. Sub Procedures are used to perform a specific set of tasks provided in the form of code. It performs only the task mentioned in the code as per the VBA language but does not return any value.

Excel VBA Sub Procedure

How to write VBA Sub Procedures?

It is important to understand the structure of the sub procedure to write sub procedure codes. Below is the structure.

Sub  (Parameters)
 
 )

End Sub

To start the sub procedure, we need to use the word "Sub." We need to give a name to the Sub as a procedure name. Procedure Name is nothing but our macro name. We do not usually have parameters inside the parenthesis in the VBA sub procedure.

After writing the Sub Procedure name, we need to write the tasks that we need to do. Then comes the End statement, i.e., End Sub.

Example #1 - Simple Sub Procedure

Now, take a look at the simple sub procedure writing methods.

Step 1: Start the word “Sub” in the module.

Step 2: Now name the macro name or procedure name.

Step 3: After giving the name to the sub procedure, just hit the enter key. It will automatically apply the end statement.

Now, we can see two things here, one is the start, and another is the end of the subprocedure. For better understanding, we can call it "Head" and "Tail."

Between the head and tail of the macro, we need to write our code to perform some tasks.

Example #2 - Simple Subroutine Task

Now, we will see how to perform some simple actions here.

Assume you want to insert the value "Excel VBA" in cell A1.

Step 1: Start the sub procedure by using the above methods.

Code:

Sub Action1()

End Sub

Step 2: To access cell A1 we need to use the word RANGE.

Code:

Sub Action1()

  Range(

End Sub

Step 3: It asks what cell1 you want to refer to is? In this case, it is an A1 cell.

Code:

Sub Action1()

  Range ("A1")

End Sub

Step 4: We need to insert the value "Excel VBA," so select the VALUE property by putting a dot after the range.

Code:

Sub Action1()

  Range("A1").Value

End Sub

When you start typing the word VALUE, you will see many options. These options are called an IntelliSense list, which predicts based on your typing. It is like how formulas will appear in the worksheet when we start typing the formula.

Step 5: After selecting VALUE, put an equal sign and enter the value in doubles quotes as "Excel VBA."

Code:

Sub Action1()

  Range("A1").Value = "Excel VBA"

End Sub

So, we have completed it.

Now, we need to execute this task. To execute the task, we had a return. So, we need to run this code by pressing the RUN button in the visual basic editor window.

We can press the excel shortcut key by placing the cursor inside the Macro code.

As soon as you run the code, you will get the "Excel VBA" value in cell A1.

Types of VBA Subroutine

We have two more types in sub procedure: Public Sub Procedure and Private Sub Procedure.

The words "Public" and "Private" are accessibility modifiers that allow us to use them differently.

Public Sub Procedure allows us to use the procedure in all the workbook modules.
Private Sub Procedure allows us to use the procedure only in the current module.

For example, look at the below image of the Public Code, which is there in Module 1.