Subs vs. Functions in VBA

A common question many new VBA developers ask is,

What is the difference between a Sub and a Function and why would I use one or the other?

There's a short, straightforward answer to this question...and then a whole bunch of caveats and "well, ackshully's".

The Straightforward Answer

A Function has a return value. A Sub does not.

If you want your procedure to return a value use a Function.  If you just need to execute some code but not return a value, then use a Sub.

The Caveats, Exceptions, and Whatnot

If you are brand new to VBA, you should stop reading at this point.  You know all you need to know about Subs and Functions at this point.

Write some code.  Get your feet under you.  Get comfortable with the basics of the language.  Then come back and read the rest of the article.  I know I can't stop you, but you risk leaving more confused than when you arrived if you keep reading.

In no particular order, here is a list of additional facts about Subs and Functions (I'll expand on each one with their own article over time):

• Sometimes you MUST use a Sub (e.g., event handlers)
• Sometimes you MUST use a Function, even if you don't need its return value (e.g., when calling from a form/report/property control, etc.)
• Sometimes you MUST use a Sub, even if you do need its return value (e.g., ribbon callbacks)
• You can "return" values from a Sub via a parameter passed by reference
• A Function has a return type even if you don't declare one explicitly
• A Function returns a value even if you don't return one explicitly
• You can return multiple values from a Sub
• You can return multiple values from a Function
• (Almost) every Sub could be a Function...but that doesn't mean it's a good idea

A NOTE ABOUT TERMINOLOGY:  In this blog, I tend to use the terms "Procedure" and "Routine" as aliases for "a Sub or Function."