Writing VBA Procedures and Functions: Subs, Functions, and Scope Explained for Excel Automation

Introduction

Imagine you're building an Excel workbook that processes monthly sales data from twelve regional offices. You need to clean each dataset, calculate commissions, flag outliers, and write results to a summary sheet. If you put all that logic into a single massive macro, you'll end up with hundreds of lines of tangled code that nobody — including you, three weeks from now — can follow. The moment something breaks, you'll be hunting for a needle in a haystack.

The solution that professional VBA developers reach for is the same one that programmers in every language use: breaking code into procedures — small, focused, reusable units that each do one thing well. In VBA, those procedures come in two flavors: Subs (which perform actions) and Functions (which calculate and return values). Understanding when to use each, and how to control which parts of your workbook can "see" them, is the skill that transforms your code from a fragile one-off script into something you can maintain, extend, and be proud of.

By the end of this lesson, you'll have a solid command of VBA's procedural building blocks and be able to write modular, well-organized automation that scales to real-world complexity.

What you'll learn:

• The difference between Sub and Function procedures, and when to use each
• How to pass data into procedures using parameters (arguments)
• How to write custom Functions that work directly in Excel cells
• What "scope" means and how Public, Private, and variable-level scope control what your code can access
• How to call procedures from other procedures to build modular, reusable automation

---

Prerequisites

You should be comfortable opening the Visual Basic Editor (VBE) and have written at least one basic macro before diving in. If you're brand new to VBA, start with Getting Started with VBA Macros in Excel first. A working knowledge of VBA variables and data types will also help — the article on VBA Variables, Data Types, and Control Structures: Building Robust Excel Automation covers that ground thoroughly.

---

What Is a Procedure, and Why Does It Matter?

In VBA, a procedure is a named block of code that performs a specific task. Think of it like a recipe card: you name the recipe, list what ingredients it needs (parameters), and describe the steps. When you want pasta, you pull out the pasta card — you don't rewrite the instructions from scratch every time.

Before procedures, a common beginner pattern is writing everything in a single Sub:

Sub DoEverything()
    ' Clean data
    ' Calculate commissions
    ' Flag outliers
    ' Write to summary sheet
    ' ... 300 lines later ...
End Sub

This works, but only barely. Every procedure you write should ideally fit on a single screen without scrolling. When your code reads like a story — where each paragraph hands off cleanly to the next — debugging becomes dramatically easier.

---

Sub Procedures: The Workhorses of VBA Automation

A Sub (short for subroutine) is a procedure that performs actions but does not return a value. The overwhelming majority of macros you'll write are Subs. Formatting cells, copying data between sheets, sending emails, refreshing PivotTables — these are all action-oriented tasks that belong in Subs.

The basic syntax looks like this:

Sub ProcedureName()
    ' Your code here
End Sub

Let's write something realistic. Suppose you manage a sales report and need a Sub that formats a data range to look consistent every time:

Sub FormatSalesTable()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sales Data")
    
    ' Apply header formatting
    With ws.Range("A1:F1")
        .Font.Bold = True
        .Interior.Color = RGB(0, 70, 127)
        .Font.Color = RGB(255, 255, 255)
    End With
    
    ' Apply alternating row colors to data rows
    Dim i As Long
    For i = 2 To 50
        If i Mod 2 = 0 Then
            ws.Rows(i).Interior.Color = RGB(235, 241, 250)
        Else
            ws.Rows(i).Interior.Color = RGB(255, 255, 255)
        End If
    Next i
    
    MsgBox "Sales table formatted successfully.", vbInformation
End Sub

You can run this Sub directly from the VBE by pressing F5 while your cursor is inside it, or by assigning it to a button on your spreadsheet.

Calling One Sub from Another

Subs can call other Subs. This is the core of modular design. Use the Call keyword (optional but readable) or just write the Sub's name:

Sub RunMonthlyReport()
    Call ImportRawData
    Call CleanAndValidate
    Call CalculateCommissions
    Call FormatSalesTable
    Call ExportToSummary
    MsgBox "Monthly report complete!", vbInformation
End Sub

RunMonthlyReport reads like an outline — you can understand the workflow at a glance without wading through implementation details. Each sub-task lives in its own named procedure.

---

Passing Data with Parameters

Procedures become far more powerful when they can accept parameters (also called arguments) — pieces of information you pass in when calling them.

Compare these two approaches:

' Version 1: No parameters (rigid)
Sub HighlightOverdueInvoices()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Invoices")
    ' Always highlights the same sheet with the same color
End Sub

