MS-Access / Getting Started

---

Public Statement

Use a Public statement in the Declarations section of a standard module or a class module to declare variables that you can use in any procedure anywhere in your database.

Syntax

Public {[WithEvents] variablename
  [([<array dimension>],... )]
  [As [New] datatype]},...

where <array dimension> is

[lowerbound To ] upperbound

Notes

If you do not include an <array dimension> specification but you do include the parentheses, you must include a ReDim statement in each procedure that uses the array to dynamically allocate the array at run time. You can define an array with up to 60 dimensions. If you do not include a lowerbound value in an <array dimension> specification, the default lower bound is 0. You can reset the default lower bound to 1 by including an Option Base 1 statement in the module Declarations section. The lowerbound and upperbound values must be integers, and upperbound must be greater than or equal to lowerbound. The number of members of an array is limited only by the amount of memory on your computer.

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, however, that you cannot declare a Public fixed-length string within a class module. You can also declare a userdefined variable structure using the Type statement and then use the user type name as a data type. You should always explicitly declare the data type of your variables. If you do not include the As datatype clause, Visual Basic assigns the Variant data type.

Use the New keyword to indicate that a declared object variable is a new instance of an object that doesn't have to be set before you use it. You can use the New keyword only with object variables to create a new instance of that class of object without requiring a Set statement. You can't use New to declare dependent objects. If you do not use the New keyword, you cannot reference the object or any of its properties or methods until you set the variable to an object using a Set statement.

Use the WithEvents keyword to indicate an object variable within a class module that responds to events triggered by an ActiveX object. Form and report modules that respond to events on the related form and report objects are class modules. You can also define custom class modules to create custom objects. If you use the WithEvents keyword, you cannot use the New keyword.

Visual Basic initializes declared variables at compile time. Numeric variables are initialized to zero (0), variant variables are initialized to empty, variable-length string variables are initialized as zero-length strings, and fixed-length string variables are filled with ANSI zeros (Chr(0)).

Example

To declare a long variable named lngMyNumber that can be used in any procedure in the database, enter the following:

Public lngMyNumber As Long

ReDim Statement

Use a ReDim statement to dynamically declare an array within a procedure or to redimension a declared array within a procedure at run time.

Syntax

ReDim [Preserve] {variablename
  (<array dimension>,...) [As datatype]},...

where <array dimension> is

[lowerbound To ] upperbound

Notes

If you're dynamically allocating an array that you previously defined with no <array dimension> specification in a Dim, Public, or Private statement, your array can have up to 60 dimensions. You cannot dynamically reallocate an array that you previously defined with an <array dimension> specification in a Dim, Public, or Private statement. If you declare the array only within a procedure, your array can have up to 60 dimensions. If you do not include a lowerbound value in an <array dimension> specification, the default lower bound is 0. You can reset the default lower bound to 1 by including an Option Base 1 statement in the module Declarations section. The lowerbound and upperbound values must be integers, and upperbound must be greater than or equal to lowerbound. The number of members of an array is limited only by the amount of memory on your computer. If you previously specified dimensions in a Public, Private, or Dim statement or in another ReDim statement within the same procedure, you cannot change the number of dimensions.

Include the Preserve keyword to ask Visual Basic not to reinitialize existing values in the array. When you use Preserve, you can change the bounds of only the last dimension in the array.

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. You can also declare a userdefined variable structure using the Type statement and then use the user type name as a data type. You should always explicitly declare the data type of your variables. If you do not include the As datatype clause, Visual Basic assigns the Variant data type. You cannot change the data type of an array that you previously declared with a Dim, Public, or Private statement. After you establish the number of dimensions for an array that has module or global scope, you cannot change the number of its dimensions using a ReDim statement.

Visual Basic initializes declared variables at compile time. Numeric variables are initialized to zero (0), variant variables are initialized to empty, variable-length string variables are initialized as zero-length strings, and fixed-length string variables are filled with ANSI zeros (Chr(0)). When you use the Preserve keyword, Visual Basic initializes only additional variables in the array. If you use a ReDim statement within a procedure to both declare and allocate an array (and you have not previously defined the array with a Dim, Public, or Private statement), Visual Basic reinitializes the array each time you run the procedure.

Example

To dynamically allocate an array named strProductNames that contains 20 strings, each with a fixed length of 25, enter the following:

ReDim strProductNames(20) As String * 25