Sub vs Function

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 function¹.

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 like.

Here is an example of returning a value in a function:

Code:

Public Function ReturnMyName() As String
  ReturnMyName = "Jason"
End 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 other method.

Here is an example of "returning a value" in a sub:

Code:

Public Sub WhatIsMyName(ByRef strName As String)
  strName = "Jason"
End 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.

This means that:

Code:

Public Sub Test()
    ' Ex. 1: This is all you need, because the function evaluates to "Jason"
    Debug.Print ReturnMyName
    
    ' Ex. 2: But if you like, you can use the return value to set a variable:
    Dim strName0 As String
    strName0 = ReturnMyName
    Debug.Print strName0
    
    ' Ex. 3: But for the sub, you need a "holder" variable for the name:
    Dim strName1 As String
    WhatIsMyName strName1
    Debug.Print strName1
    
    ' Ex. 4: But you CAN NOT do this, because these paras force pass by value
    Dim strName2 As String
    WhatIsMyName (strName2)
    Debug.Print strName2
End Sub

Here are my general rules of thumb for sub vs. function:
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

¹Actually you can call a sub here, using =[MySub] notation, but it will call the sub multiple times and therefore, probably useless for actual code.