Modularizing VBA Code: When to Split into Functions and Subs

Smaller, focused procedures reduce bugs and make testing easier.

When to split

• The procedure exceeds ~50 lines
• Mixed responsibilities (I/O, calculation, UI)
• Repeated code blocks (extract to a Function/Sub)

Functions vs. Subs

• Function returns a value and has no side effects (ideally)
• Sub performs actions (formatting, writing, UI)

Public Function NetPrice(ByVal gross As Double, ByVal vat As Double) As Double
    NetPrice = gross / (1 + vat)
End Function

Public Sub WriteNetPrice(ByVal target As Range, ByVal gross As Double, ByVal vat As Double)
    target.Value = NetPrice(gross, vat)
End Sub

Parameter design

Prefer explicit arguments over reading globals. Use ByVal by default; use ByRef when you really need it.

Example refactor

Public Sub CreateReport()
    Dim data As Variant
    data = LoadData()
    data = TransformData(data)
    OutputData data
End Sub