MS-Access / Getting Started

---

Functions and Subroutines

You can create two types of procedures in Visual Basic-functions and subroutines-which are also known as Function procedures and Sub procedures. 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, so long as the module containing the procedure is open. This is the same as using the Static statement (discussed earlier in this tutorial) 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 tutorial. 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 (using 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.

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