Page 117 - Excel Progamming Weekend Crash Course

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

92 Saturday Morning

The second method omits the Call keyword and uses the procedure name alone:

procname arglist
Note that when the Call keyword is omitted, the parentheses around the arguments are
also omitted.
When a procedure takes arguments, each argument must be an expression that evaluates
to the correct data type (the type defined for the argument). To pass an array, use the array
name followed by empty parentheses.

Argument Type Checking
Each procedure argument has a defined data type. When the program passes a variable as
an argument to a procedure, the type of the variable is checked against the argument’s
declared type. If the types differ, a “ByRef Argument Type Mismatch” error occurs. The fol-
lowing code snippet causes this error (at the Call statement):

Sub MySub(val As Single)
...
End Sub
Dim X As Long
X = 12345
Call MySub(X)
There are two ways to get around this problem. One is to use VBA’s data conversion func-
tions, which convert an expression to a specified data type. Thus, the CSng function con-
verts an expression to type Single and could be used in the above example as follows:

Call MySub(CSng(X))
You can refer to the VBA documentation for details on the other data conversion
functions.
The other way to avoid type-checking errors is to enclose the argument in an extra set
of parentheses, as shown here:

Call MySub((X))
The parentheses convert the argument into a typeless expression, so the type mismatch
error is not triggered.

The Insert ➪ Procedure Command

The easiest way to add a new procedure to a VBA module is with the Insert ➪
Procedure command. In the dialog box that’s displayed, you enter the name of
the procedure and select whether it is a sub or a function procedure. The editor
enters the outline of the procedure in the module. You then edit the procedure
to add arguments and code.