This article is one in a series on Subs vs. Functions in VBA.
One of the "additional facts" from my Subs vs. Functions article was this:
Functionreturns a value even if you don't return one explicitly
This is closely related to the fact that every
Function has a return type, whether you set one or not.
Explicitly Returning a Value from a Function
Programmers from other languages who use VBA often have trouble figuring out how to return a value from a function.
Why? Because almost every other programming language on the planet uses the
Return keyword to return the value of a function.
This includes 9 of the top 10 languages currently listed in the TIOBE index, including VB.NET!
The lone exception in the above list is SQL, and TIOBE is honestly stretching the definition of a programming language by including it in the list.
Welp, apparently SQL is Turing Complete–though only if you include its support for common table expressions; who knew?
Return Keyword in VBA
VBA does have a
Return keyword. The problem is that it's used to return to the call site of a
What's GoSub? you ask. It doesn't matter. It's evil. There is no need for it. Forget I brought it up.
Return keyword is being used elsewhere, we still needed some way to return the value of a function in VBA.
Setting the Value to the Name of the Function
The way to return a value from a function in VBA is to assign a value to a variable that shares the name of the function. Here's how the VBA Language Specification defines it in Section 5.3.1 Procedure Declarations:
A function declaration implicitly defines a local variable, known as the function result variable, whose name and declared type are shared with the function and whose scope is the body of the function.
Here's an example:
Exiting a Function in VBA
In most other programming languages, the return keyword does double duty; it sets the return value for the function AND it exits immediately.
In VBA, these two duties remain separate. If you want to set a Function's return value and exit immediately in VBA, you need to use two different lines of code:
Function WhatIsToday() As String If Month(Date) = 3 And Day(Date) = 15 Then WhatIsToday = "The Ides of March. Caesar, look out!" Exit Function End If WhatIsToday = "Today is " & Format(Date, "mmmm d") End Function
If we remove the
Exit Function line in the above
WhatIsToday() function, then on March 15 the function would return "Today is March 15" rather than the all-important warning to Julius Caesar.
In fact, that exact programming error is what led to Caesar's untimely death. Really, it should surprise no one to know that VBA was in fact around in 44 B.C. and that it has not changed one bit in all that time.
Implicitly Returning a Value from a Function
I said at the beginning that every function in VBA returns a value.
But what if you never assign a value to the implicit variable that shares the name of the function? In that case, what gets returned is the default value of the Function's type.
Function Baz() As Integer End Function
Baz() above returns 0 because that is the default value of the Integer data type.
The default values for each VBA data type are defined in Section 2.3, Variables, of the VBA Language Specification:
Implicitly Determining the Type of a Function
Of course, in order to know the default value of a function, you need to know its return type.
It's easy to tell what the return type of a function is if you've explicitly declared it using the
As keyword, like with the Baz() function above. However, if you don't use the
As keyword to define your function's type, it gets trickier. Most of the time, if you've left off the
As keyword, your function will be implicitly typed as a
Variant data type. That's not a guarantee, though.
It turns out there are four ways to set a Function's return type in VBA:
- GOOD: Explicitly
- LAZY-BAD: Implicitly
- CLEVER-BAD: Via Type-Suffix
- JUST PLAIN EVIL: Implicitly via
See my article, Every Function Has a Return Type, for more details:
The moral of the story is,
Always explicitly declare the return type of a function.
The Default-est Value is Empty
If you create a new Access database,
and declare a function with no explicit return type,
and never assign a value to be returned,
that function will be implicitly typed as a
and its return value will be
Function ContentsOfMyMind() End Function