MS-Access / Getting Started

---

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, 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 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 tutorial. 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 (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 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.

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