' Version 2: With parameters (flexible)
Sub HighlightRange(ws As Worksheet, targetRange As Range, highlightColor As Long)
    Dim cell As Range
    For Each cell In targetRange
        If cell.Value < Date Then
            cell.Interior.Color = highlightColor
        End If
    Next cell
End Sub

The second version can work on any worksheet, any range, and any color. You call it like this:

Sub RunHighlighting()
    Dim invoiceSheet As Worksheet
    Set invoiceSheet = ThisWorkbook.Worksheets("Invoices")
    
    Call HighlightRange(invoiceSheet, invoiceSheet.Range("C2:C100"), RGB(255, 200, 200))
End Sub

ByVal vs. ByRef: An Important Distinction

When you pass parameters in VBA, you need to know about two modes: ByVal and ByRef.

• ByRef (the default) passes a reference to the original variable. If the procedure modifies it, the change persists outside the procedure.
• ByVal passes a copy of the value. The original variable is protected.

Sub TestByRef(ByRef score As Long)
    score = score * 2  ' Modifies the original variable
End Sub

Sub TestByVal(ByVal score As Long)
    score = score * 2  ' Only modifies the local copy
End Sub

Sub DemonstrateScope()
    Dim myScore As Long
    myScore = 50
    
    Call TestByRef(myScore)
    Debug.Print myScore  ' Prints 100 — original was changed
    
    myScore = 50
    Call TestByVal(myScore)
    Debug.Print myScore  ' Prints 50 — original unchanged
End Sub

---

Function Procedures: Returning Values

A Function is like a Sub with one crucial addition: it returns a value. This makes Functions the right tool whenever you need to calculate something and use the result elsewhere.

Function ProcedureName(parameters) As ReturnDataType
    ' Your logic here
    ProcedureName = resultValue  ' Assign the return value
End Function

Notice the As ReturnDataType at the end — you declare what type of value the Function hands back. And you assign the return value by setting the Function's own name equal to a result.

Here's a practical example. Suppose your company calculates sales commissions on a tiered basis:

Function CalculateCommission(saleAmount As Double, regionCode As String) As Double
    Dim baseRate As Double
    Dim bonusRate As Double
    
    ' Determine base rate by region
    Select Case regionCode
        Case "NE", "SE"
            baseRate = 0.08  ' 8% for Eastern regions
        Case "MW", "SW"
            baseRate = 0.065  ' 6.5% for Western regions
        Case Else
            baseRate = 0.07  ' 7% default
    End Select
    
    ' Apply bonus tier for large deals
    If saleAmount > 100000 Then
        bonusRate = 0.015
    ElseIf saleAmount > 50000 Then
        bonusRate = 0.005
    Else
        bonusRate = 0
    End If
    
    CalculateCommission = saleAmount * (baseRate + bonusRate)
End Function

You can now call this function from another Sub:

Sub ProcessCommissions()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sales Data")
    
    Dim i As Long
    For i = 2 To 100
        Dim saleAmt As Double
        Dim region As String
        saleAmt = ws.Cells(i, 3).Value
        region = ws.Cells(i, 4).Value
        
        ' Call our Function and write the result to column 5
        ws.Cells(i, 5).Value = CalculateCommission(saleAmt, region)
    Next i
End Sub

Using VBA Functions Directly in Excel Cells

Here's a feature beginners often discover with delight: any VBA Function stored in a standard module (not a Sheet module or ThisWorkbook) is automatically available as a custom worksheet formula.

After writing CalculateCommission above, you can type this directly into a cell:

=CalculateCommission(C2, D2)

Excel will call your VBA function and display the result, just like a built-in formula. This is incredibly useful for business logic that's too complex for a single Excel formula. The catch: workbooks with custom functions must be saved as .xlsm (macro-enabled) format, and the user's macro security settings must allow them to run.

---

Understanding Scope: Who Can See What?

Scope determines which parts of your VBA project can access a given procedure or variable. Getting scope wrong is one of the most common sources of bugs and confusion in VBA projects, so it's worth understanding deeply.

Think of scope like building access in a large office. Some rooms are public — anyone in the building can walk in. Others are private — only people on that floor can access them. And some storage (variables) only exists for the duration of a single meeting (local variables).

Procedure Scope: Public vs. Private

Every procedure is either Public or Private.

Public procedures are accessible from anywhere in the workbook — any other module, any other Sub or Function:

' In Module1
Public Sub FormatSalesTable()
    ' Any code in this workbook can call this
End Sub

Private procedures are only accessible within the same module:

' In Module1
Private Sub ApplyHeaderStyle(ws As Worksheet)
    ' Only other procedures in Module1 can call this
End Sub

