Day 9 — Subs, Functions and arguments

One 200-line Sub is a nightmare to fix. Six small ones that each do one thing is a program you can actually maintain.

Sub does a job. Function answers a question.

	Sub	Function
Purpose	Performs an action	Calculates and hands back a value
Returns	Nothing	One value
Run with F5	Yes	No
Usable in a cell formula	No	Yes

Passing arguments

An argument is a value you hand in when you call the procedure. It makes one piece of code work on many different inputs.

Sub Main()
    ReportRow 2
    ReportRow 7
    ReportRow 14
End Sub

Sub ReportRow(r As Long)
    Debug.Print "Row " & r & ": " & Cells(r, 4).Value & " sold " & _
                Cells(r, 6).Value & " " & Cells(r, 5).Value
End Sub

Notice there is no Sub called Main in Excel’s eyes - it is just the first one, and that is what runs. Written out, the same three lines would be twenty.

Brackets or no brackets Calling a Sub, leave the brackets off: ReportRow 2. Calling a Function and using its answer, put them on: total = OrderValue(2). Get this backwards and VBA gives a confusing syntax error.

Functions return a value

Inside a Function, you assign the answer to the function’s own name. That is the bit that looks strange at first.

Sub ShowValues()
    Debug.Print "Row 2:", OrderValue(2)
    Debug.Print "Row 5:", OrderValue(5)
    Debug.Print "Biggest of the two:", Larger(OrderValue(2), OrderValue(5))
End Sub

Function OrderValue(r As Long) As Double
    OrderValue = Cells(r, 6).Value * Cells(r, 7).Value
End Function

Function Larger(a As Double, b As Double) As Double
    If a > b Then
        Larger = a
    Else
        Larger = b
    End If
End Function

Now the loop reads like English

Sub FillWithFunction()
    Dim i As Long
    Dim lastRow As Long

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("H1").Value = "Total"

    For i = 2 To lastRow
        Cells(i, 8).Value = OrderValue(i)
    Next i

    Debug.Print "Grand total:", GrandTotal()
End Sub

Function OrderValue(r As Long) As Double
    OrderValue = Cells(r, 6).Value * Cells(r, 7).Value
End Function

Function GrandTotal() As Double
    Dim i As Long
    Dim lastRow As Long
    Dim t As Double

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        t = t + OrderValue(i)
    Next i

    GrandTotal = t
End Function

Each piece is small enough to check on its own. When the grand total is wrong you know to look inside GrandTotal, and nowhere else.

Exit Sub and Exit Function

Leave early when there is nothing left to do. It saves a level of indentation and reads better than wrapping the whole body in an If.

Sub CheckRows()
    Debug.Print Describe(2)
    Debug.Print Describe(99)
End Sub

Function Describe(r As Long) As String
    If Cells(r, 1).Value = "" Then
        Describe = "Row " & r & " is empty"
        Exit Function
    End If

    Describe = "Row " & r & " is order " & Cells(r, 1).Value
End Function

ByVal and ByRef

By default VBA passes a variable ByRef - the procedure gets the original, and changing it changes the caller’s copy too. ByVal sends a copy instead.

Sub Caller()
    Dim n As Long

    n = 10
    BumpByRef n
    Debug.Print "After ByRef:", n

    n = 10
    BumpByVal n
    Debug.Print "After ByVal:", n
End Sub

Sub BumpByRef(ByRef x As Long)
    x = x + 5
End Sub

Sub BumpByVal(ByVal x As Long)
    x = x + 5
End Sub

Default to ByVal A procedure that quietly changes the variable you handed it is a horrible bug to track down. Write ByVal unless you specifically want the value changed - it is four extra characters and it removes the surprise.

Try these yourself

Write a Function UnitsOnRow(r) that returns the units for a row, and use it in a loop.
Write a Function that takes a region name and returns how many orders it has.
Rewrite the North report from Day 5 using one Sub and two Functions.
Show, with your own example, what changes when you swap ByRef for ByVal.
Explain the difference between a Sub and a Function to somebody who has never coded.

← PreviousDay 8 — Numbers, rounding and dates Next →Day 10 — Arrays, and why they make macros fast