VBA Sub

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

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.

VBA Sub

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Sub (wallstreetmojo.com)

How to write VBA Sub Procedures?

You can download this VBA Sub Procedure Template here – VBA Sub Procedure Template

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

Sub [Name of the Procedure] (Parameters)
 
 [What needs to be done?])

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.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

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.

Example 1

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

Example 1-1

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

Example 1-2

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.”

Example 1-3

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
Example 2

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

Code:

Sub Action1()

  Range(

End Sub
Example 2-1

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
VBA Sub Example 2-2

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
VBA Sub Example 2-3

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
VBA Sub Example 2-4

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 keyExcel Shortcut KeyAn Excel shortcut is a technique of performing a manual task in a quicker way.read more by placing the cursor inside the Macro code.

VBA Sub Example 2-5

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

VBA Sub Example 2-6

Types of VBA Subroutine

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

VBA Public sub
VBA Private sub

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.

vba sub module1

Now, in Module 2 also, we can use this sub procedure.

vba sub module2

Action1 is the name of the sub procedure we have used in Module 1.

In Module 2, we have mentioned it as “Call Action1”. It means when you run the sub procedure, it will execute the subprocedure Action1 from Module 1.

One cannot access Private Sub Procedures from a different module. We need to access them from the same module only.

Recommended Articles

This article has been a guide to VBA Sub. Here, we learn how to invoke a sub procedure in Excel VBA and types of subroutines, along with examples and downloadable templates. Below are some useful articles related to VBA: –