Variables and Constants

In addition to using Visual Basic code to work with the controls on any open forms or reports (as you can with macros), you can declare and use named variables in Visual Basic code for storing values temporarily, calculating a result, or manipulating any of the objects in your database. To create a value available anywhere in your code, you can define a global variable, as you can find in the modGlobals module in the Conrad Systems Contacts sample database.

Another way to store data in Visual Basic is with a constant. A constant is a data object with a fixed value that you cannot change while your application is running. You’ve already encountered some of the built-in constants in Access 2007-Null, True, and False. Visual Basic also has a large number of intrinsic constants-built-in constants that have meaningful names-that you can use to test for data types and other attributes or that you can use as fixed arguments in functions and expressions. You can view the list of intrinsic constants by searching for the Visual Basic Constants topic in Help. You can also declare your own constant values to use in code that you write.

In the following sections, you’ll learn about using variables to store and calculate data and to work with database objects.

Data Types

Visual Basic supports data types for variables and constants that are similar to the data types you use to define fields in tables. It also allows you to define a variable that is a pointer to an object (such as a form or a recordset). The data types are described in Table 19–1.

Table 19–1: Visual Basic Data Types
Open table as spreadsheet

Data Type	Size	Data-Typing Character	Can Contain
Boolean	2, bytes	(none)	True (−1) or False (0)
Byte	1 byte	(none)	Binary data ranging in value from 0. through 255
Integer	2 bytes	%	Integers from −32,768 through 32,767
Long	4 bytes	&,	Integers from −2,147,483,648 through 2,147,483,647
Single	4 bytes	!	Floating-point (imprecise) numbers from approximately −3.4 × 1038 through 3.4 × 1038
Double	8, bytes	#	Floating-point (imprecise) numbers from approximately −1.79 × 10308 through 1.79 × 10308
Currency	8, bytes	@	A scaled integer with four decimal places from −922,337,203,685,477.5808 through 922,337,203,685,477.5807
Decimal	14 bytes	(none)	A precise number with up to 29 digits and up to 28 decimal places from −79.228 × 1027 to 79.228 × 1027 (Visual Basic in Access supports the Decimal data type only as a type within the Variant data type.)
String	10 bytes plus 2, bytes per character	$	Any text or binary string up to approximately 2, billion bytes in length, including text, hyperlinks, memo data, and “chunks” from an ActiveX object; a fixed-length string can be up to 65,400 characters long
Date	8, bytes	(none)	Date/time values ranging from January 1, 100, to December 31, 9999
Object	4 bytes	(none)	A pointer to an object-you can also define a variable that contains a specific type of object, such as the Database object
Variant	16 bytes through approximately 2, billion bytes	(none)	Any data, including Empty, Null, and date/time data (Use the VarType function to determine the current data type of the data in the variable. A Variant can also contain an array of Variants. Use the IsArray function to determine whether a Variant is an array.)
User-defined	Depends on elements defined	(none)	Any number of variables of any of the above data types

You can implicitly define the data type of a variable by appending a data-typing character, as noted in the table above, the first time you use the variable. For example, a variable named MyInt% is an integer variable. If you do not explicitly declare a data variable that you reference in your code and do not supply a data-typing character, Visual Basic assigns the Variant data type to the variable. (See “Declaring Constants and Variables” on page 965 to learn how to explicitly declare data variables.) Note that although the Variant data type is the most flexible (and, in fact, is the data type for all controls on forms and reports), it is also the least efficient because Visual Basic must do extra work to determine the current data type of the data in the variable before working with it in your code. Variant is also the only data type that can contain the Null value.

The Object data type lets you define variables that can contain a pointer to an object. See “Collections, Objects, Properties, and Methods” on page 978 for details about objects that you can work with in Visual Basic. You can declare a variable as the generic Object data type, or you can specify that a variable contains a specific type of object. The major object types are AccessObject, Application, Catalog, Column, Command, Connection, Container, Control, Database, Document, Error, Field, Form, Group, Index, Key, Parameter, Procedure, Property, QueryDef, Recordset, Relation, Report, Table, TableDef, User, View, and Workspace.

Inside Out-Using Option Explicit Is a Good Idea 

You can request that Visual Basic generate all new modules with an Option Explicit statement by selecting the Require Variable Declaration check box on the Editor tab of the Options dialog box, as shown in Figure 19–3. If you set this option, Visual Basic includes an Option Explicit statement in the Declarations section of every new module. This helps you avoid errors that can occur when you use a variable in your code that you haven’t properly declared in a Dim, Public, Static, or Type statement or as part of the parameter list in a Function statement or a Sub statement. (See “Functions and Subroutines” on page 1005.) When you specify this option in a module, Visual Basic flags any undeclared variables it finds when you ask it to compile your code. Using an Option Explicit statement helps you find variables that you might have misspelled when you entered your code.

Variable and Constant Scope

The scope of a variable or a constant determines whether the variable or the constant is known to only one procedure, all procedures in a module, or all procedures in your database. You can create variables or constants that can be used by any procedure in your database (public scope). You can also create variables or constants that apply only to the procedures in a module or only to a single procedure (private scope). A variable declared inside a procedure is always private to that procedure (available only within the procedure). A variable declared in the Declarations section of a module can be private (available only to the procedures in the module) or public. You can pass values from one procedure to another using a parameter list, but the values might be held in variables having different names in the two procedures. See the sections on the Function, Sub, and Call statements later in this chapter.

To declare a public variable, use the Public statement in the Declarations section of a standard module or a class module. All modules attached to forms or reports are class modules. To declare a public constant, use the Public keyword with a Const statement in the Declarations section of a standard module. You cannot declare a public constant in a class module. To declare a variable or a constant that all procedures in a module can reference, define that variable or constant in the Declarations section of the module. (A variable defined in a Declarations section is private to the module unless you use the Public statement.) To declare a variable or a constant used only in a particular procedure, define that variable or constant as part of the procedure.

Visual Basic in Access 2007 allows you to use the same name for variables or constants in different module objects or at different levels of scope. In addition, you can declare public variables and constants in form and report modules as well as public variables and constants in standard modules.

To use the same name for public variables and constants in different module objects or form or report modules, specify the name of the module to which it belongs when you refer to it. For example, you can declare a public variable named intX in a module object with the name modMyModule and then declare another public variable named intX in a second module object, named modMyOtherModule. If you want to reference the intX variable in modMyModule from a procedure in modMyOtherModule (or any module other than modMyModule), you must use

 modMyModule.intX

You can also declare variables or constants with the same name at different levels of scope within a module object or a form or report module. For example, you can declare a public variable named intX and then declare a local variable named intX within a procedure. (You can’t declare a public variable within a procedure.) References to intX within the procedure refer to the local variable, while references to intX outside the procedure refer to the public variable. To refer to the public variable from within the procedure, qualify it with the name of the module, just as you would refer to a public variable from within a different module.

Declaring a public variable in a form or report module can be useful for variables that are logically associated with a particular form or report but that you might also want to use elsewhere. Like the looser naming restrictions, however, this feature can sometimes create confusion. In general, it’s still a good idea to keep common public variables and constants in standard modules and to give public variables and constants names that are unique across all variable names in your application.

Note 

For information on the syntax conventions used in the remainder of this chapter, refer to “Syntax Conventions” in the “Conventions Used In This Book” section at the beginning of this book.