Sub Procedures in VBA

A Sub Procedure is a named block of VBA statements that performs a particular task.

A basic Sub Procedure looks like this:

Sub Hello()

    MsgBox "Hello"

End Sub

Here, Hello is the name of the Sub Procedure.

The Sub keyword is used to start a Sub Procedure.

Sub Welcome()

    MsgBox "Welcome"

End Sub

The word Sub tells VBA that a new Sub Procedure is being declared.

The End Sub statement marks the end of a Sub Procedure.

Sub ShowMessage()

    MsgBox "Hello VBA"

End Sub

Everything between Sub and End Sub belongs to that procedure.

The basic structure is:

Sub ProcedureName()

    ' VBA statements

End Sub

The procedure name identifies the procedure, while the statements inside it perform the required task.

A Sub Procedure should have a meaningful name that describes its purpose.

Examples:

Sub CalculateTotal()

End Sub

Sub PrintReport()

End Sub

Sub AddStudent()

End Sub

Meaningful names make VBA programs easier to understand.

Let's create a simple Sub Procedure that displays a message.

Sub Hello()

    MsgBox "Hello Student!"

End Sub

When this procedure runs, Excel displays a message box containing Hello Student!.

Sub Procedures are commonly written inside a standard VBA module.

Steps:

1. Open Excel.
2. Press Alt + F11.
3. Insert a standard module.
4. Type the Sub Procedure.
5. Run the procedure.

A Sub Procedure can be run from the VBA Editor.

Place the cursor inside the procedure and press:

F5

You can also run a macro from Excel's Developer → Macros option.

A Sub Procedure can work with Excel cells.

Sub WriteName()

    Range("A1").Value = "Rahul"

End Sub

When the procedure runs, the text Rahul is placed in cell A1.

A Sub Procedure can perform calculations and place the result in a cell.

Sub CalculateTotal()

    Range("C1").Value = 100 + 200

End Sub

After running the procedure, cell C1 contains:

300

A Sub Procedure can use variables to store temporary values.

Sub StudentMarks()

    Dim marks As Integer

    marks = 85

    MsgBox marks

End Sub

The variable marks stores the value 85.

A Sub Procedure can contain many VBA statements.

Sub StudentDetails()

    Range("A1").Value = "Student Name"
    Range("B1").Value = "Rahul"
    Range("A2").Value = "Marks"
    Range("B2").Value = 85

End Sub

All these instructions are executed as part of the same procedure.

One Sub Procedure can call another Sub Procedure.

Sub MainProgram()

    ShowMessage

End Sub

Sub ShowMessage()

    MsgBox "Welcome to VBA"

End Sub

When MainProgram runs, it calls ShowMessage.

The Call keyword can also be used to call a Sub Procedure.

Sub MainProgram()

    Call ShowMessage

End Sub

Sub ShowMessage()

    MsgBox "Hello VBA"

End Sub

Both direct calling and the Call statement can be used for calling procedures.

A Sub Procedure can receive values called arguments.

Sub ShowName(name As String)

    MsgBox name

End Sub

The procedure receives a name and displays it.

We can pass a value when calling a Sub Procedure.

Sub MainProgram()

    ShowName "Amit"

End Sub

Sub ShowName(name As String)

    MsgBox name

End Sub

Here, Amit is passed to the name argument.

A Sub Procedure can receive more than one argument.

Sub AddNumbers(a As Integer, b As Integer)

    MsgBox a + b

End Sub

The procedure receives two numbers and displays their sum.

Values can be passed to multiple arguments when calling a procedure.

Sub MainProgram()

    AddNumbers 10, 20

End Sub

Sub AddNumbers(a As Integer, b As Integer)

    MsgBox a + b

End Sub

The result displayed is 30.

A Sub Procedure in a standard module is generally available to other procedures in the project unless its scope is restricted.

A procedure can also be explicitly declared as Public.

Public Sub ShowWelcome()

    MsgBox "Welcome"

End Sub

The Public keyword specifies that the procedure has public scope.

A Sub Procedure can be declared as Private.

Private Sub ShowMessage()

    MsgBox "This is a private procedure"

End Sub

A Private procedure is intended to be used only within its containing module.

Sub Procedures are useful for automating data-entry tasks.

Sub AddStudent()

    Range("A2").Value = "101"
    Range("B2").Value = "Rahul"
    Range("C2").Value = "ADCA"

End Sub

This procedure enters a student ID, name, and course into worksheet cells.

A Sub Procedure can also apply formatting.

Sub FormatHeading()

    Range("A1:C1").Font.Bold = True

End Sub

This procedure makes the text in cells A1 to C1 bold.

Sub Procedures can be used to automate report generation.

Sub CreateReport()

    Range("A1").Value = "Student Report"
    Range("A2").Value = "Total Students"
    Range("B2").Value = 50

End Sub

A larger procedure can contain many steps required to prepare a report.

Comments can be added inside a Sub Procedure to explain the code.

Sub CalculateTotal()

    ' Calculate the total marks
    Range("F2").Value = Range("B2").Value + Range("C2").Value

End Sub

A comment begins with an apostrophe (') in VBA.

Both Sub Procedures and Functions contain VBA code, but they are commonly used for different purposes.

Beginners may make some common mistakes when creating Sub Procedures.

• Forgetting End Sub.
• Using invalid procedure names.
• Forgetting quotation marks around text.
• Using incorrect cell references.
• Calling a procedure with incorrect arguments.

Writing small programs and testing them regularly helps identify these mistakes.

Let's create a simple procedure for entering student information.

Sub StudentEntry()

    Range("A1").Value = "Student ID"
    Range("B1").Value = "Student Name"
    Range("C1").Value = "Course"

    Range("A2").Value = 101
    Range("B2").Value = "Amit"
    Range("C2").Value = "ADCA"

End Sub

Running this procedure places the sample student information into the worksheet.

• Use meaningful procedure names.
• Keep each procedure focused on a specific task.
• Use comments where necessary.
• Keep related procedures organized in modules.
• Test procedures after making changes.
• Avoid unnecessarily long procedures.

Well-organized Sub Procedures make VBA projects easier to maintain.

A simple workflow for creating a Sub Procedure is:

1. Open the VBA Editor.
2. Open or insert a standard module.
3. Type the Sub keyword.
4. Give the procedure a meaningful name.
5. Write the required VBA statements.
6. Close the procedure using End Sub.
7. Run and test the procedure.
8. Save the workbook.

A Sub Procedure is a named block of VBA code used to perform a specific task. It begins with Sub and ends with End Sub.

Sub Procedures can work with cells, variables, calculations, formatting, data entry, reports, and other VBA objects.

They can also receive arguments and call other procedures. Learning Sub Procedures is an important step toward writing practical VBA applications.