Getting Acquainted with VBA 6.0

VBA is a real programming language, not a macro language. You create the preferred equivalent of macros with VBA functions and subprocedures. Although you can execute VBA subprocedures directly from an open code module, you more typically execute VBA subprocedures from user-initiated events, such as clicking a command button or changing the current record of a bound form. (Chapter 28, "Handling Events with VBA 6.0," explains how to use VBA subprocedures as event-handlers.) You execute VBA functions by calling them from calculated controls in forms and reports, from the Validation Rule property value of a field or table, or from within a VBA subprocedure.

Where You Use VBA Code

Short VBA procedures using the DoCmd object usually are sufficient to provide the methods needed by simple applications to run queries, display forms, and print reports. The Access-specific DoCmd object lets you run any macro action from VBA as a method of the DoCmd object. For example, executing DoCmd.OpenForm("FormName") opens the FormName form.

The built-in functions of Access allow you to perform complex calculations in Jet queries, but not in Transact-SQL (T-SQL) queries. You might want or need to use VBA code for any of the following reasons:

• To create user-defined functions (UDFs) that substitute for complex expressions you use repeatedly to validate input data, compute values for text boxes, and perform other duties. Creating a UDF that you refer to by a short name minimizes potential typing errors and lets you document the way your expressions work.

• To write query expressions that include more complex decision structures than allowed by Jet's inline IIf function (in an If...Then...Else...End If structure, for example), or to write expressions that need loops for repetitive operations.

Note

You can't incorporate VBA in T-SQL queries, so queries that include VBA user-defined functions won't upsize to SQL Server 2000 databases.

• To write and execute SELECT queries with WHERE clauses or other SQL elements whose values come from controls on forms, such as list or combo boxes.

• To execute transaction processing SQL statements with BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION key words against Jet or SQL Server databases.

• To manipulate ActiveX controls and other applications' objects with Automation code.

• To open more than one database in a Jet application where attaching a table or using the Jet SQL IN statement isn't sufficient for your application.

• To provide hard-copy documentation for your application. If you execute actions from VBA code, you can print the code to improve the documentation for your application.

• To provide graceful error handling if something goes wrong in your application. With VBA code, you can closely control how your application responds to errors such as missing data, incorrect values entered by a user, and other problems. One of the shortcomings of Access macros is their inability to respond appropriately to execution errors.

Typographic and Naming Conventions Used for VBA

This book uses a special set of typographic conventions for references to VBA keywords and object variable names in VBA examples:

• Monospace type is used for all VBA code in the examples, as in lngItemCounter.

