Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 37 ยท Control Flow and Procedures

Procedures and Functions: Sub, Function and Arguments

Write reusable VBA code with Sub and Function procedures: parameters, return values, ByVal vs ByRef, Optional and ParamArray, calling rules, Exit and UDFs.

As programs grow you split them into small named pieces called procedures. VBA has two main kinds: a Sub does something, a Function does something and returns a value. Good procedures are short, have one job and can be reused.

Sub procedures

VB
Sub ShowGreeting(ByVal who As String)
    MsgBox "Hello, " & who & "!"
End Sub

Sub Main()
    ShowGreeting "Ada"
    ShowGreeting "Grace"
End Sub

who is a parameter. The value you pass when calling ("Ada") is the argument. A Sub called as a statement takes its arguments without parentheses: ShowGreeting "Ada". With Call you do use them: Call ShowGreeting("Ada").

Function procedures

A Function returns a value by assigning to its own name:

VB
Function AddTax(ByVal price As Double, ByVal rate As Double) As Double
    AddTax = price * (1 + rate)
End Function

Sub Demo()
    Dim gross As Double
    gross = AddTax(100, 0.2)          ' parentheses required when using the result
    Debug.Print gross                 ' 120
End Sub

Notes:

  • Declare the return type with As Double after the parameter list. Without it, the function returns a Variant.
  • The assignment AddTax = ... does not end the function; code continues. Use Exit Function to leave early.
  • If you never assign, the function returns the default value of its type.
  • A function can return objects too; assign with Set: Set GetSheet = Worksheets("Data").

Return Boolean for checks

VB
Function IsWeekend(ByVal d As Date) As Boolean
    IsWeekend = (Weekday(d, vbMonday) >= 6)
End Function

Functions that answer yes/no should be named like a question: IsWeekend, HasHeader.

ByVal versus ByRef

By default VBA passes arguments by reference (ByRef): the procedure receives the original variable and can change it. ByVal passes a copy.

VB
Sub AddOne(ByRef n As Long)
    n = n + 1
End Sub

Sub Test()
    Dim count As Long
    count = 5
    AddOne count
    Debug.Print count     ' 6, the original changed
End Sub

Change ByRef to ByVal and count would stay 5.

ByVal ByRef (default)
Receives a copy the variable itself
Caller's variable changed? no yes
Use for inputs outputs, large arrays/strings

Write ByVal explicitly for plain inputs so nobody changes the caller's variable by accident. Use ByRef when you deliberately want to return several values:

VB
Sub MinMax(values() As Long, ByRef lo As Long, ByRef hi As Long)
    Dim i As Long
    lo = values(LBound(values)): hi = lo
    For i = LBound(values) + 1 To UBound(values)
        If values(i) < lo Then lo = values(i)
        If values(i) > hi Then hi = values(i)
    Next i
End Sub

Passing an expression (AddOne (count)) in extra parentheses secretly turns ByRef into a copy: a common source of confusing bugs.

Optional and default values

VB
Function Greeting(ByVal who As String, Optional ByVal punctuation As String = "!") As String
    Greeting = "Hello, " & who & punctuation
End Function

Debug.Print Greeting("Ada")            ' Hello, Ada!
Debug.Print Greeting("Ada", "?")       ' Hello, Ada?

Optional parameters must be last. Give them a default, or declare as Optional x As Variant and test IsMissing(x).

Named arguments

Pass arguments by name with := in any order, which is clearer for long lists:

VB
Debug.Print Greeting(punctuation:="...", who:="Ada")

ParamArray: any number of arguments

VB
Function SumAll(ParamArray nums() As Variant) As Double
    Dim v As Variant
    For Each v In nums
        SumAll = SumAll + v
    Next v
End Function

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

ParamArray must be the last parameter and arrives as a Variant array.

Custom worksheet functions (UDFs)

A public Function in a standard module can be used in a cell formula:

VB
Function Fahrenheit(ByVal celsius As Double) As Double
    Fahrenheit = celsius * 9 / 5 + 32
End Function

In a cell, =Fahrenheit(A1) returns 212 when A1 holds 100. A UDF called from a cell cannot change other cells, format, or select anything; it can only return a value.

Scope and structure

  • Public (default) and Private apply to procedures (scope).
  • Exit Sub and Exit Function leave early.
  • Sub names appear in the Macros dialog only when they take no arguments and are Public.
  • Keep one task per procedure, 5 to 30 lines, with a clear name: ReadSettings, BuildReport.

Sub or Function?

Sub Performs actions. No return value.
Function Returns a value you can use.
In a cell Only Functions work as formulas.
1 / 2

Stand-in: functions, defaults and rest arguments

The JavaScript stand-in (not VBA) mirrors AddTax, Optional defaults, ParamArray and the ByRef-style output through an object.

JS Stand-in: Optional, ParamArray, ByRef

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How does a Function return its value?

    Write FunctionName = result; execution continues until End Function or Exit Function.
  2. 2. What is the default way VBA passes arguments?

    Unless you write ByVal, the argument is passed by reference.
  3. 3. Where must Optional parameters be placed?

    Optional parameters follow the required ones; ParamArray comes last.
  4. 4. What can a UDF used in a cell NOT do?

    A function called from a worksheet formula can only return a value.

Summary

  • Sub performs actions; Function returns a value via its name.
  • ByVal passes a copy, ByRef (default) the original: write ByVal for inputs.
  • Optional, named arguments and ParamArray make flexible signatures.
  • Public Functions can be used as worksheet formulas (UDFs).

Next: Objects and the Object Model