If you omit the keyword entirely, VBA defaults to Public for procedures in standard modules. But being explicit is a professional habit — it signals your intent to anyone reading your code.

Variable Scope: Local, Module-Level, and Public

Variables follow the same logic, but with three distinct levels:

Local variables are declared inside a procedure with Dim. They exist only while that procedure runs, then vanish:

Sub CalculateTotals()
    Dim total As Double  ' Local — dies when Sub ends
    total = 0
    ' ...
End Sub

Module-level variables are declared at the top of a module, outside any procedure. They persist for the lifetime of the module and are accessible to all procedures within it:

' At the top of Module1, outside any procedure
Dim reportDate As Date  ' Module-level — visible to all Subs/Functions in Module1

Sub SetReportDate()
    reportDate = Date
End Sub

Sub PrintReportDate()
    MsgBox "Report date: " & reportDate  ' Can access reportDate
End Sub

Public module-level variables are declared with Public instead of Dim at the top of a module, making them visible everywhere in the project:

' At the top of Module1
Public CompanyName As String  ' Accessible from any module in the project

A Practical Scope Example

Here's how a well-scoped module might look for a commission reporting system:

' ============================================
' Module: CommissionProcessor
' ============================================

' Module-level: shared across all procedures in this module
Private mReportMonth As Integer
Private mReportYear As Integer

' ---- Public entry point ----

Public Sub RunCommissionReport(reportMonth As Integer, reportYear As Integer)
    mReportMonth = reportMonth
    mReportYear = reportYear
    
    Call LoadSalesData
    Call ProcessAllRegions
    Call WriteReportHeader
    
    MsgBox "Commission report for " & mReportMonth & "/" & mReportYear & " complete!"
End Sub

' ---- Private helpers (internal use only) ----

Private Sub LoadSalesData()
    ' Reads raw data for mReportMonth/mReportYear
End Sub

Private Sub ProcessAllRegions()
    Dim regions As Variant
    regions = Array("NE", "SE", "MW", "SW")
    
    Dim i As Integer
    For i = 0 To UBound(regions)
        Call ProcessRegion(regions(i))
    Next i
End Sub

Private Sub ProcessRegion(regionCode As String)
    ' Process a single region using CalculateCommission function
End Sub

Private Sub WriteReportHeader()
    ' Uses mReportMonth and mReportYear to write header
End Sub

Notice how only RunCommissionReport is Public — it's the single "door" into this module's functionality. Everything else is internal machinery. This is clean, professional module design.

---

Early Exit from Procedures

Sometimes you need to bail out of a procedure before it finishes — perhaps because input data is invalid, or a required worksheet doesn't exist. Use Exit Sub or Exit Function for this:

Function GetMonthlyTarget(month As Integer) As Double
    If month < 1 Or month > 12 Then
        MsgBox "Invalid month: " & month, vbExclamation
        GetMonthlyTarget = 0  ' Return a safe default
        Exit Function        ' Stop here
    End If
    
    ' Continue with normal logic...
    GetMonthlyTarget = 50000 * (1 + (month - 1) * 0.05)
End Function

Early exit is far better than deeply nested If blocks. It handles edge cases at the top, leaving the main logic clean and unindented. This pattern is sometimes called a guard clause.

---

Hands-On Exercise

Let's put this all together. Build a small but complete VBA module that demonstrates Subs, Functions, parameters, and scope working together.

Scenario: You have a worksheet called "Q4 Sales" with salesperson names in column A, sale amounts in column B (rows 2–20), and a blank "Performance" column in column C. You'll write a system that categorizes each sale and writes a performance label.

Step 1: Open the Visual Basic Editor (Alt + F11). In your workbook's VBAProject, right-click on "Microsoft Excel Objects," choose Insert → Module. A new standard module appears.

Step 2: Paste the following code into the module:

' ============================================
' Module: SalesPerformance
' ============================================

' Module-level threshold values
Private Const GOLD_THRESHOLD As Double = 80000
Private Const SILVER_THRESHOLD As Double = 50000

' Public entry point — run this to process the sheet
Public Sub CategorizeAllSales()
    Dim ws As Worksheet
    
    ' Safely get the worksheet
    If Not SheetExists("Q4 Sales") Then
        MsgBox "Sheet 'Q4 Sales' not found. Please create it first.", vbCritical
        Exit Sub
    End If
    
    Set ws = ThisWorkbook.Worksheets("Q4 Sales")
    
    Dim i As Long
    For i = 2 To 20
        Dim saleAmount As Double
        saleAmount = ws.Cells(i, 2).Value
        
        ' Use our Function to get the label
        ws.Cells(i, 3).Value = GetPerformanceLabel(saleAmount)
    Next i
    
    Call ApplyPerformanceFormatting(ws)
    MsgBox "Sales categorization complete!", vbInformation
