Declaring Constants and Variables


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.

Open table as spreadsheet

Data Type

Prefix

Boolean

bol

Byte

byt

Currency

cur

Double

dbl

Integer

int

Long

lng

Single

sgl

String

str

User-defi ned (using the Type statement)

usr

Variant

var

Catalog

cat

Column

col

Command

cmd

Connection

cn

Control

ctl

Database

db

Document

doc

Field

fld

Form

frm

Index

idx

Key

key

Parameter

prm

Procedure

prc

Property

prp

QueryDef

qdf

Recordset

rst

Report

rpt

Table

tbl

TableDef

tbl

View

vew

Workspace

wks

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"




Microsoft Office Access 2007 Inside Out
MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)
ISBN: 0735623252
EAN: 2147483647
Year: 2007
Pages: 234

Similar book on Amazon
Access 2007: The Missing Manual
Access 2007: The Missing Manual
Microsoftu00ae Office Access(TM) 2007 Step by Step (Step By Step (Microsoft))
Microsoftu00ae Office Access(TM) 2007 Step by Step (Step By Step (Microsoft))
Microsoft Office Access 2007 Forms, Reports, and Queries
Microsoft Office Access 2007 Forms, Reports, and Queries
Alison Balter's Mastering Microsoft Office Access 2007 Development
Alison Balter's Mastering Microsoft Office Access 2007 Development

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net