SUB in VBA is also known as a subroutine or a procedure which contains all the code, Sub statement 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, substatement can be both public and private and the name of the subprocedure is mandatory in VBA.
VBA Sub Procedure
Sub means Sub Procedure in VBA. Sub Procedures are used to perform a specific set of task 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 Sub Procedures in Excel VBA?
We can write this subprocedure in any module of the Visual Basic editor.
In order to write VBA sub procedure codes, it is important to understand the structure of the sub procedure. Below is the structure
Sub [Name of the Procedure] (Parameters) [What needs to be done?]) End Sub
To start off the VBA sub procedure 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 VBA Sub Procedure we don’t usually have parameters inside the parenthesis.
After writing the VBA Sub procedure name we need to write the tasks needs to be done in VBA language.
Then comes the End statement i.e. End Sub.
Simple Subroutine in Excel VBA
Now take a look at the simple VBA 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 enter key it will automatically apply the end statement.
Now we can see two things here one is the start of sub procedure and another is the end of the sub procedure. 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.
Simple Subroutine Task in VBA
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 sub procedure 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? 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 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 Subroutine in VBA
We have two more types in VBA Sub procedure one is Public Sub Procedure and the second one is Private Sub Procedure.
The words “Public” & “Private” are accessibility modifiers which 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 Public Code which is there in Module 1.
Now in Module 2 also I can use this subprocedure.
Action1 is the name of the sub procedure we have used in Module 1.
In Module 2 I have mentioned it as “Call Action1”. This means when you run the sub procedure it will execute the sub procedure Action1 from Module 1.
Private Sub Procedures cannot be accessed from a different module, we need to access 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 –