📎 This article includes 1 downloadable practice file ↓

Most macros start as one long Sub that does everything: open the file, clean it, total it, format it, email it. It works until you need “the same cleaning, but for another file”, and you copy 80 lines and change three. Six months later you have five copies and a bug fixed in only two of them.

The cure is boring and powerful: break the work into small named pieces that you can call. VBA gives you two kinds — Subs, which do something, and Functions, which work something out and hand back an answer.

Calling one Sub from another

Sub MonthlyReport()
    ImportData
    CleanData
    BuildSummary
    MsgBox "Report ready"
End Sub

Sub CleanData()
    ' trim spaces, fix dates, remove blank rows...
End Sub

The top Sub now reads like a to-do list. When something breaks you know which piece to open, and each piece can be tested on its own (put the cursor inside it and press F5).

Passing information in: arguments

Sub HighlightAbove(ws As Worksheet, col As String, limit As Double)
    Dim c As Range
    For Each c In ws.Range(col & "2:" & col & ws.Cells(ws.Rows.Count, col).End(xlUp).Row)
        If c.Value > limit Then c.Interior.Color = RGB(255, 235, 156)
    Next c
End Sub

Sub Demo()
    HighlightAbove Worksheets("Sales"), "E", 100000
    HighlightAbove Worksheets("Returns"), "D", 5000
End Sub

One piece of logic, used twice with different inputs. That’s the whole idea.

ByVal and ByRef — the one that bites

By default VBA passes arguments ByRef: the Sub receives the original variable, so if it changes it, the caller’s variable changes too.

Sub AddTax(ByRef amount As Double)
    amount = amount * 1.18
End Sub

Sub Test()
    Dim price As Double: price = 100
    AddTax price
    Debug.Print price      ' 118 — price itself was changed
End Sub

Sometimes that’s what you want. Usually it’s a surprise. Write ByVal when a Sub should only use a value, and keep ByRef for the rare case where changing it is the point. Being explicit costs one word and prevents hours of “why did my total change?”.

Functions: give an answer back

Function NetAmount(ByVal gross As Double, ByVal discountPct As Double) As Double
    NetAmount = gross * (1 - discountPct)
End Function

Sub Use()
    Debug.Print NetAmount(2500, 0.1)     ' 2250
End Sub

The answer is returned by assigning to the function’s own name. The As Double after the brackets is the type of the answer.

User-defined functions: your own Excel formulas

Put a Public Function in a normal module and you can use it in a cell like any built-in function:

Public Function GST(ByVal amount As Double, Optional ByVal rate As Double = 0.18) As Double
    GST = Round(amount * rate, 2)
End Function

Public Function INWORDS_LAKH(ByVal n As Double) As String
    ' 1234567 -> "12.35 lakh"
    If n >= 10000000 Then
        INWORDS_LAKH = Format(n / 10000000, "0.00") & " crore"
    ElseIf n >= 100000 Then
        INWORDS_LAKH = Format(n / 100000, "0.00") & " lakh"
    Else
        INWORDS_LAKH = Format(n, "#,##0")
    End If
End Function

In a cell: =GST(B2), =GST(B2, 5%) or =INWORDS_LAKH(D10). They appear in the formula autocomplete list too.

Making UDFs recalculate

Excel recalculates a UDF only when one of its arguments changes. If your function reads cells that aren’t passed in as arguments, it won’t update. Either pass everything it needs as arguments (best), or add Application.Volatile at the top — which makes it recalculate on every change in the workbook and can slow big files.

Optional arguments and defaults

Optional ByVal rate As Double = 0.18 above means callers can leave the rate out. Optional arguments must come last. For an optional Variant you can test whether it was supplied with IsMissing(x).

Private vs Public

• Public (the default): visible from other modules and, for Functions, from cells.
• Private: only usable inside its own module. Mark helper Subs Private so they don’t clutter the Alt+F8 macro list.

Where people go wrong

Practice

The workbook has GST, INWORDS_LAKH and a FY (financial year) function ready to use in cells, plus a Refactor sheet: a 60-line macro to break into four Subs. The answer is in a second module so you can compare.

Next: working with sheets and workbooks — adding, copying, looping through them and saving copies safely.