I've been working with Access for a while now, and although I understand the obvious benefit of a Function over a Sub, been that it can return values as a result, I'm not sure as to why I should use a Sub over a Function. After all unless I'm mistaken; Functions can do everything Subs can do?
Note: I'm fully aware of how to use both Sub's and Function's so not looking for an explanation of how they work.
I found one other difference, at least on Excel but likely other Office apps. If you want to customize the ribbon by adding a button to launch a VB program, when you choose Macros in the "Choose commands from" dropdown menu, it lists any Subs in your code but not Functions. Note that a Private Sub will also be hidden from the customize ribbon selection, as will a Public Function.
So to summarize, these will be available to add as buttons on the ribbon: Sub, Public Sub
And these will not be available to add: Function, Public Function, Private Function, Private Sub
