Functions and Subroutines

You can create two types of procedures in Visual Basic–functions and subroutines-also known as Function procedures and Sub procedures. (As you’ll learn later in “Understanding Class Modules” on page 1009, class modules also support a special type of function, Property Get, and special subroutines, Property Let and Property Set, that let you manage properties of the class.) Each type of procedure can accept parameters-data variables that you pass to the procedure that can determine how the procedure operates. Functions can return a single data value, but subroutines cannot. In addition, you can execute a public function from anywhere in Access, including from expressions in queries and from macros. You can execute a subroutine only from a function, from another subroutine, or as an event procedure in a form or a report.

Function Statement

Use a Function statement to declare a new function, the parameters it accepts, the variable type it returns, and the code that performs the function procedure.

Syntax

 [Public | Private | Friend] [Static] Function functionname   ([<arguments>]) [As datatype]     [<function statements>]      [functionname = <expression>]      [Exit Function]      [<function statements>]      [functionname = <expression>]  End Function     

where <arguments> is

 {[Optional][ByVal | ByRef][ParamArray] argumentname[()]   [As datatype][= default]},...

Notes

Use the Public keyword to make this function available to all other procedures in all modules. Use the Private keyword to make this function available only to other procedures in the same module. When you declare a function as private in a module, you cannot call that function from a query or a macro or from a function in another module. Use the Friend keyword in a class module to declare a function that is public to all other code in your application but is not visible to outside code that activates your project via automation.

Include the Static keyword to preserve the value of all variables declared within the procedure, whether explicitly or implicitly, as long as the module containing the procedure is open. This is the same as using the Static statement (discussed earlier in this chapter) to explicitly declare all variables created in this function.

You can use a type declaration character at the end of the functionname entry or use the As datatype clause to declare the data type returned by this function. Valid datatype entries are Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String (for variable-length strings), String * length (for fixed-length strings), Object, Variant, or one of the object types described earlier in this chapter. If you do not declare a data type, Visual Basic assumes that the function returns a variant result. You can set the return value in code by assigning an expression of a compatible data type to the function name.

You should declare the data type of any arguments in the function’s parameter list. Note that the names of the variables passed by the calling procedure can be different from the names of the variables known by this procedure. If you use the ByVal keyword to declare an argument, Visual Basic passes a copy of the argument to your function. Any change you make to a ByVal argument does not change the original variable in the calling procedure. If you use the ByRef keyword, Visual Basic passes the actual memory address of the variable, allowing the procedure to change the variable’s value in the calling procedure. (If the argument passed by the calling procedure is an expression, Visual Basic treats it as if you had declared it by using ByVal.) Visual Basic always passes arrays by reference (ByRef).

Use the Optional keyword to declare an argument that isn’t required. All optional arguments must be the Variant data type. If you declare an optional argument, all arguments that follow in the argument list must also be declared as optional. You can specify a default value only for optional parameters. Use the IsMissing built-in function to test for the absence of optional parameters. You can also use the ParamArray argument to declare an array of optional elements of the Variant data type. When you call the function, you can then pass it an arbitrary number of arguments. The ParamArray argument must be the last argument in the argument list.

Use the Exit Function statement anywhere in your function to clear any error conditions and exit your function normally, returning to the calling procedure. If Visual Basic runs your code until it encounters the End Function statement, control is passed to the calling procedure but any errors are not cleared. If this function causes an error and terminates with the End Function statement, Visual Basic passes the error to the calling procedure. See “Trapping Errors” on page 1028 for details.

Example

To create a function named MyFunction that accepts an integer argument and a string argument and returns a double value, enter the following:

 Function MyFunction (intArg1 As Integer, strArg2 As _     String) As Double      If strArg2 = "Square" Then        MyFunction = intArg1 * intArg1      Else        MyFunction = Sqr(intArg1)      End If  End Function

Sub Statement

Use a Sub statement to declare a new subroutine, the parameters it accepts, and the code in the subroutine.

Syntax

 [Public | Private | Friend] [Static] Sub subroutinename   ([<arguments>])     [ <subroutine statements> ]     [Exit Sub]      [ <subroutine statements> ] End Sub 

where <arguments> is

 {[Optional][ByVal | ByRef][ParamArray]    argumentname[()] [As datatype][ = default]},...

Notes

Use the Public keyword to make this subroutine available to all other procedures in all modules. Use the Private keyword to make this procedure available only to other procedures in the same module. When you declare a sub as private in a module, you cannot call that sub from a function or sub in another module. Use the Friend keyword in a class module to declare a sub that is public to all other code in your application but is not visible to outside code that activates your project via automation.

Include the Static keyword to preserve the value of all variables declared within the procedure, whether explicitly or implicitly, as long as the module containing the procedure is open. This is the same as using the Static statement (discussed earlier in this chapter) to explicitly declare all variables created in this subroutine.

You should declare the data type of all arguments that the subroutine accepts in its argument list. Valid datatype entries are Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String (for variable-length strings), String * length (for fixed-length strings), Object, Variant, or one of the object types described earlier in this chapter. Note that the names of the variables passed by the calling procedure can be different from the names of the variables as known by this procedure. If you use the ByVal keyword to declare an argument, Visual Basic passes a copy of the argument to your subroutine. Any change you make to a ByVal argument does not change the original variable in the calling procedure. If you use the ByRef keyword, Visual Basic passes the actual memory address of the variable, allowing the procedure to change the variable’s value in the calling procedure. (If the argument passed by the calling procedure is an expression, Visual Basic treats it as if you had declared it by using ByVal.) Visual Basic always passes arrays by reference (ByRef).

Use the Optional keyword to declare an argument that isn’t required. All optional arguments must be the Variant data type. If you declare an optional argument, all arguments that follow in the argument list must also be declared as optional. You can specify a default value only for optional parameters. Use the IsMissing built-in function to test for the absence of optional parameters. You can also use the ParamArray argument to declare an array of optional elements of the Variant data type. When you call the subroutine, you can then pass it an arbitrary number of arguments. The ParamArray argument must be the last argument in the argument list.

Use the Exit Sub statement anywhere in your subroutine to clear any error conditions and exit your subroutine normally, returning to the calling procedure. If Visual Basic runs your code until it encounters the End Sub statement, control is passed to the calling procedure but any errors are not cleared. If this subroutine causes an error and terminates with the End Sub statement, Visual Basic passes the error to the calling procedure. See “Trapping Errors” on page 1028 for details.

Example

To create a subroutine named MySub that accepts two string arguments but can modify only the second argument, enter the following:

 Sub MySub (ByVal strArg1 As String, ByRef strArg2 _   As String)     <subroutine statements> End Sub