Feb 12 2009

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.  


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.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 


2 Comments on this post


  1. K Teeters said:

    I’m a nut that EXCEL’d my IRS records for years (10? using .wks 1st)
    Now the IRS has destroyed the 0 to 100k tax rate
    Hzlookup could always find a tax bracket
    (ETC has always been political)
    But the SocialSecurity worksheet has always been simple.
    Income, credits, Amount 50% taxed,..85% taxed..= taxed
    SS for 1040 line 20

    1040-D , and D1 and the Qualified Div worksheet have now been
    complicated by the IRS Forcing their table LOOKup …
    for Single rates of 10% to 22% in a NON-uniform rate …
    So now the Sched-D problem is reduced to matching
    BUYs&Sells, calculating Gains/Losses and ST/LongTerm

    Maybe the quick answer is buy quicken…but I’ve already
    created a year-long sheet of Daily trades…CREE, IBM, YHOO …

    a macro to copy ALL trades into a 2nd sheet..sorted by
    issue, sub-Option ( March-IBM) and trade dates..
    and all kinds of G/L ( pivot tables…almost worked )

    BUT I’ve NOT been able to macro/sub/function the
    1040-D Line Summation of several trades…

    Issue Buy-$, Sell+$, StartDate, End Date (Gain) (Loss) Net

    Consider Holding 500 shares of CREE, ( base cost $10 )
    with the following OPTION trades. ( 5 max..safely)

    date #1
    Sell (-3) CREE-Jan$10 calls for =$2
    Jan2010 and Jan 2011 are entirely diff symbols

    Sell (-1) CREE-Jan$10 calls for =$3

    Buy (-2) CREE-Jan$10 calls for =$2.50
    (close OBLIGATION of 1 sale)

    (IF) CREE expires an below $10 the IRS 1040-D lines become:
    matching BUYs to Sales:

    This is NO-Longer Buy 1st , Sell next results =..

    CreeJan10calls (2) +2.00 , -2.50 ,Date#1, Date#2 -$100
    CreeJan10calls (1) +2.00 , -0 ,Date#1, Date#4 +200
    CreeJan10calls (1) +3.00 , -0 ,Date#2, Date#4 +300

    ..and IF date #1 & #5 are different years… a deeper problem.

    THE IRS should pay YOU for this
    ( and other FREE worksheet solutions)
    its not that you&I are competing with QUICKEN

    I’ve NOT found QUICKEN as a solution, or any LESS programing
    than fighting the DATA and the IRS

    Don’t even need pretty PDF formatted data

    Yes, NOT a trivial problem,
    just a list of several sequential FUNCTIONs.

    January 30th, 2010 at 1:53 pm
  2. john said:

    Hey K Teeters… that is quite a comment. I’ll have to look more at your question and see if we can find a solution. I am definitely not a tax expert though… anyone have any suggestions on that?

    January 30th, 2010 at 4:03 pm


You must be logged in to post a comment.

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks