Page 118 - Excel Progamming Weekend Crash Course

h540629 ch07.qxd 9/2/03 9:33 AM Page 93

Session 7 — Procedures and Modules 93

Function Procedures

A function procedure is identical to a sub procedure in almost every respect, including the
way arguments are declared and the types of code it can contain. The difference is that a
function procedure returns a value to the calling program, where a sub procedure does not.
A function procedure is defined using the Function...End Function keywords:
Function FunctionName(arguments) As type
...
End Function
The type element specifies the data type of the value that the function returns and is
one of VBA’s data types, a user-defined type, or an enumerated type. If the As type portion
of the function definition is omitted, the return type defaults to Variant.
The value returned by a function is specified by assigning a value to the function name
within the body of the function:

Function MyFunction() As Integer
...
MyFunction = expression
End Function
This assignment is often at the end of the function, but it can also be located elsewhere
within the body of the function. Making this assignment does not in itself end the func-
tion. You can terminate the function early with the Exit Function statement.
If you do not assign a value to the function name, no error occurs. The value returned
by the function will in this case be the value for an uninitialized variable of the specified
return type (zero for numeric return types, a blank string for String and Variant return
types, and Nothing for object return types).
The fact that a function returns a value affects the way it is called. Because it returns a
value, a function call can be considered an expression and therefore can be used anywhere
an expression can be used. Function arguments must be enclosed in parentheses. Here are
some examples;
X = Func1() + Func2()
X = Func3(Func4())
Debug.Print Func5(a, b, c)

This code uses the Debug.Print statement to display data in the VBA
Editor’s Immediate Window. The Immediate Window is covered in Session 2.
Note
You can also call a function in the same way that a sub procedure is called (as detailed
earlier in this session). In this case the function’s return value is ignored.