What is the difference between Subroutine and Function in VBA

Excel VBA Sub Routine Vs Function

The Sub(routine) can NOT return a value. But a Function can return a value. If you want to call them then a Function can be called by it’s name with variables in parenthesis and  a Subroutine can be called only by writing it name with variables without parenthesis as explained below.

Subroutine (Sub) Syntax with Example:                                                              

Sub  Add(x as integer, y as integer, z as integer)

z= x+y

End Sub

'Calling Subroutine

Add  x, y, z
when it will return result will be saved in z variable, so it has not returned anything.

Function Syntax with Example:                                                                 

Function  Add(x as integer, y as integer) as integer

Add= x+y        

End Function

'Calling Function

T=Add( x, y)

when it will return result will be stored in ‘T’ variable. The function always returns a value so its value must be saved in some variable

Explanation:
Sub:

The easiest way to think of a Sub vs a Function is that a Function can return a value and a Sub cannot. A Sub can be thought of as a small program that performs some action that is contained within the program. 

Subs can be used to update a cell or perform an import and calculation, but the result can’t be returned to another sub or function. Another thing to note is that a sub (or macro) cannot be accessed directly by a cell reference. 

For example, when a cell is used to show today’s date, =Today(), that formula is also a built-in function, not a sub.

Function :

A Function is similar to a Sub, except that a Function can return a value. It may be easier to think of a function as similar to a formula in excel. You can provide the function the necessary inputs and the function returns the desired value. 

You can build a custom function for just about any action and then access that function from either a call from a macro, or a direct reference from a cell. 

Thanks, TAMATAM