You can think of a Sub procedure as a command that can be executed either by the user or by another procedure. Function procedures, on the other hand, usually return a single value (or an array), just like Excel worksheet functions and VBA built-in functions. As with built-in functions, your Function procedures can use arguments.
Function procedures are quite versatile and can be used in two situations:
As part of an expression in a VBA procedure
In formulas that you create in a worksheet
In fact, you can use a Function procedure anywhere that you can use an Excel worksheet function or a VBA built-in function. As far as I know, the only exception is that you can't use a VBA function in a data validation formula.