[VBA] Organizing Code by Creating Subroutines (Modularization)

Introduction

When creating macros in VBA, you might find yourself writing procedures that become dozens or even hundreds of lines long. This often leads to “spaghetti code,” where it is difficult to understand what is happening and where. Such code is extremely hard to modify later or for others to read.

The most effective solution to this problem is “Subroutines.”

By breaking down a long series of processes into meaningful, small, independent macros (subroutines) and calling them sequentially from a main macro, you can dramatically improve the readability of your code.

This article explains the basic technique of structuring your program by splitting processes into subroutines and using the Call statement.

VBA Sample Code: Modularizing the Process

In this example, we will split the process of “creating a report” into three subroutines:

1. FormatReportHeader: Sets the formatting for the header.
2. PopulateReportData: Writes the data into the report.
3. ApplyFinalTouches: Applies final formatting (such as borders).

We then create a main macro, CreateReport, to call these subroutines in order.

The Completed Code

' === Main Execution Macro ===
Sub CreateReport()
    ' Call each subroutine in order
    Call FormatReportHeader
    Call PopulateReportData
    Call ApplyFinalTouches
    
    MsgBox "Report creation complete.", vbInformation
End Sub


' --- Subroutines (Modularized Macros) ---

' Use the "Private" keyword to ensure these can only be called from within this module
Private Sub FormatReportHeader()
    ' Set header formatting
    With Worksheets("Sheet1").Range("B2")
        .Value = "Monthly Sales Report"
        .Font.Bold = True
        .Font.Size = 16
    End With
End Sub

Private Sub PopulateReportData()
    ' Write data
    Worksheets("Sheet1").Range("B4").Value = "Sales"
    Worksheets("Sheet1").Range("C4").Value = 150000
End Sub

Private Sub ApplyFinalTouches()
    ' Apply final formatting (Borders)
    Worksheets("Sheet1").Range("B2:C4").Borders.LineStyle = xlContinuous
End Sub

Explanation of the Code

Sub CreateReport()

This is the main process (parent macro) that the user executes. The role of this macro is not to perform specific tasks itself, but to manage which subroutines to run and in what order. The code acts like a “Table of Contents” for the process, making the overall flow very easy to understand.

Call FormatReportHeader

Call is the statement used to run another procedure (in this case, the subroutine named FormatReportHeader). While Call can be omitted, it is recommended to keep it because it makes it clear that you are invoking another macro.

Private Sub ...

The key point here is adding the Private keyword to the subroutine declarations.

• Public Sub (or simply Sub): A public macro that can be called from any module in the workbook.
• Private Sub: A private macro that can only be called from within the module where it is written.

Since subroutines are merely “parts” of the main macro and are not meant to be run directly by the user, setting them to Private prevents them from appearing in Excel’s “Macro” list (Alt+F8), which helps avoid accidental execution.

Benefits of Using Subroutines

1. Improved Readability: Since the code is divided into meaningful units like “Create Header” or “Input Data,” it is easier to find where specific tasks are written.
2. Improved Maintainability: If you want to change only the header format, you only need to modify FormatReportHeader. You do not have to worry about breaking other parts of the code.
3. Improved Reusability: Parts like FormatReportHeader might be reusable in other report-creation macros.

Summary

In this article, I explained how to organize your VBA code by splitting processes into subroutines.

• Break down a long process into small Private Sub procedures based on their function.
• Call them sequentially from a main Public Sub using the Call statement.

While it might feel like extra work at first, mastering this “modular” approach is the first step toward developing more advanced and complex macros. Please use this technique to refactor (improve) your own code.