Difference Between Sub and Function

The difference between when to use Sub and Function in VBA can be confusing.  Let’s take a look at some of the differences between the two and when they should be used.  Using these two features correctly can greatly increase the flexibility of your designs.

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

Using Function

A function is similar 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.  

Example

In the next post I am going to provide a few examples of how a Sub or Macro work versus how a function works. I’d like to take a look at how a function works from both a sub call and from a cell reference call. Once you learn to write your own custom functions, it can make large multi-nested if statements much easier to read and understand. If you building large formulas all the time, this next post may be for you.