VBA Brush Up 04：Passing Arguments to Procedures and Functions

來自：Julitta Korol，“Access.2003.Programming.by.Example.with.VBA.XML.and.ASP”，by Wordware Publishing, Inc. 2005, p52-p75

Technorati 標籤: byval,byref,argument,parameter,call,ismissing,optional,VBA

1. Once the function does something, the result is assigned to the function name. Notice that the function procedure’s name is used as if it were a variable.
2. By default, Visual Basic passes information to a function procedure (or a subroutine) by reference (ByRef keyword), referring to the original data specified in the function’s argument at the time the function is called. So, if the function alters the value of the argument, the original value is changed.If you want the function procedure to change the original value, you don’t need to explicitly insert the ByRef keyword, because passed variables default to ByRef.
   When you use the ByVal keyword in front of an argument name, Visual Basic passes the argument by value. It means that Visual Basic makes a copy of the original data. This copy is then passed to a function. If the function changes the value of an argument passed by value, the original data does not change — only the copy changes
3. Arguments that are optional come at the end of the argument list, following the names of all the required arguments. Optional arguments must always be the Variant data type. This means that you can’t specify the Optional argument’s type by using the As keyword.
4. The IsMissing function allows you to determine whether or not the optional argument was supplied. This function returns the logical value of True if the third argument is not supplied, and returns False when the third argument is given.
   1. Function Avg(num1, num2, Optional num3) 
   2.     Dim totalNums As Integer 
   3.     totalNums = 3 
   4.     If IsMissing(num3) Then 
   5.         num3 = 0 
   6.         totalNums = totalNums - 1 
   7.     End If 
   8.     Avg = (num1 + num2 + num3) / totalNums 
   9. End Function 

   Although you are not required to use the Call keyword when calling a procedure, you must use it when the call to the procedure requires arguments. The argument list must be enclosed in parentheses.

5. Arguments vs. Parameters
   • An argument is a variable, constant, or expression that is passed to a subprocedure.
   • A parameter is a variable that receives a value passed to a subprocedure.