Databases Reference
In-Depth Information
Chapter 11. Functions and Subroutines
VBA allows two kinds of procedures: functions and subroutines. The only difference
between a function and a subroutine is that a function returns a value, whereas a
subroutine does not.
11.1 Calling Functions
A function declaration has the form:
[Public or Private] Function
FunctionName
(
Param1
As
DataType1
, _
Param2
As
DataType2
,...) As
ReturnType
Note that we must declare the data types not only of each parameter to the function, but
also of the return type. Otherwise, VBA declares these items as variants.
I will discuss the optional keywords
Public
and
Private
later in this chapter, but you
can probably guess that they are used here to indicate the scope of the function, just as
they are used in variable declarations. For example, the
AddOne
function in Example 11-
1 adds 1 to the original value.
Example 11-1. The AddOne function
Public Function AddOne(Value As Integer) As Integer
AddOne = Value + 1
End Function
To use the return value of a function, we just place the call to the function within the
expression, in the location where we want the value. For instance, the code:
MsgBox "Adding 1 to 5 gives: " & AddOne(5)
produces the message box in Figure 11-1, where the expression
AddOne(5)
is replaced by
the return value of
AddOne
, which in this case is 6.
Figure 11-1. The message dialog box displayed by Example 11-1
Note that, in general, any parameters to a function must be enclosed in parentheses within
the function call.
