Subs vs. Functions in VBA
What is the difference between a Sub and a Function and why would you use one or the other? I'll give you the short answer...and then we can explore the long answer.
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
Subvia a parameter passed by reference - A
Functionhas a return type even if you don't declare one explicitly - A
Functionreturns 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
Subcould be aFunction...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."
