What is the difference between a sub
and a function?
They are both procedures, but a function returns a value
and a sub does not. A bit more precisely, a function can
return a value and a sub can not.
A sub can however, take an argument by reference and modify it,
something some consider the same as the sub returning a value. Sometimes
you hear this as a return by reference.
So, there's not actually a lot of difference in the functionality
between a sub and a function. However, there is quite a bit of
difference in where you can use on or the other, and that may make the
difference in which you decide to use:
- You can not call a sub from a query,
only a function.
- You can not call a sub from a menu,
only a function.
- You can not call a user-created sub
from the Form's event property dialog, only a function1.
Since you can have a function anywhere you can
have a sub, but not vice-versa, some people choose to only write
functions. Since Access will generate subs, this has the additional
benefit of setting off Access-created code from user-created code. Some
prefer to stick to the rule that if it returns a value it is a function
and if it does not it is a sub. Either way is fine, choose whatever you
Here is an example of returning a value in a function:
The point to understand is
that we "set" the variable that has the same name as the function, this
is the VBA way, where other languages may use a "Return" statement or
Public Function ReturnMyName() As String
ReturnMyName = "Jason"
Here is an example of "returning a value" in a sub:
The point to understanding
is that we aren't returning a value in the sense that the routine
itself evaluates to the return value (which is the case for a
function), we are simply setting the value of a passed in variable.
Public Sub WhatIsMyName(ByRef strName As String)
strName = "Jason"
This means that:
Here are my general rules
of thumb for sub vs. function:
Public Sub Test()
' Ex. 1: This is all you need, because the function evaluates to "Jason"
' Ex. 2: But if you like, you can use the return value to set a variable:
Dim strName0 As String
strName0 = ReturnMyName
' Ex. 3: But for the sub, you need a "holder" variable for the name:
Dim strName1 As String
' Ex. 4: But you CAN NOT do this, because these paras force pass by value
Dim strName2 As String
1. Prefer functions over subs
2. If a function is only called once in a procedure, Ex. 1 - style
calling is acceptable. Else, use a "holder" variable, as in Ex. 2
1Actually you can call a sub here, using =[MySub]
notation, but it will call the sub multiple times and therefore,
probably useless for actual code.