[VBA] How to Pass Arguments to Subroutines: Basics for Writing Reusable Code

Introduction

Splitting VBA processing into subroutines (modularized macros) helps organize your code. However, if you leave them as is, they can only execute processing on “specific cell ranges” or “fixed text strings.”

To turn a subroutine into a powerful and reusable “versatile component,” “Arguments” are essential. Arguments allow the calling side (Parent Macro) to pass specific data (such as which cell range to target) to the subroutine (Child Macro).

In this article, I will explain the basic syntax for passing arguments to subroutines and the benefits of doing so in a clear and easy-to-understand way.

How to Pass Values to a Subroutine with Arguments

In this example, we will create a reusable subroutine called FormatCell that writes specified text into a specified cell range and sets the formatting.

Completed Code

' === Main Execution Macro ===
Sub CreateFormattedHeader()

    ' --- Pass arguments in order (Positional Arguments) ---
    ' Pass a Range object as the 1st argument and a String as the 2nd argument
    Call FormatCell(Worksheets("Sheet1").Range("B2"), "Sales")
    
    ' --- Pass by specifying argument names (Named Arguments) ---
    ' You can specify which value goes to which argument regardless of the order
    Call FormatCell(targetText:="Profit", targetRange:=Worksheets("Sheet1").Range("D2"))
    
End Sub


' --- Subroutine that receives arguments ---
' Define arguments in the part: (targetRange As Range, targetText As String)
Private Sub FormatCell(targetRange As Range, targetText As String)

    ' Execute processing using the passed arguments
    With targetRange
        .Value = targetText
        .Font.Bold = True
        .Interior.Color = RGB(220, 230, 241) ' Light blue
    End With
    
End Sub

Code Explanation

Private Sub FormatCell(targetRange As Range, targetText As String)

This is the definition of the subroutine that receives arguments.

• Inside the () after Sub, define the arguments you want to receive in the format VariableName As DataType, separated by commas.
• targetRange and targetText defined here function as special variables (parameters) that can only be used within this FormatCell subroutine.

Call FormatCell(Worksheets("Sheet1").Range("B2"), "Sales")

This is the most common way to call a subroutine using “Positional Arguments.”

• When calling FormatCell, describe the values you want to pass inside the () in the same order as the subroutine definition.
• The 1st value (Range("B2")) is passed to targetRange, and the 2nd value ("Sales") is passed to targetText.

Call FormatCell(targetText:="Profit", targetRange:=...)

This is how to call using “Named Arguments” (or Keyword Arguments).

• You explicitly specify which value to pass to which argument using the format ArgumentName:=Value.
• Using named arguments has the benefit of making the code more readable, and you do not need to worry about the order of the arguments defined in the subroutine.

Benefits of Using Arguments

1. Dramatic Improvement in Reusability: The FormatCell subroutine has become a generic “formatting tool” that can handle any cell range and any text.
2. Code Simplification: Without arguments, you might have needed to create separate macros for “Sales” formatting and “Profit” formatting.
3. Improved Maintainability: If you want to change the formatting logic (e.g., the background color), you only need to modify the code inside FormatCell. The change will be reflected in all places calling this subroutine.

Summary

In this article, I explained how to make your code more flexible and reusable by passing arguments to subroutines.

• Definition: Prepare a receiver in the subroutine like Sub MacroName(Arg1 As Type, Arg2 As Type).
• Calling: Pass specific values from the calling side like Call MacroName(Value1, Value2).
• Named Arguments: Using ArgumentName:=Value allows you to pass values without worrying about the order, making the code easier to read.

Arguments are one of the most basic and important concepts in VBA programming. By using them effectively, you can significantly improve the quality of your code.