Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

Subroutines & Arguments in VBA

Learn how to create reusable Sub procedures, pass arguments, use ByVal and ByRef, work with optional parameters, and build practical Excel automation programs.

1. What Is a Subroutine?

A Subroutine is a named block of VBA code that performs a particular task. In VBA, a Subroutine is declared using the Sub keyword and ends with End Sub.

Instead of writing the same instructions repeatedly, you can create a Sub procedure once and call it whenever required. Subroutines are useful for formatting reports, calculating totals, processing rows, and preparing worksheets.

Main Procedure Call Subroutine Execute Reusable Code Control returns to the calling procedure after the Sub finishes.
Remember: A Sub procedure performs actions but does not return a value directly as its procedure result. Use a Function when you need a procedure that returns a value.

2. Syntax of a Sub Procedure

Sub ProcedureName()

    ' Statements to execute

End Sub

Example 1: Simple Subroutine

Sub WelcomeMessage()

    MsgBox "Welcome to VBA Programming!"

End Sub

The procedure is defined with the name WelcomeMessage. When it runs, VBA displays a message box.

How to execute it

  1. Open the Visual Basic Editor using Alt + F11.
  2. Insert a standard module.
  3. Write the Sub procedure.
  4. Place the cursor inside the procedure.
  5. Press F5 to run it.

3. Calling One Subroutine from Another

A Sub procedure can execute another Sub procedure. This helps divide a large task into smaller, reusable operations.

Example 2: Calling another Sub

Sub MainProcedure()

    MsgBox "Starting the program."

    DisplayMessage

    MsgBox "Program completed."

End Sub

Sub DisplayMessage()

    MsgBox "This message comes from another Sub."

End Sub

Execution sequence

  1. MainProcedure starts.
  2. The first message box appears.
  3. DisplayMessage executes.
  4. Control returns to MainProcedure.
  5. The final message box appears.

Using Call

Sub MainProcedureWithCall()

    Call DisplayMessage

End Sub

The Call keyword is optional when calling a Sub. If you use Call, enclose the argument list in parentheses when arguments are supplied. Without Call, arguments to a Sub are normally written without parentheses.

4. What Are Arguments?

Arguments are values supplied to a procedure when it is called. They allow one reusable Sub to work with different input values.

A parameter is the variable declared in the procedure definition. An argument is the actual value or variable supplied in the call.

Term Meaning Example
Parameter A variable declared in the procedure definition. studentName As String
Argument A value supplied when calling the procedure. "Rahul"

Example 3: Subroutine with one argument

Sub DisplayStudent(ByVal studentName As String)

    MsgBox "Student Name: " & studentName

End Sub

Sub RunStudentExample()

    DisplayStudent "Rahul"
    DisplayStudent "Priya"
    DisplayStudent "Amit"

End Sub

The same Sub procedure can display different student names because the caller supplies a different argument each time.

5. Passing Multiple Arguments

A Sub procedure can accept two or more arguments. Separate the parameters using commas in the procedure declaration.

Example 4: Calculate total price

Sub CalculateTotal(ByVal quantity As Long, _
                    ByVal unitPrice As Currency)

    Dim totalAmount As Currency

    totalAmount = quantity * unitPrice

    MsgBox "Total Amount: " & Format(totalAmount, "0.00")

End Sub

Sub RunTotalExample()

    CalculateTotal 5, 250

    CalculateTotal 3, 1000

End Sub
Quantity Unit Price Total
5 250 1,250
3 1,000 3,000

Explanation: The calling procedure passes quantity and unit price. The reusable Sub calculates the total for whichever values are supplied.

6. ByVal in VBA

ByVal passes an argument's value to the parameter. Reassigning the parameter does not change the caller's original variable.

Example 5: Demonstrating ByVal

Sub ChangeValueByVal(ByVal number As Long)

    number = 100

    MsgBox "Value inside Sub: " & number

End Sub

Sub TestByVal()

    Dim x As Long

    x = 10

    ChangeValueByVal x

    MsgBox "Value outside Sub: " & x

End Sub
Location Value
Inside Sub 100
Outside Sub 10
Key point: For ordinary variables, changes to the local parameter do not update the caller's variable when the parameter is passed ByVal.

7. ByRef in VBA

ByRef passes an argument by reference. When a compatible variable is passed, assigning a new value to the parameter can change the caller's variable.