• Bold monospace type is used for all VBA reserved words and type-declaration symbols, as in Dim and %. (Type-declaration symbols aren't used in this book; instead, your VBA code defines the data type of each variable prior to use.) Standard function names in VBA, such as those as described in Chapter 10, "Understanding Jet Operators and Expressions," also are set in bold type so that reserved words, standard function names, and reserved symbols stand out from variable names, function names, and values you assign to variables. Keywords incorporated by reference in Access, such as DoCmd (an Access-specific object) or Recordset (a data-specific object), are not set in bold type.

To review some of the VBA functions and their descriptions, see "Functions for Date and Time," p. 370 and "Text-Manipulation Functions," p. 373.

• Italic monospace type indicates a replaceable item, also called a placeholder, as in Dim DataItem As String. DataItem is replaced by a name that you supply.

• Bold-italic monospace type indicates a replaceable reserved word, such as a data type, as in Dim DataItem As DataType; DataType is replaced by a VBA reserved word corresponding to the desired VBA data type, such as String or Object.

• Names of variables that refer to Jet and Access objects, such as queries, forms or reports, use a three-letter prefix derived from the object name, as in qryFormName, frmFormName and rptReportName. SQL Server objects use two-letter prefixes for views, functions, and stored procedures vwViewName, fnFunctionName, spProcedureName. With a few exceptions, this book doesn't use prefixes for table or field names.

  Tip

  

  Most of the three-letter prefixes used in this book correspond to those recommended by Microsoft or the "Leszynski Naming Conventions for Access," a white paper published by Stan Leszynski of Kwery Corporation. You can order copies of the standards in the form of help files or white papers at http://www.kwery.com/.

  
  

• Names of all other variables are preceded by a three-letter data type identifier, such as varVariantVariable (Variant) and intIntegerVariable (Integer). Variables representing instances of objects use an arbitrary three-letter prefix, such as chtObject for a PivotChart.

• Optional elements appear in the text within square brackets, as in [OptionItem]. If you add the optional element, you don't type the brackets. Square brackets also enclose object names that contain spaces or special punctuation symbols for compatibility with Jet SQL and T-SQL. In this case, your code must contain the square brackets.

• Elements requiring you to choose from a set of alternatives are enclosed with French braces and separated by pipe symbols, as in Do {While|Until} Expression...Loop.

• An ellipsis (...) substitutes for code that isn't shown in syntax and code examples, as in If...Then...Else...End If.

Modules, Functions, and Subprocedures

A module is a container for VBA code, just as a form is a container for control objects. Access 2003 provides the following four types of modules:

• Access modules You create an Access module to contain your VBA code the same way that you create any other new database object: Click the Module button in the Database window and then click the New button. Alternatively, you can click the New Object button on the toolbar and choose Module from the drop-down menu. Figure 27.1 shows the IsLoaded() function of the Utility Functions module of Northwind.mdb. Access modules are also called standard modules.

  Figure 27.1. The VBA editor displays the IsLoaded() function of the Utility Functions module.

  

• Form modules Form modules contain code to respond to events triggered by forms or controls on forms. Essentially, when you add VBA code to a form object, you create a new class of object in the database. The event-handling procedures you create for the form are its new class's methods, hence the term class module for the code module associated with a particular form. You open a form module by clicking the Code button of the toolbar in Form Design view. Alternatively, choose View, Code. Either of these methods opens a module that Access automatically names Form_FormName, where FormName is the name of the selected form. Forms in Access 2002 have a HasModule property. If this read-only property is set to Yes, then the form has a class module; otherwise, it doesn't.

  Tip

  

  Another method of opening a form module is to click the Builder button for one of the event properties for a form or a control object on a form. Selecting Code Builder from the Choose Builder dialog displays the Form_FormName module with a procedure stub, Private Sub ObjectName_EventName()...End Sub, written for you. Access 2003 adds the VBA Private prefix by default. Figure 27.2 shows the VBA code for the CustomerID_AfterUpdate and part of the CustomerID_BeforeUpdate event-handling subprocedures of Northwind.mdb's Orders form.

  
  

  Figure 27.2. The CustomerID_AfterUpdate subprocedure is a typical event-handler for a combo list. The code executes when you select a CustomerID value in the Order form's Bill To combo box.

  

• Report modules Report modules contain code for responding to events triggered by reports, sections of reports, or group headers and footers. (Control objects on reports don't trigger events.) You open a report's class module the same way you open a form's class module. Report class modules are named Report_ReportName automatically. Like forms, if an Access 2003 report has a class module, the Has Module property value is set to Yes (HasModule = True).

• Class modules A class module not associated with a form or report lets you define your own custom objects, together with their properties and methods. Writing unassociated class modules is beyond the scope of this book.

Elements of Modules

A module consists of a Declarations section and usually one or more procedures (interchangeably called subprocedures) or functions. As the name suggests, the Declarations section of a module is used to declare items (usually variables and constants, the subjects of following sections) used by the procedures and functions contained in the module. You can use a module without functions or procedures to declare Public variables and constants that can be used by any function or procedure in any module of the database or project.

Procedures

Procedures are typically defined as subprograms referred to by name in another program. Referring to a procedure by name calls or invokes the procedure; the code in the procedure executes, and then the sequence of execution returns to the program that called the procedure. Another name for a procedure is subroutine. Procedures are defined by beginning (Sub) and end (End Sub) reserved words with an optional Public, Private, or Static prefix, as in the following example:

 Private Sub ProcName    [Start of procedure code]    ...    [End of procedure code] End Sub 

Tip

You can refer to the procedure name to invoke the procedure, but VBA provides a keyword, Call, that explicitly invokes a procedure. Prefixing the procedure name with Call is good programming practice because this keyword identifies the name that follows as the name of a procedure rather than a variable.

VBA introduces another class of procedure called property procedures that use the {Property Let|Property Get|Property Set}...End Property structure to create custom properties for Access objects, such as forms or controls. A discussion of property procedures is beyond the scope of this book.

Procedures

Functions are a class of procedures that return values to their names, as explained in Chapter 10. C programmers would argue that procedures are a class of functions, called void functions, which don't return values. Regardless of how you view the difference between functions and subprocedures, keep the following points in mind:

• Access macros require that you write VBA functions (not subprocedures) to act in place of macro actions when using the RunCode macro action. Macros ignore the value returned by the function, if a return value is specified.

• Form and report class modules use subprocedures (not functions) to respond to events. Using form-level and report-level subprocedures for Access event-handling code mimics Visual Basic's approach for events triggered by forms, controls on forms, and other objects.

• A custom subprocedure is a subprocedure that isn't assigned to an event. The only way you can call a custom subprocedure in a VBA module is from a VBA function or another subprocedure. You can't directly execute a subprocedure in an Access module from any Access database object.

• Function names in Access modules are global in scope with respect to Access modules unless they are declared Private. Thus, you cannot have duplicate Public function names in any Access module in your application. However, form and report class modules can have a function with the same name as a Public function in a standard module because form and report function and procedure names have form-level or report-level scope. A function in a form module with the same name as a function in an Access module takes priority over the Access module version. Therefore, if you include the IsLoaded() function in a form module and call the IsLoaded() function from a procedure in the form module, the IsLoaded() function in the form module executes.

Functions are created within a structure similar to procedures, as in the following example:

 Private Function FunctionName([Argument As DataType]) _   As ReturnType   [Start of function code]   ...   [End of function code]   FunctionName = 123 End Function 

In the preceding example, the FunctionName = 123 statement returns the value 123 to intReturnValue. In this case, the ReturnType data type must be a numeric data type, such as Integer. Most functions return True or False (Boolean), a numeric value (Integer, Long, Single, Double, or Decimal), or a set of characters (String).

To execute a VBA function in VBA code, you ordinarily use the function in an expression, such as

 intReturnValue = FunctionName([Argument]) 

when the function returns a value. You can ignore the return value by calling the function with subprocedure syntax.

References to VBA and Access Modules

Access 2003 uses references to make Component Object Model (COM) objects available for programming by module code. A reference points to a COM dynamic link library (.dll), object library (.olb), or type library (.tlb) file installed on your computer with an entry in the Registry. To view the default references, open a new module in a new database, and then choose Tools, References to open the References dialog (see Figure 27.3). Current references are indicated by a mark in the adjacent check box. You add references by scrolling the list, and marking the check box of the new reference. When you click OK to close the References dialog and then reopen it, the new reference appears below the last of the original references.

Figure 27.3. A new database created in Access 2000 format has the four standard references shown here. The Microsoft Access 11.0 Object Library enables programming of Access-specific objects, such as DoCmd.

Tip

You can't add a reference to more than one version of the same COM object. Attempting to do this results in a "Name conflicts with existing module, project, or object library" message. For example, if the Microsoft ActiveX Data Objects 2.1 Library is selected, you must clear its check box before adding a reference to the Microsoft ActiveX Data Objects 2.7 Library.

Microsoft Data Access Components (MDAC 2.7), which includes the ActiveX Data Objects 2.7 Library (ADO 2.7) is the current version installed by Office 2003. Running MSDE setup from the Office 2003 distribution CD-ROM installs MDAC 2.7 with ADO 2.7, which includes components specifically designed to support SQL Server 2000. The default reference for new Jet databases or Access data projects that you create in Access 2000 format, however, is ADO 2.1 the same version used by Access 2000. Creating a new Access database or data access project in Access 2002 format adds a reference to ADO 2.5. Converting a project from Access 2000 to 2002 format doesn't update the ADO reference from version 2.1 to 2.5. ADO 2.x is the subject of Chapter 30, "Understanding Universal Data Access, OLE DB, and ADO." If you open a database created with Access 2000 or earlier, the Microsoft DAO 3.6 Library (DAO 3.6) is the default data object reference.

Tip

Always use the latest version of ADO for applications that don't require full backward compatibility with earlier versions of Access. If you intend to distribute runtime applications, the Windows Installer package includes the version of the MDAC type library which includes ADO that you specify as a reference to update users' computers. Be sure to specify ADO 2.7 as a reference if you're distributing Access Data Projects (ADP). All versions of ADO are backward compatible. (DAO 3.6 is backward compatible with DAO 3.5 and 3.0, but not DAO 2.5 and earlier.)

The Object Browser

Referenced objects appear in the Project/Library drop-down list of the Object Browser. To view the Object Browser, open a module and press F2, click the Object Browser button on the toolbar, or choose View, Object Browser. <All Libraries> is the default selection in the Project/Library list. Figure 27.4 shows a few of the references to Form, Report, and Module objects of Northwind.mdb in the Classes list. Only objects that can act as VBA containers appear in the Classes list; tables, queries, and macros don't qualify.

Figure 27.4. The Object Browser window displays classes of the object you choose in the top list (the Northwind database) in the Classes list and members of the class in the Members list. The bottom pane shows the calling syntax for the function.

When you select a function or subprocedure name in a module, the function or subprocedure name and arguments, if any, appear in the window at the bottom of the Object Browser dialog. You can get help on Access, VBA, and other objects by clicking the help (?) button, which ignores user-defined functions and the event-handling subprocedures you write. The next chapter describes object classes and the use of the Object Browser in detail.

Data Types and Database Objects in VBA

When you create Jet tables, all data types that you use to assign field data types and sizes (except for OLE Object and Memo field data types) have data type counterparts in VBA. With the exception of the Variant and Currency data types, VBA data types are represented in most other dialects of BASIC, such as Microsoft's early QuickBASIC and the QBasic interpreter supplied with MS-DOS 5 and later.

Traditional BASIC dialects use a punctuation symbol called the type-declaration character, such as $ for the String data type, to designate the data type. The VBA data types, the type-declaration characters, the corresponding field data types, and the ranges of values are shown in the VBA Type, Symbol, Field Type, Minimum Value, and Maximum Value columns, respectively, of Table 27.1. The Field Types Byte, Integer, Long Integer, Single, and Double correspond to the Field Size property of the Number data type in tables, queries, forms, and reports. VBA adds the Byte and Boolean data types to support the 8-bit Byte and 1-bit Yes/No field data types.

Table 27.1. VBA and Corresponding Field Data Types

VBA	Symbol	Field Type	Minimum Value	Maximum Value
Byte	None	Byte	0	255
Integer	%	Integer	32,768	32,767
Boolean	None	Yes/No	True	False
Long	&	Long Integer, AutoNumber	2,147,483,648	2,147,483,647
Single	!	Single	3.402823E38	1.401298E 45
			1.401298E 45	3.402823E38
Double	#	Double	1.7200069313486232E308	4.94065645841247E 324
			4.94065645841247E 324	1.7200069313486232E308
Currency	@	Currency	922,337,203,685, 477.5808	922,337,203,685, 477.5807
String	$	Text or Memo	0 characters	Approximately 2 billion characters
Date	None	Date/Time	January 1, 100	December 31, 9999
Variant	None	All	Any of the preceding	Any of the preceding

Tip

All data returned from fields of tables or queries is of the Variant data type by default. Variables of the Variant data type can hold any type of data listed in Table 27.1. If you assign the field value to a conventional data type, such as Integer, the data type is said to be coerced.

You can dispense with the type-declaration character if you explicitly declare your variables with the {Dim|Private|Public} typVarName As DataType statement, discussed later in this section. If you don't explicitly declare the variables' data type or use a symbol to define an implicit data type, VBA variables default to the Variant data type.

Tip

Using the Variant data type causes VBA code to execute more slowly than when you assign variables an explicit data type with the {Dim|Private|Public} typVarName As DataType statement.

The # sign is also used to enclose values specified as dates, as in varNewYear = #1/1/2001#. In this case, bold type isn't used for the enclosing # signs because these symbols aren't intended for the purpose of the # reserved symbol that indicates the Double data type.

Jet database objects such as databases, tables, and queries and application objects (forms and reports), all of which you used in prior chapters, also have corresponding object data types in VBA. These object data types are defined by the object (also called type or class) library references. The most commonly used object data types and the object library that includes the objects are listed in Table 27.2. The Database, QueryDef, and TableDef object types are specific to Jet and DAO, and aren't available in ADP.

Table 27.2. The Most Common Jet Database Object Data Types Supported by VBA

Object Data Type	Library	Corresponding Database Object Type
Database	DAO 3.6	Databases opened by the Jet database engine when using DAO
Connection	ADO 2.x	ADO replacement for DAO.Database object
Form	Access 11.0	Forms, including subforms
Report	Access 11.0	Reports, including subreports
DataAccessPage	Access 11.0	Definition of a link to a Data Access Page
Control	Access 11.0	Controls on forms, subforms, reports, and subreports
QueryDef	DAO 3.6	Jet query definitions (SQL statement equivalents) when using DAO
Command	ADO 2.x	ADO replacement for DAO.QueryDef object
TableDef	DAO 3.6	Jet table definitions (structure, indexes, and other table properties)
DAO.Recordset	DAO 3.6	A virtual representation of a Jet table or the result set of a Jet query created by DAO
ADODB.Recordset	ADO 2.x	ADO replacement for the DAO.Recordset object

Note

OLE DB is Microsoft's current COM-based database connectivity architecture. OLE DB is the foundation of Microsoft's Universal Data Access initiative, which is described in Chapter 30. ADO is an "Automation wrapper" over OLE DB, which makes OLE DB objects accessible to Access and all other applications that support Automation through VBA. Recordset is an object that's common to both DAO and ADO, so it's good programming practice to prefix Recordset with its source class identifier, as in DAO.Recordset or ADODB.Recordset. You must specify the prefix if you include references to DAO 3.6 and ADO 2.x in your application. You can use ADO with both Jet and SQL Server databases; DAO is restricted to Jet databases only.

The .NET framework's ADO.NET substitutes for OLE DB and ADO and currently offers an ADO.NET managed data provider for SQL Server databases. .NET adds a COM interop(erability) wrapper to ADO and OLE DB for handling connections to Jet and other databases that don't have managed data providers. COM interop ensures that your Jet databases won't become obsolete when Visual Studio .NET becomes the primary Windows programming environment.

Variables and Naming Conventions

Variables are named placeholders for values of a specified data type that change when your VBA code is executed. You give variables names as you name fields, but the names of variables cannot include spaces or any other punctuation except the underscore character (_). The other restriction is that a variable cannot use a VBA keyword by itself as a name; keywords are called reserved words for this reason. The same rules apply to giving names to functions and procedures. Variable names in VBA typically use a combination of uppercase and lowercase letters to make them more readable.

Implicit Variables

You can create variables by assigning a value to a variable name, as in the following example:

 NewVar = 1234 

A statement of this type declares a variable, which means to create a new variable with a name you choose. The statement in the example creates a new implicit variable, NewVar, of the Variant data type with a value of 1234. (NewVar would be more appropriately named varNewVar.) When you don't specify a data type for an implicit variable by appending one of the type-declaration characters to the variable name, the Variant data type is assigned by default. The following statement uses the % type identifier to create a variable of the Integer data type:

 NewVar% = 1234 

Explicit Variables

It's a better programming practice to declare your variables and assign those variables a data type before you give variables a value. The most common method of declaring variables is by using the Dim typVarName As Datatype structure, in which As specifies the data type. This method declares explicit variables. An example follows:

 Dim intNewVar As Integer 

If you don't add the As Integer keywords, intNewVar is assigned the Variant data type by default.

You can require that all variables be explicitly declared before their use by adding the statement Option Explicit in the Declarations section of a module. The advantage of using Option Explicit is that the VBA compiler detects misspelled variable names and displays an error message when misspellings are encountered. If you don't use Option Explicit and you misspell a variable name, the VBA interpreter creates a new implicit variable with the misspelled name. The resulting errors in your code's execution can be difficult to diagnose. The VBA editor automatically adds an Option Explicit statement to the Declarations section of each module if you select the Require Variable Declaration option in the VBA editor's Options dialog, which you open by choosing Tools, Options (see Figure 27.5).

Figure 27.5. Mark the Require Variable Declaration check box on the Editor page of the VBA editor's Options dialog. Despite many requests from Access developers, this feature isn't enabled by default.

Scope and Duration of Variables

Variables have a characteristic called scope, which determines when the variables appear and disappear in your VBA code. Variables appear the first time you declare them and then disappear and reappear on the basis of the scope you assign to them. When a variable appears, it is said to be visible meaning that you can assign the variable a value, change its value, and use it in expressions. Otherwise, the variable is invisible. If you use a variable's name while it's invisible, you instead create a new variable with the same name, if you haven't specified the Required Variable Declaration feature.

The following lists the four scope levels in VBA:

• Local (procedure-level) scope The variable is visible only during the time when the procedure in which the variable is declared is running. Variables that you declare, with or without using Dim typVarName As DataType in a procedure or function, are local in scope.

• Form-level and report-level scope The variable is visible only when the form or report in which it's declared is open. You declare form-level and report-level variables in the Declarations section of form and report modules with Private typVarName As DataType. (Dim typVarName As DataType also works, but Private is the preferred scope identifier.)

• Module-level scope The variable is visible to all procedures and functions contained in the module in which the variable was declared. (Modules open when you open the database.) You declare variables with module scope in the Declarations section of the module with the same syntax as form-level and report-level variables.

• Global or public scope The variable is visible to all procedures and functions within all modules. You declare variables with global scope in the Declarations section of a module using Public typVarName As DataType. The Public scope identifier is available only in Access modules.

The scope and visibility of variables declared in two different Access modules of the same database, both with two procedures, are illustrated by the diagram in Figure 27.6. In each procedure, variables declared with different scopes are used to assign values to variables declared within the procedure. Invalid assignment statements are shown crossed out in the figure. These assignment statements are invalid because the variable used to assign the value to the variable declared in the procedure isn't visible in the procedure.

Figure 27.6. This diagram illustrates valid and invalid assignment for variables of different scopes.

Variables also have an attribute called duration, or lifetime. The duration of a variable is your code's execution time between the first appearance of the variable (its declaration) and its disappearance. Each time a procedure or function is called, local variables declared with the Dim typVarName As DataType statement are set to default values 0 for numeric data types and the empty string ("") for string variables. The duration of these local variables is usually equal to the lifetime of the function or procedure from the time the function or procedure is called until the End Function or End Sub statement is encountered.

To preserve the values of local variables between occurrences (called instances) of a procedure or function, you substitute the reserved word Static for Dim. Static variables have a duration of your Access application, but their scope is determined by where you declare them. Static variables are useful when you want to count the number of occurrences of an event. You can make all variables in a function or procedure static variables by preceding Function or Sub with the Static keyword.

Tip

Minimize the number of local variables that you declare Static. Local variables don't consume memory when they aren't visible. This characteristic of local variables is especially important in the case of arrays, discussed in the "VBA Arrays" section that follows shortly, because arrays are often very large.

User-Defined Data Types

You can create your own data type that consists of one or more Access data types. User-defined data types are discussed in this section pertaining to variables because you need to know what a variable is before you can declare a user-defined data type. You declare a user-defined data type between the Type...End Type keywords, as in the following example:

 Type tagDupRec   lngField1 As Long   strField2 As String * 20   sngField3 As Single   dblField4 As Double End Type 

User-defined data types are particularly useful when you create a variable to hold the values of one or more records of a table that uses fields of different data types. The String * 20 statement defines lngField2 of the user-defined data type as a fixed-length string of 20 characters, usually corresponding to the Size property of the Text field data type. String variables in user-defined data types traditionally have a fixed length, but VBA 6.0 lets you use variable-length strings. You must declare your user-defined data type (called a record or structure in other programming languages) in the Declarations section of a module.

You must explicitly declare variables to be of the user-defined type with the Dim, Private, Public, or Static keywords because no reserved symbol exists to declare a user-defined data type, as in Dim usrCurrentRec As tagDupRec. To assign a value to a field of a variable with a user-defined data type, you specify the name of the variable and the field name, separating the names with a period, as in usrCurrentRec.lngField1 = 2048.

VBA Arrays

Arrays are variables that consist of a collection of values, called elements of the array, of a single data type in a regularly ordered structure. Implicitly declared arrays aren't allowed in VBA. You declare an array with the Dim statement, adding the number of elements in parentheses to the variable name for the array, as in the following example:

 Dim astrNewArray(20) As String 

This statement creates an array of 21 elements, each of which is a conventional, variable-length string variable. You create 21 elements because the first element of an array is the 0 (zero) element, unless you specify otherwise by adding the To modifier, as in the following example:

 Dim astrNewArray(1 To 20) As String 

The preceding statement creates an array with 20 elements.

You can create multidimensional arrays by adding more values separated by commas. The statement

 Dim alngNewArray(9, 9, 9) As Long 

creates a three-dimensional array of 10 elements per dimension. This array, when visible, occupies 4,000 bytes of memory (10x10x10x4 bytes/long integer).

You can create a dynamic array by declaring the array using Dim without specifying the number of elements and then using the ReDim reserved word to determine the number of elements the array contains. You can ReDim an array as many times as you want. Each time you do so, the values stored in the array are reinitialized to their default values, determined by the data type, unless you follow ReDim with the reserved word, Preserve. The following sample statements create a dynamic array:

 Dim alngNewArray() As Long           'In Declarations sections ReDim Preserve alngNewArray(9, 9, 9)   'In procedure, preserves prior values ReDim alngNewArray(9, 9, 9)         'In procedure, reinitializes all 

Dynamic arrays are useful when you don't know how many elements an array requires when you declare it. You can ReDim a dynamic array to zero elements when you no longer need the values it contains; this tactic lets you recover the memory that the array consumes while it's visible. Alternatively, you can use the Erase reserved word followed by a dynamic array's name to remove all the array's elements from memory. (Erase used on an array with fixed dimensions merely reinitializes the array to its condition before you assigned any values to it.) Arrays declared with Dim can have up to 60 dimensions. You can only use the ReDim statement to alter the size of the last dimension in a multidimensional array.

Scope, duration rules, and keywords apply to arrays in the same way in which they apply to conventional variables. You can declare dynamic arrays with global and module-level scope by adding the Public or Private statement to the Declarations section of a module and then using the ReDim statement by itself in a procedure. If you declare an array with Static, rather than Dim, the array retains its values between instances of a procedure.

Tip

Don't use the Option Base keywords to change the default initial element of arrays from 0 to 1. Option Base is included in VBA for compatibility with earlier BASIC dialects. Many arrays you create from VBA objects must begin with element 0. If you're concerned about the memory occupied by an unused zero element of an array, use the Dim ArrayName(1 To N) As DataType declaration. In most cases, you can disregard the zero element.

Named Database Objects as Variables in VBA Code

Properties of database objects you create with Access can be treated as variables and assigned values within VBA code. For example, you can assign a new value to the text box that contains the address information for a customer by name with the following statement:

 Forms!Customers!Address = "123 Elm St." 

The collection name Forms defines the type of object. The exclamation point (called the bang symbol by programmers) separates the name of the form and the name of the control object. The ! symbol is analogous to the \ path separator that you use when you're dealing with folder and file names. If the name of the form or the control object contains a space or other punctuation, you must enclose the name within square brackets, as in the following statement:

 Forms!Customers![Contact Name] = "Joe Hill" 

Alternatively, you can use the Set keyword to create your own named variable for the control object. This procedure is convenient when you need to refer to the control object several times. It's more convenient to type txtContact rather than the full "path" to the control object in this case, a text box.

 Dim txtContact As Control Set txtContact = Forms!Customers![Contact Name] txtContact.Value = "Joe Hill" 

Tip

Specifying the Value property when assigning a value to a control isn't required because Value is the default property of controls and fields of Recordset objects. It's good programming practice, however, to do so. Adding the Value property when manipulating ADODB.Recordset objects with VBA results in improved performance. Specifying the Value property is important for code you might upgrade to Visual Basic .NET, because Visual Basic .NET doesn't support default properties of objects.

You can assign any database object to a variable name by declaring the variable as the object type and using the Set statement to assign the object to the variable. You don't create a copy of the object in memory when you assign it a variable name; the variable refers to the object in memory. Referring to an object in memory is often called pointing to an object; many languages have a pointer data type that holds the address of the location in memory where the variable is stored. VBA has no direct support for pointers . The next chapter deals with creating variables that point to the Access 2002 database objects supplied by ADO 2.x.

Object Properties and the With...End With Structure

VBA provides the With...End With structure that offers a shorthand method of setting the values of object properties, such as the dimensions and other characteristics of a form. The With...End With structure also lets you set the values of fields of a user-defined data type without repeating the variable name in each instance. To use the With...End With structure to set object property values, you must first declare and set an object variable, as in the following example:

 Dim frmFormName As Form Set frmFormName = Forms!FormName With frmFormName   .Top = 1000   .Left = 1000   .Width = 5000   .Height = 4000 End With 

When using the With...End With structure with user-defined data types, you don't use the Set statement. Names of properties or fields within the structure are preceded by periods.

Symbolic Constants

Symbolic constants are named placeholders for values of a specified data type that don't change when executing your VBA code. You precede the name of a symbolic constant with the keyword Const, as in Const sngPI As Single = 3.1416. You declare symbolic constants in the Declarations section of a module or within a function or procedure. Precede Const with the Public keyword if you want to create a global constant that's visible to all modules, as in Public Const gsngPI = 3.1416. The g prefix of the variable name is an abbreviation for Global, which most programmers prefer to p for Public. Public constants can be declared only in the Declarations section of a VBA module.

You don't need to specify a data type for constants explicitly because VBA chooses the data type that stores the data most efficiently. VBA can make this choice because it knows the value of the data when it "compiles" your code. It's a better programming practice, however, to specify the data type of constants.

Note

Office 2003's VBA is an interpreted language, so the term compile in a VBA context is a misnomer. When you "compile" the VBA source code that you write in a code-editing window, the VBA editor creates a tokenized, binary version of the code (called pseudocode, or p-code) stored in an.mdb or .adp file. Only Visual Basic 6.0 compiles VBA 6.0 code to create an executable (.exe) file. Visual Basic .NET is similar to Office 2003's VBA; programs written in Visual Basic .NET create p-code that the Common Language Runtime (CLR) executes.

Access System-Defined Constants

VBA includes seven system-defined constants True, False, Yes, No, On, Off, and Null that are created by the VBA and Access type libraries when launched. Of these seven, you can use True, False, and Null, which are declared by the VBA library, in VBA code. The remaining four are declared by the Access type library and are valid for use with all database objects except modules. When the system-defined constants True, False, and Null are used in VBA code examples in this book, they appear in bold monospace type. This book doesn't use the Access-defined Yes, No, On, and Off constants; don't use them in the code you write.

Access Intrinsic Constants

VBA provides a number of predeclared, intrinsic, symbolic constants that are primarily for use as arguments of Access DoCmd.ActionName statements. These statements let you execute standard database actions in VBA (such as opening forms, printing reports, applying sorts or filters, and so on). Access 2003 intrinsic constants carry the prefix ac, as in acExportMerge. You can display the list of Access intrinsic constants in the Object Browser by selecting Access in the Project/Library list and then selecting Globals in the Classes list.

When you select a constant in the Members Of list, its numeric value appears at the bottom of the Object Browser window (see Figure 27.7). A good programming practice is to use constant names rather than their numeric values when applicable to make your code more readable. You can't use any of these intrinsic constants' names as names for constants or variables that you define.

Figure 27.7. Access has hundreds of intrinsic constants defined by the Microsoft Access 11.0 Object Library. Constants with the prefix A_ are included for backward compatibility with the Access Basic language used by Access 2.0 and earlier.

VBA Intrinsic Constants

VBA has many of its own constants, in addition to the three mentioned in the earlier "Access System-Defined Constants" section. These constants carry a vb prefix. To see a list of the VBA constants, select VBA in Object Browsers top combo list, accept the default <globals> class, and scroll to the vb... entries. One of the most commonly used VBA intrinsic constants is vbCrLf, which inserts a (carriage) return and line feed into a string.

VBA Named and Optional Arguments

Procedures often have one or more arguments that pass values from the calling statement to the called procedure. Traditionally, you must pass all the values required by the procedure in your calling statement. As an example, if a procedure accepts four arguments, Arg1...Arg4, your calling statement must provide values for Arg1...Arg4, as in the following example:

 Sub CallingProc()   ...   Call CalledProc(100000, 200000, 300000, 400000)   ... End Sub Sub CalledProc(Arg1 As Long,Arg2 As Long, _           Arg3 As Long,Arg4 As Long)   [Subprocedure code] End Sub 

Note

The space followed by an underscore following Arg2 As Long in the preceding subprocedure is called the code-continuation character (CCC or 3C). VBA interprets instructions on a line-by-line basis. The CCC lets you continue a statement on the next line. Using the CCC lets you format your code for easier readability.

VBA 6.0 lets you declare the arguments of the subprocedure to be Optional, eliminating the need to pass every parameter to the procedure. You use named arguments to pass values to specific arguments, as in the following example:

 Sub CallingProc()   ...   Call CalledProc(Arg2:=200000, Arg3:=300000)   ... End Sub Sub CalledProc(Optional Arg1 As Long,Optional Arg2 As Long,           Optional Arg3 As Long,Optional Arg4 As Long)   [Subprocedure code] End Sub 

The := operator specifies that the preceding element is the name of an argument; named arguments need not be entered in the order that the arguments appear in the called procedure. However, if you want to omit an argument or arguments, the corresponding argument name(s) of the called procedure must be preceded by the keyword Optional. Missing arguments return Null values to subprocedure code, but you can supply a default argument value in the subprocedure. If you omit the As Datatype modifier of an argument in the called procedure, the argument assumes the default Variant data type.