VBA Sub

Excel VBA Sub Procedure

SUB in VBA is also known as a subroutine or a procedure which contains all the code, it is an inbuilt statement in VBA and when used it automatically gives us the statement of end sub and the middle portion is used for coding, sub statement can be both public and private and the name of the subprocedure 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 kind of value.

VBA Sub

How to write VBA Sub Procedures?

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

In order to write sub procedure codes, it is important to understand the structure of the subprocedure. Below is the structure

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

End Sub

To start off the subprocedure, we need to use the word “Sub,” and we need to give a name to the Sub as a procedure name. Procedure Name is nothing but our macro name. In the VBA subprocedure, we don’t usually have parameters inside the parenthesis.

After writing the Sub procedure name, we need to write the tasks that need to be done. 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.

Example 1

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

Example 1-1

Step 3: After giving the name to the subprocedure, 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” & “Tail.”

Example 1-3

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

Example #2 – Simple Subroutine Task

Ok, now we will see how to perform some simple action here.

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

Step 1: Start the subprocedure by using the above methods.

Code:

Sub Action1()

End Sub
Example 2

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

Code:

Sub Action1()

  Range(

End Sub
Example 2-1

Step 3: It is asking what is the cell1 you want to refer to? 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 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 would see many options, and these options are called an IntelliSense list, which predicts based on what you’re typing. This 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 are done.

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

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

VBA Sub Example 2-5

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

VBA Sub Example 2-6

Types of VBA Subroutine

We have two more types in Sub procedure one is Public Sub Procedure, and the second one is Private Sub Procedure.

VBA Public sub
VBA Private sub

The words “Public” & “Private” are accessibility modifiers that allow us to use them sub procedures differently.

  • Public Sub Procedure allows us to use the procedure in all the modules of the workbook.
  • Private Sub Procedure allows us to use the procedure only in the current module, nowhere else.

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 I can use this subprocedure.

vba sub module2

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

In Module 2, I have mentioned it as “Call Action1”. This means when you run the subprocedure, it will execute the subprocedure Action1 from Module 1.

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

Recommended Articles

This has been a guide to VBA Sub. Here we learn how to invoke a Sub Procedure in Excel VBA and types of subroutine along with examples & downloadable templates. Below are some useful articles related to VBA –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>