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:
Functionhas 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
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.
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
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
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
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
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:
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.
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 18.104.22.168.
- 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 22.214.171.124.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.
A <TYPED-NAME> is an <IDENTIFIER> that is immediately followed by a <type-suffix> with no intervening whitespace.
- 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:
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.
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 126.96.36.199). Implicit Definition directives define the mapping from such characters to declared types.