Page 114 - Excel Progamming Weekend Crash Course

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

Session 7 — Procedures and Modules 89

Procedure Arguments

An argument is a piece of data that is passed to a procedure when the procedure is called. A
procedure can be defined to take as many arguments as are needed. When defining the pro-
cedure, the argument list should contain one element for each argument. Each element has
the following syntax:

argumentname As type
Argumentname is the name of the argument, following VBA’s naming rules. Type is the
data type of the argument. An argument can be any of VBA’s data types, an object type
from the Excel object model, a user-defined type, or an enumeration type. If the data type
is not specified, the argument defaults to type Variant. Multiple arguments are separated
by commas, as shown in the following example:

Sub MyProcedure(count As Integer, deadline As Date, overdue As Boolean)
...
End Sub

To pass an array as an argument, use an empty set of parentheses following the argu-
ment name. The following specifies one argument that is an array of type Single:

Sub AnotherSub(data() As Single)
Within the body of the procedure, each argument is available as a variable of the speci-
fied type and initially has the value that was assigned to the argument (see the following
section, “Calling Procedures”).

Assign argument names that are descriptive of the data the argument holds.

Tip

Optional Arguments
By default, procedure arguments are required — that is, when the procedure is called, a value
must be passed for each of the procedure’s defined arguments or an error occurs. You can,
however, define one or more procedure arguments as optional. The syntax is:

Optional argname As type = default
Argname and type are the same as for nonoptional argument. You can assign a default
value to the argument — this is the value the argument will have in the procedure if a value
is not passed when the procedure is called. If you do not want to assign a default value,
omit the = default part of the argument definition. In the procedure definition, all
optional arguments must come at the end of the argument list, following any nonoptional
arguments.
Execution exits a procedure when it reaches the End Sub statement. You can
also exit a procedure by executing the Exit Sub statement anywhere within
Tip the procedure.