Sub, Function and passing arguments

Choose between Sub and Function, understand the ByRef default that bites everybody, and scope procedures across modules.

Sub, Function and returning values

Option Explicit

' A Sub performs an action and returns nothing to the caller.
Public Sub WriteReport(ByVal ws As Worksheet)
    ws.Range("A1").Value = BuildTitle("report")
End Sub

' A Function returns a value through its own name.
Public Function BuildTitle(ByVal topic As String) As String
    BuildTitle = "Monthly " & topic        ' assign to the function name
    Exit Function                          ' an early exit still returns what is set
End Function

' A Function you can call from a worksheet cell must be Public and in a
' standard module, with no side effects on other cells.
Public Function Band(ByVal score As Double) As String
    Select Case score
        Case Is >= 90: Band = "A"
        Case Is >= 80: Band = "B"
        Case Else:     Band = "F"
    End Select
End Function

' A Property is the third kind of procedure, used on classes
' Private mName As String
' Public Property Get Name() As String: Name = mName: End Property
' Public Property Let Name(ByVal v As String): mName = v: End Property

• Assign to the function name, not to a local variable with the same name only in your head. Reading the function name inside its own body is a recursive call, which is how accidental infinite recursion starts.
• Exit Function before assigning leaves the function at its default: an empty string, zero, False, or Nothing. Always set a value on every path.
• A worksheet function cannot change another cell, format anything, or open a file. Excel calls it during recalculation and the side effect either does nothing or causes a circular reference.
• Use Sub for anything that acts and Function for anything that computes. A Sub that returns a value through a ByRef parameter is harder to read than a Function.

ByVal, ByRef and optional arguments

' The default is ByRef: the callee can change the caller's variable.
Sub Bumps(ByRef n As Long)
    n = n + 1
End Sub

Sub DoesNotBump(ByVal n As Long)
    n = n + 1                     ' changes a local copy only
End Sub

Sub Demo()
    Dim v As Long
    v = 10
    Bumps v                       ' no parentheses needed for a Sub call
    Debug.Print v                 ' 11

    v = 10
    DoesNotBump v
    Debug.Print v                 ' 10
End Sub

' An unqualified call with parentheses on a single argument evaluates and
' copies, which silently turns an intended ByRef into ByVal:
'   Bumps (v)      -> v stays 10. A classic and invisible bug.

' Optional and named arguments
Public Function FormatAmount(ByVal amount As Double, _
                             Optional ByVal currency As String = "EUR", _
                             Optional ByVal decimals As Integer = 2) As String
    FormatAmount = Format$(amount, "0." & String$(decimals, "0")) & " " & currency
End Function

Sub UseIt()
    Debug.Print FormatAmount(12.5)
    Debug.Print FormatAmount(12.5, "USD")
    Debug.Print FormatAmount(12.5, decimals:=0)      ' named arguments
    Debug.Print FormatAmount(currency:="GBP", amount:=9)
End Sub

' ParamArray must be the last parameter and is always ByVal.
Public Function SumAll(ParamArray values() As Variant) As Double
    Dim i As Long, total As Double
    For i = LBound(values) To UBound(values)
        total = total + CDbl(values(i))
    Next i
    SumAll = total
End Function

' Debug.Print SumAll(1, 2, 3, 4)

Declare every parameter ByVal unless you specifically intend to write back to the caller's variable. It costs nothing for objects, makes the intent visible, and removes an entire category of surprise.

Scope between modules

' In Module1
Option Explicit
Public gCounter As Long            ' visible to every module in the project
Private mCache As Collection       ' visible only inside this module

Public Sub PublicEntry()
    DoWork                          ' a Private Sub is still callable from this module
End Sub

Private Sub DoWork()
    gCounter = gCounter + 1
End Sub

' In Module2
Option Explicit
Public Sub CallAcrossModules()
    Module1.PublicEntry             ' qualify the call: the module is the namespace
    Module1.gCounter = 0
    ' Module1.DoWork                ' compile error: it is Private
End Sub

' A module-level variable persists for the life of the project. In a
' standard module it resets when the project is reset (End, or a code edit).
' Static keeps a value across calls but only inside the one procedure.
Public Sub CountCalls()
    Static calls As Long
    calls = calls + 1
    Debug.Print "call #" & calls
End Sub

• In a standard module, a module-level variable resets when the project is reset, which happens on End, on an unhandled error, or when you edit the code. Never treat one as persistent storage.
• A variable declared in a class module lives per instance, which is the correct place for state shared by several procedures.
• Sheet1 and ThisWorkbook are class modules, not standard modules. Macros the user can run belong in a standard module.
• Avoid Public module-level variables as a communication channel between modules. A Function with a parameter makes the dependency explicit and testable.

FAQ

Related

Control flow: If, Select Case and loops Error handling and the classic pitfalls

Last refreshed 2026-09-18.