Argument Data Types
When you specify arguments for a procedure, they always default to Variant, which is the
default variable type in VBA. You can also declare your parameters as other data types,
choosing from the data types you saw in the last chapter.
The advantage of declaring with data types is that they introduce a discipline to your code
in terms of what information the procedure is looking for. If you do not specify a type but use
the default Variant, your procedure will accept anything, be it a number or a string. This can
have unfortunate consequences within that procedure if you are expecting a string and a
number gets passed across, or vice versa. If you specify the parameter as a string, an error
will occur if you do not pass a string:
Function Myfunction(Target as String)
This can also be useful if you are writing a custom function for use in a SQL query. When
users enter your function into their query, they must give the parameters according to the data
type specified. If a string is specified, they must put the value in quotes or they can request
the user input a value by using [Input Value] as a parameter in the function.
Optional Arguments
You can make specific arguments optional by using theOptionalkeyword:
Function Myfunction (Target as String, Optional Flag as Integer)
In this example, the user must specify the parameterTargetas a string, but the parameter
Flagwill appear with square brackets around it and need not be specified. All optional
parameters must come after the required ones.
Passing Arguments by Value
You can also use theByValorByRefkeyword to define how parameters are passed to your
procedure. The important distinction is that ByVal takes a copy of the variable that is being
passed and does not alter the original value of the variable within the subroutine or function.
ByRef uses the original variable itself and any changes to the value that are made within the
subroutine or function are reflected through the entire scope of the variable. The ByRef
methodology can often lead to bugs that are hard to track down. Your function may alter the
value of your variable, but this may not be apparent when you are looking at the chain of
your code.
It is generally accepted by programmers that ByRef is not a good idea unless you know
exactly what you are doing.
Function MyFunction (ByVal Target as String)
Chapter 3: Modules, Functions, and Subroutines 33