MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)
The following sections show the syntax of the statements you can use to define constants and variables in your modules and procedures.
Const Statement
Use a Const statement to define a constant.
Syntax
[Public | Private] Const {constantname [As datatype] = <const expression>},...
Notes
Include the Public keyword in the Declarations section of a standard module to define a constant that is available to all procedures in all modules in your database. Include the Private keyword to declare constants that are available only within the module where the declaration is made. Constants are private by default, and a constant defined within a procedure is always private. You cannot define a Public constant in a class module. (All constants in a class module are private.)
The datatype can be Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String, or Variant. You cannot declare a constant as an object. Use a separate As datatype clause for each constant being declared. If you don’t declare a type, Visual Basic assigns the data type that is most appropriate for the expression provided. (You should always explicitly declare the data type of your constants.)
The <const expression> cannot include variables, user-defined functions, or Visual Basic built-in functions (such as Chr). You can include simple literals and other previously defined constants.
Example
To define the constant PI to be available to all procedures in all modules, enter the following in the Declarations section of any standard module.
Public Const PI As Double=3.14159
| Inside Out-Use Variable Naming Conventions | It’s a good idea to prefix all variable names you create with a notation that indicates the data type of the variable, particularly if you create complex procedures. This helps ensure that you aren’t attempting to assign or calculate incompatible data types. (For example, the names will make it obvious that you’re creating a potential error if you try to assign the contents of a long integer variable to an integer variable.) It also helps ensure that you pass variables of the correct data type to procedures. Finally, including a prefix helps ensure that you do not create a variable name that is the same as an Access or Visual Basic reserved word. The following table suggests data type prefixes that you can use for many of the most common data types.
|
Dim Statement
Use a Dim statement in the Declarations section of a module to declare a variable or a variable array that can be used in all procedures in the module. Use a Dim statement within a procedure to declare a variable used only in that procedure.
Syntax
Dim {[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 as many as 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 chapter. You can also declare a user-defined 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)). If you use a Dim statement within a procedure to declare variables, Visual Basic reinitializes the variables each time you run the procedure.
Examples
To declare a variable named intMyInteger as an integer, enter the following:
Dim intMyInteger As Integer
To declare a variable named dbMyDatabase as a database object, enter the following:
Dim dbMyDatabase As Database
To declare an array named strMyString that contains fixed-length strings that are 20 characters long and contains 50 entries from 51 through 100, enter the following:
Dim strMyString(51 To 100) As String * 20
To declare a database variable, a new table variable, and two new field variables for the table; set up the objects; and append the new table to the Tabledefs collection, enter the following:
Public Sub NewTableExample() Dim db As DAO.Database Dim tdf As New DAO.TableDef, fld1 As New DAO.Field, fld2 As New DAO.Field ' Initialize the table name tdf.Name="MyTable" ' Set the name of the first field fldl.Name="MyField1" ' Set its data type fldl.Type=dbLong ' Append the first field to the Fields ' collection of the table tdf.Fields.Append fld1 ' Set up the second field fld2.Name="MyField2" fld2.Type=dbText fld2.Size=20 ' Append the second field to the table tdf.Fields.Append fld2 ' Establish an object on the current database Set db=CurrentDb ' Create a new table by appending tdf to ' the Tabledefs collection of the database db.TableDefs.Append tdf End Sub
See “Collections, Objects, Properties, and Methods” on page 978 for details about working with DAO objects. See “Functions and Subroutines” on page 1005 for details about the Sub statement.
To declare an object variable to respond to events in another class module, enter the following:
Option Explicit Dim WithEvents objOtherClass As MyClass Sub LoadClass () Set objOtherClass=New MyClass End Sub Sub objOtherClass_Signal(ByVal strMsg As string) MsgBox "MyClass Signal event sent this " &, "message:" &, strMsg End Sub
In class module MyClass, code the following:
Option Explicit Public Event Signal(ByVal strMsg As String) Public Sub RaiseSignal(ByVal strText As String) RaiseEvent Signal(strText) End Sub
In any other module, execute the following statement:
MyClass.RaiseSignal "Hello"
Enum Statement
Use an Enum statement in a module Declarations section to assign long integer values to named members of an enumeration. You can use an enumeration name as a restricted Long data type.
Syntax
[Public \| Private] Enum enumerationname <member> [= <long integer expression>] … End Enum
Notes
Enumerations are constant values that you cannot change when your code is running. Include the Public keyword to define an enumeration that is available to all procedures in all modules in your database. Include the Private keyword to declare an enumeration that is available only within the module where the declaration is made. Enumerations are public by default.
You must declare at least one member within an enumeration. If you do not provide a <long integer expression> assignment, Visual Basic adds 1. to the previous value or assigns 0. if the member is the first member of the enumeration. The <long integer expression> cannot include variables, user-defined functions, or Visual Basic built-in functions (such as CLng). You can include simple literals and other previously defined constants or enumerations.
Enumerations are most useful as a replacement for the Long data type in a Function or Sub statement. When you call the function or sub procedure in code, you can use one of the enumeration names in place of a variable, constant, or literal. If you select the Auto List Members option (see Figure 19–3), Visual Basic displays the available names in a drop-down list as you type the sub or function call in your code.
Example
To declare a public enumeration for days of the week and use the enumeration in a procedure, enter the following:
Option Explicit Public Enum DaysOfWeek Sunday=1. Monday Tuesday Wednesday Thursday Friday Saturday End Enum Public Function NextDate(lngDay As DaysOfWeek) As Date ' This function returns the next date ' that matches the day of week requested Dim intThisDay As Integer, datDate As Date ' Get today datDate=Date ' Figure out today's day of week intThisDay=WeekDay(datDate) ' Calculate next day depending on ' whether date requested is higher or lower If intThisDay < lngDay Then NextDate=datDate+(lngDay-intThisDay) Else NextDate=datDate+(lngDay+7)-intThisDay End If End Function
You can test the function from the Immediate window by entering the following:
?NextDate(Monday)
Event Statement
Use the Event statement in the Declarations section of a class module to declare an event that can be raised within the module. In another module, you can define an object variable using the WithEvents keyword, set the variable to an instance of this class module, and then code procedures that respond to the events declared and triggered within this class module.
Syntax
[Public] Event eventname ([<arguments>])
where <arguments> is
{[ByVal | ByRef] argumentname [As datatype]},...
Notes
An Event must be public, which makes the event available to all other procedures in all modules. You can optionally include the Public keyword when coding this statement.
You should declare the data type of any arguments in the event’s argument list. Note that the names of the variables passed by the triggering procedure can be different from the names of the variables known by this event. If you use the ByVal keyword to declare an argument, Visual Basic passes a copy of the argument to your event. Any change you make to a ByVal argument does not change the original variable in the triggering procedure. If you use the ByRef keyword, Visual Basic passes the actual memory address of the variable, allowing the event to change the variable’s value in the triggering procedure. (If the argument passed by the triggering 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).
Example
To declare an event that can be triggered from other modules, enter the following in the class module MyClass:
Option Explicit Public Event Signal(ByVal strMsg As String) Public Sub RaiseSignal(ByVal strText As String) RaiseEvent Signal(strText) End Sub
To respond to the event from another module, enter the following:
Option Explicit Dim WithEvents objOtherClass As MyClass Sub LoadClass () Set objOtherClass=New MyClass End Sub Sub objOtherClass_Signal(ByVal strMsg As string) MsgBox "MyClass Signal event sent this " &, "message: " &, strMsg End Sub
To trigger the event in any other module, execute the following:
MyClass.RaiseSignal "Hello"
Private Statement
Use a Private statement in the Declarations section of a standard module or a class module to declare variables that you can use in any procedure within the module. Procedures in other modules cannot reference these variables.
Syntax
Private {[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 chapter. You can also declare a user-defined 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 within this module, enter the following:
Private lngMyNumber As Long
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 chapter. Note, however, that you cannot declare a Public fixed-length string within a class module. You can also declare a user-defined 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 chapter. You can also declare a user-defined 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 statemetnt.
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
Static Statement
Use a Static statement within a procedure to declare a variable used only in that procedure and that Visual Basic does not reinitialize while the module containing the procedure is open. Visual Basic opens all standard and class modules (objects you can see in the Modules list in the Navigation Pane) when you open the database containing those objects. Visual Basic keeps form or report class modules open only while the form or the report is open.
Syntax
Static {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 in this chapter. 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.
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)).
Examples
To declare a static variable named intMyInteger as an integer, enter the following:
Static intMyInteger As Integer
To declare a static array named strMyString that contains fixed-length strings that are 20 characters long and contains 50 entries from 51 through 100, enter the following:
Static strMyString(51 To 100) As String * 20
Type Statement
Use a Type statement in a Declarations section to create a user-defined data structure containing one or more variables.
Syntax
[Public | Private] Type typename {variablename [({<array dimension>},...)] As datatype} ... End Type
where <array dimension> is
[lowerbound To] upperbound
Notes
A Type statement is most useful for declaring sets of variables that can be passed to procedures (including Windows API functions) as a single variable. You can also use the Type statement to declare a record structure. After you declare a user-defined data structure, you can use typename in any subsequent Dim, Public, Private, or Static statement to create a variable of that type. You can reference variables in a user-defined data structure variable by entering the variable name, a period, and the name of the variable within the structure. (See the second part of the example that follows.)
Include the Public keyword to declare a user-defined type that is available to all procedures in all modules in your database. Include the Private keyword to declare a userdefined type that is available only within the module in which the declaration is made. You must enter each variable name entry on a new line. You must indicate the end of your user-defined data structure using an End Type statement.
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. You can also declare a user-defined 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.
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 in any variable that you declare as this Type. You can define an array with as many as 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.
Note that a Type declaration does not reserve any memory. Visual Basic allocates the memory required by the Type statement when you use typename as a data type in a Dim, Public, Private, or Static statement.
Example
To define a user type structure named MyRecord containing a long integer and three string fields, declare a variable named usrContacts using that user type, and then set the first string to “Jones”, first enter the following:
Type MyRecord lngID As Long strLast As String strFirst As String strMid As String End Type
Within a procedure, enter the following:
Dim usrContacts As MyRecord usrContacts.strLast = "Jones"
Категории