Every Function Has a Return Type

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 has a return type even if you don't declare one explicitly

As it turns out, there are four different ways to declare the return types of your functions:

• GOOD: Explicitly
• LAZY-BAD: Implicitly
• CLEVER-BAD: Via Type-Suffix
• JUST PLAIN EVIL: Implicitly via Def Types

Explicitly Declared Return Types

Good programming practice is to explicitly declare the return types of your functions.  What does this mean?  It means including the As <datatype> clause at the end of your function declaration line.

For example:

Function GetCurrentYear() As Integer
    GetCurrentYear = Year(VBA.Date)
End Function

Implicitly Declared Return Types

If you don't explicitly declare a return type for your function, VBA will do it for you.  The default type in VBA is Variant.  So, if you declare a function, but don't explicitly declare a return type using the As <datatype> clause, your function will have an implicit type of Variant.

These two function declarations are identical to the compiler:

'Explicit return type of Variant
Function MyFunction() As Variant
End Function

'Implicit return type of Variant
Function MyFunction()
End Function

Even though they are identical to the compiler, they are not identical to someone reading your code.

In the first case, someone reading your code knows that you intend to return a Variant value.

In the second case, an inexperienced person reading your code probably does not realize that it is returning a Variant.  Even an experienced person is left wondering whether you realized that the function is returning a Variant.

Moral of the story: it's always better to explicitly declare your data types.

Type-Suffix Declared Return Types

While I would not recommend it, the third option for declaring return types is to use a type suffix.

A type suffix is one of the following characters that can be used to set the type of a function, variable, or literal:

%   Integer
&   Long
^   LongLong
!   Single
#   Double
@   Currency
$   String

Here's how you would use the type suffixes to declare return types for your functions:

Function VariantFunction()
End Function

Function IntegerFunction%()
End Function

Function LongFunction&()
End Function

#If Win64 Then
Function LongLongFunction^()
End Function
#End If

Function SingleFunction!()
End Function

Function DoubleFunction#()
End Function

Function CurrencyFunction@()
End Function

Function StringFunction$()
End Function

And here's the proof that they actually work:

The type suffix is optional when you call the function.  One thing you CANNOT do, though, is to use a different type suffix to coerce a function from one type to another.  See below:

DefType + Naming Convention Return Types

I wrote above that "the default type in VBA is Variant."  That is true.  But you can override those defaults by using the arcane and ill-advised Def-type syntax.

The basic idea is that you declare the type of all implicitly declared identifiers based on the first letter in their name.  In the example below, every function and variable that begins with the letter "A" and does not have an explicit type will implicitly be declared as a Boolean, based on the statement: DefBool A.

If that sounds insane and convoluted and just asking for trouble...that's because it is.  Don't do this.  It's evil.

...

Unless you're messing with a colleague who's too lazy to explicitly type his functions and you want to teach him a lesson.  In that case, by all means, have at it!

[MS-VBAL]: The VBA Language Specification

The VBA Language Specification is the authoritative document that lays out how VBA works at a very technical level.  Below are excerpts from a couple of relevant sections of the Spec.

5.3.1 Procedure Declarations

The declared type of a function declaration is defined as follows:

• If the <function-name> element of a function declaration is a <TYPED-NAME> then the declared type of the function declaration is the declared type corresponding to the <type-suffix> of the <TYPED-NAME> as specified in section 3.3.5.3.
• If the <function-name> element of a function declaration is not a <TYPED-NAME> and the function declaration does not include a <function-type> element its declared type is its implicit type as specified in section 5.2.3.1.5.
• If a function declaration includes a <function-type> element then the declared type of the function declaration is the specified type of the <function-type> element.

3.3.5.3 Special Identifier Forms

A <TYPED-NAME> is an <IDENTIFIER> that is immediately followed by a <type-suffix> with no intervening whitespace.

Static Semantics

• The name value of a <TYPED-NAME> is the name value of its <IDENTIFIER> elements.
• The declared type of a <TYPED-NAME> is defined by the following table:

5.2.3.1.5 Implicit Type Determination

An <IDENTIFIER> that is not explicitly associated with a declared type via either a <type-spec> or a <type-suffix> might be implicitly associated with a declared type. The implicit declared type of such a name is defined as follows:

• If the first letter of the name value of the <IDENTIFIER> has is in the character span of a <letter-spec> that is part of a <def-directive> within the module containing the <IDENTIFIER> then its declared type is as specified in section 5.2.2.
• Otherwise its implicit declared type is Variant.

5.2.2 Implicit Definition Directives

Implicit Definition directives define the rules used within a module (section 4.2) for determining the declared type (section 2.2) of implicitly typedentities (section 2.2). The declared type of such entities can be determined based upon the first character of its name value (section 3.3.5.1). Implicit Definition directives define the mapping from such characters to declared types.