Functions

A VBA function starts with the FUNCTION statement and finishes with the END FUNCTION statement. All other statements that lie between the FUNCTION and END FUNCTION statements, will be executed by the function, when called to do so.

In the following function syntax example:

• Every placeholder shown inside arrow brackets ( <placeholder>) should be replaced in any actual code with the value of the item that it describes. The arrow brackets and the word they contain should not be included in the statement, and are shown here only for your information.

• Statements shown between square brackets ( [ ]) are optional. The square brackets should not be included in the statement, and are shown here only for your information.

A typical VBA function is structured like in the following example:

Function <FunctionName> ( [ Byval ] [ <Argument/s> ] ) [ As <ReturnDataType> ]
<statement>
<statement>
<statement>
[ <FunctionName> = <value> ]
End Function

Where:

• Function is the required function statement basic keyword

• [ Byval ] is the optional parameter for the argument;

• <FunctionName> represents the required name of the function being created

• <Argument/s> represents the optional argument/s of the function

• <ReturnDataType> represents the optional return data type of the function

• <statement> represents the executable VBA script statement/s

• = <value> represents the optional assignment of the return value for the function

• End Function is the function terminating statement

The name given to the function, immediately follows the FUNCTION keyword, and is used to identify the function to VBA. This name is referred to when the function is called upon (called) to be executed (perform the statements it contains) by some other procedure in VBA.

Function names can contain the letters 'A' to 'Z' and 'a' to 'z', the underscore '_' and digits '0' to '9'. The function name must begin with a letter, be no longer than 40 characters, cannot contain the space character, and cannot be a reserved word. Function names (once declared), become a keyword in VBA. Like most keywords in VBA, these names are not case sensitive.

The function name always ends with a pair of parentheses ( ) which may or may not contain one or more arguments required by (necessary for use in) the function. Multiple arguments if used, are separated by commas ( , ). See the section titled 'Arguments in VBA' for more details and argument syntax.

All the lines located between the FUNCTION and the END FUNCTION statements, contain the statements that will be executed when the function is called in VBA. These statements will be executed one at a time in logical order from top to bottom within the function.

The return value of the function is optionally assigned within the function in a statement using the function name. This value is often used within the calling procedure to determine the status of the function. Commonly, this value may be a Boolean True or False to indicate the successful completion or not of the function.

See Also

Subroutines and Functions

Arguments

Subroutines

Accessing Functions in DLLs