Subs vs. Functions

Every Function in VBA Returns a Value

Whether you assign one explicitly or not, every function in VBA has a return value.

Mike Wolfe

Mar 10, 2023 • 4 min read

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:

A Function returns 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!

Python: return x
C: return x;
Java: return x;
C++: return x;
C#: return x;
VB.NET: Return x
JavaScript: return x;
SQL: SELECT x;
PHP: return $x;
Go: return x

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?}

The `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 GoSub call.

^{What's GoSub? you ask. It doesn't matter. It's evil. There is no need for it. Forget I brought it up.}

Because the 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:

Function Foo() As Integer
    Foo = 42
End Function

The function Foo returns the literal value 42.

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

The 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 Def Types

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 Variant,
and its return value will be Empty.

Function ContentsOfMyMind()
End Function

Image by Lisa Van Dorp from Pixabay