Difference Between Sub and Function
- 0 Comments
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.
[ad#in-post-ad]
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.