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.
How to write VBA Sub Procedures?
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.
Step 2: Now name the macro name or procedure name.
Step 3: After giving the name to the subprocedure, 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” & “Tail.”
Between the head and tail of the macro, we need to write our code to perform some task.
4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
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.
Sub Action1() End Sub
Step 2: To access the cell A1 we need to use the word RANGE.
Sub Action1() Range( End Sub
Step 3: It is asking what is the cell1 you want to refer to? In this case, it is an A1 cell.
Sub Action1() Range ("A1") End Sub
Step 4: We need to insert the value “Excel VBA,” so select VALUE property by putting a dot after the range.
Sub Action1() Range("A1").Value End Sub
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.”
Sub Action1() Range("A1").Value = "Excel VBA" End Sub
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.
As soon as you run the code, you will get the value “Excel VBA” in cell A1.
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.
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.
Now in Module 2 also I can use this subprocedure.
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.
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 –