Example 6: Demonstrating ByRef

Sub ChangeValueByRef(ByRef number As Long)

    number = 100

End Sub

Sub TestByRef()

    Dim x As Long

    x = 10

    ChangeValueByRef x

    MsgBox "Value outside Sub: " & x

End Sub
Output: Value outside Sub: 100

ByVal vs ByRef

ByVal ByRef
Passes the argument's value. Passes access to the argument variable.
Reassigning the parameter does not reassign the caller's variable. Reassigning the parameter can change the caller's variable.
Useful when the procedure should not update the caller's variable. Useful when the procedure must update a variable supplied by the caller.
Important: In VBA, parameters default to ByRef if neither ByVal nor ByRef is specified. Declare the intended passing method explicitly for clearer code.

8. Optional Arguments

An Optional parameter allows a procedure to be called without supplying that argument. If the caller omits the argument, VBA uses the declared default value when one is provided.

Example 7: Optional discount rate

Sub CalculateDiscount( _
    ByVal price As Currency, _
    Optional ByVal discountRate As Double = 0.1)

    Dim discountAmount As Currency
    Dim finalPrice As Currency

    discountAmount = price * discountRate
    finalPrice = price - discountAmount

    MsgBox "Original Price: " & Format(price, "0.00") & vbCrLf & _
           "Discount: " & Format(discountAmount, "0.00") & vbCrLf & _
           "Final Price: " & Format(finalPrice, "0.00")

End Sub

Sub RunDiscountExamples()

    CalculateDiscount 1000

    CalculateDiscount 1000, 0.2

End Sub
Call Rate Discount Final Price
CalculateDiscount 1000 10% 100 900
CalculateDiscount 1000, 0.2 20% 200 800

Optional parameters must appear after required parameters. In this example, the default discount rate is 10%, but the caller can provide a different rate.

9. Named Arguments

Named arguments let you identify a parameter by its name instead of relying only on its position in the call. They are especially useful when a procedure has several optional parameters.

Example 8: Using named arguments

Sub DisplayEmployee( _
    ByVal employeeName As String, _
    Optional ByVal department As String = "General", _
    Optional ByVal location As String = "Pune")

    MsgBox "Name: " & employeeName & vbCrLf & _
           "Department: " & department & vbCrLf & _
           "Location: " & location

End Sub

Sub RunNamedArgumentExample()

    DisplayEmployee "Rahul"

    DisplayEmployee "Priya", _
                    location:="Mumbai"

    DisplayEmployee employeeName:="Amit", _
                    department:="Finance", _
                    location:="Pune"

End Sub

Named arguments make calls easier to read and allow you to specify an optional argument without supplying every optional argument before it.

10. Practical Example: Reusable Worksheet Formatting

Suppose you regularly prepare reports. Instead of writing formatting instructions for every report, create a reusable Sub that accepts a worksheet range as an argument.

Example 9: Format a report heading

Option Explicit

Sub FormatHeading(ByVal headingRange As Range)

    With headingRange
        .Font.Bold = True
        .Font.Size = 16
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(13, 110, 253)
        .HorizontalAlignment = xlCenter
    End With

End Sub

Sub CreateReports()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ws.Range("A1").Value = "Employee Report"
    ws.Range("A1:D1").Merge

    FormatHeading ws.Range("A1:D1")

    ws.Range("A3").Value = "Employee Name"
    ws.Range("B3").Value = "Department"
    ws.Range("C3").Value = "Salary"
    ws.Range("D3").Value = "Status"

    FormatHeading ws.Range("A3:D3")

    ws.Columns("A:D").AutoFit

    MsgBox "Report formatting completed.", vbInformation

End Sub

Explanation

  1. FormatHeading accepts a Range object.
  2. The Sub applies bold text, font size, font color, background color, and alignment.
  3. CreateReports prepares the worksheet headings.
  4. The formatting Sub is called twice for different ranges.
  5. The same formatting logic can be reused in other report procedures.

11. Passing Worksheet Objects to a Sub

A procedure can receive a worksheet object as a parameter. This makes the code reusable across different worksheets without hardcoding a worksheet name inside the procedure.

Example 10: Add a heading to a worksheet

Option Explicit

Sub AddWorksheetHeading(ByVal ws As Worksheet, _
                        ByVal headingText As String)

    With ws.Range("A1")
        .Value = headingText
        .Font.Bold = True
        .Font.Size = 15
    End With