End Sub

' Function: returns a performance label based on sale amount
Private Function GetPerformanceLabel(amount As Double) As String
    If amount >= GOLD_THRESHOLD Then
        GetPerformanceLabel = "Gold"
    ElseIf amount >= SILVER_THRESHOLD Then
        GetPerformanceLabel = "Silver"
    ElseIf amount > 0 Then
        GetPerformanceLabel = "Bronze"
    Else
        GetPerformanceLabel = "No Sale"
    End If
End Function

' Private helper: colors column C based on the label written there
Private Sub ApplyPerformanceFormatting(ws As Worksheet)
    Dim i As Long
    For i = 2 To 20
        Select Case ws.Cells(i, 3).Value
            Case "Gold"
                ws.Cells(i, 3).Interior.Color = RGB(255, 215, 0)
            Case "Silver"
                ws.Cells(i, 3).Interior.Color = RGB(192, 192, 192)
            Case "Bronze"
                ws.Cells(i, 3).Interior.Color = RGB(205, 127, 50)
            Case Else
                ws.Cells(i, 3).Interior.Color = RGB(255, 255, 255)
        End Select
    Next i
End Sub

' Private utility: checks if a sheet exists before we try to use it
Private Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0
    SheetExists = Not ws Is Nothing
End Function

Step 3: Create a worksheet named "Q4 Sales." Put salesperson names in A2:A20 and sale amounts in B2:B20 (mix values between 0 and 120,000).

Step 4: Run CategorizeAllSales by pressing F5 while your cursor is inside that Sub.

After running, column C should contain "Gold," "Silver," "Bronze," or "No Sale," with appropriate background colors.

Take a moment to study the design: CategorizeAllSales is the only public entry point. GetPerformanceLabel is a private Function that returns a value. ApplyPerformanceFormatting is a private Sub that performs formatting. SheetExists is a reusable private utility Function. Each piece does exactly one thing.

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting to assign the return value in a Function

If you write logic inside a Function but never assign FunctionName = someValue, the Function silently returns 0 (for numbers) or an empty string. No error — just wrong results. Always make sure your return assignment is reachable.

Mistake 2: Trying to call a Private procedure from another module

You'll get a "Sub or Function not defined" compile error. Either make the procedure Public, or move the calling code into the same module.

Mistake 3: Naming a Function the same as a built-in Excel function

If you create a Function called Sum or Date, VBA gets confused. Prefix custom functions with your company or project initials — WSD_CalculateCommission, for example — to avoid naming collisions.

Mistake 4: Using Public variables when parameters would be cleaner

If you're storing intermediate results in Public variables just so procedures can share them, step back and ask whether those values should be passed as parameters instead. Public variables persist across calls and can hold stale values if you're not careful.

Mistake 5: Modifying data inside a Function

Functions used as worksheet formulas should not modify the workbook — Excel doesn't allow it (you'll get errors). Even in purely VBA contexts, it's best practice to keep Functions free of side effects. If you need to both return a value and modify something, split the work: use the Function for the calculation and a Sub to apply the result.

---

Summary & Next Steps

You now understand the two fundamental building blocks of VBA automation: Subs perform actions and Functions calculate and return values. You know how to pass data into procedures using parameters, how ByVal and ByRef control whether original variables are protected, and how scope — Public vs. Private, local vs. module-level — determines what your code can see and access.

Most importantly, you've seen how these concepts combine into a modular design where each procedure has a single responsibility, the public surface area is minimal, and the whole system is easier to read, debug, and extend.

Here's where to go next from here:

• Put your new skills to work with loops and conditions: Most real procedures need to iterate over data and make decisions — Automating Repetitive Tasks with VBA Loops and Conditions covers exactly that.
• Learn to manipulate spreadsheet data from code: Working with Ranges, Cells, and Worksheets in VBA for Data Professionals shows you how to interact with Excel's object model from well-structured procedures.
• Build something real: Building an Automated Reporting System with VBA applies everything in this lesson to a complete, end-to-end reporting workflow.
• Take modular design further: Once you're comfortable with Subs and Functions, Advanced VBA: Class Modules and Object-Oriented Patterns for Excel Automation introduces object-oriented thinking in VBA — a natural next step for building larger, more sophisticated tools.

Good procedure design is one of those skills that quietly makes everything else easier. The time you invest in breaking your code into clean, purposeful Subs and Functions pays back every time you revisit a workbook months later and immediately understand what it does.