End Sub

Sub RunWorksheetHeading()

    AddWorksheetHeading _
        ThisWorkbook.Worksheets("Sheet1"), _
        "Monthly Sales Report"

    AddWorksheetHeading _
        ThisWorkbook.Worksheets("Sheet2"), _
        "Employee Report"

End Sub
Important: Passing an object reference ByVal prevents reassignment of the caller's object variable through that parameter, but it does not make the object itself immutable. The procedure can still change properties of the worksheet object it receives.

12. Validate Arguments Before Processing

A reusable procedure should check whether the supplied arguments meet its requirements. For example, a discount rate should not be negative or greater than 100%.

Example 11: Validate discount rate

Option Explicit

Sub ApplyValidatedDiscount( _
    ByVal price As Currency, _
    ByVal discountRate As Double)

    Dim finalPrice As Currency

    If price < 0 Then
        MsgBox "Price cannot be negative.", vbExclamation
        Exit Sub
    End If

    If discountRate < 0 Or discountRate > 1 Then
        MsgBox "Discount rate must be between 0 and 1.", vbExclamation
        Exit Sub
    End If

    finalPrice = price * (1 - discountRate)

    MsgBox "Final Price: " & Format(finalPrice, "0.00"), vbInformation

End Sub

Sub RunValidatedDiscount()

    ApplyValidatedDiscount 2000, 0.15

End Sub
Example output: Final Price: 1700.00

13. Understanding Procedure Scope

Scope determines where a procedure can be called from. In a standard module, a Sub declared Public is generally accessible throughout the VBA project, while a Private Sub is restricted to its declaring module.

Public procedure

Public Sub ShowPublicMessage()

    MsgBox "This is a public procedure."

End Sub

Private procedure

Private Sub ShowPrivateMessage()

    MsgBox "This is a private procedure."

End Sub
Note: Public and Private visibility can depend on the module type and declaration context. A Private procedure is accessible only within its declaring module. Public procedures in standard modules are commonly used for reusable project-level operations.

14. Common Mistakes to Avoid

  • Wrong argument count: Supplying fewer arguments than required or supplying too many arguments.
  • Wrong argument type: Passing text when a numeric parameter is expected.
  • Incorrect ByVal/ByRef assumption: Understanding whether a parameter can change a caller's variable.
  • Incorrect Call syntax: Using parentheses incorrectly when calling a Sub.
  • Optional parameters in the wrong order: Optional parameters must follow required parameters.
  • Hardcoded worksheet names: Pass worksheet or range objects when that makes a procedure more reusable.
  • No input validation: Validate values before calculations or worksheet modifications.

15. Practice Exercises

Create separate procedures for these tasks and test them using different arguments.

  1. Create a Sub that displays a welcome message.
  2. Create a Sub that accepts a student name and displays it.
  3. Create a Sub that accepts two numbers and displays their sum.
  4. Create a Sub that accepts quantity and unit price and calculates the total.
  5. Demonstrate the difference between ByVal and ByRef.
  6. Create a Sub with an optional discount rate.
  7. Use named arguments to pass an employee name and department.
  8. Create a reusable Sub that formats a specified Range object.
  9. Pass a Worksheet object to a procedure and add a heading.
  10. Validate a price and discount rate before calculating the final price.
  11. Create one main procedure that calls three smaller Sub procedures.
  12. Create a Private helper Sub and call it from another procedure in the same module.

Important Points to Remember

  • A Sub procedure begins with Sub and ends with End Sub.
  • Parameters are declared in the procedure definition; arguments are supplied when calling it.
  • One Sub can call another Sub.
  • ByVal prevents reassignment of the caller's ordinary variable through the parameter.
  • ByRef can allow a procedure to change the caller's variable.
  • Use Optional for parameters that do not always need to be supplied.
  • Use named arguments to make calls clearer when several parameters are available.
  • Pass worksheet or range objects to create reusable Excel procedures.
  • Validate arguments before performing important calculations or updates.

Summary

Subroutines organize VBA instructions into reusable procedures. Arguments allow a procedure to receive different values, while ByVal and ByRef control how parameters interact with variables supplied by the caller. Optional and named arguments make procedures more flexible and readable.

Passing worksheet and range objects allows the same procedure to work with different parts of a workbook. These techniques help create maintainable VBA automation for reports, calculations, formatting, and data processing.