Learn Access Now! Chapter 19 Previous Section in Chapter 20 Next Section in Chapter 20 Chapter 21
(This is section 2 of 6 in this chapter)
In VBA, a program consists of procedures. These procedures are made up of individual program lines, which you must put together according to VBA rules. These rules are referred to as syntax. Think of syntax as programming grammar--if you don't follow the rules, VBA can't understand what you are trying to do.
There are many elements that go into a program, including the following:
The following sections explain each of these elements.
When you write programs in VBA, you create programming statements. For example, consider the following line of code, which is commonly used in programs:
Option Compare Database
The first two words of this line are a VBA statement; the last word is a modifier for the statement--sort of like an argument when you are writing macros. VBA defines many different statements (scores of them) you can use in your programs. These statements do everything from defining how VBA should behave to setting the current disk drive.
Note: A detailed description of each VBA statement is beyond the scope of this book. If you want to see what statements are available, use the on-line Help system available within the VBA Editor to search for Statements, then choose the Statements Reference topic.
Your VBA programs use variables to store information. Just like you can store data in a field within a table, you can store information in a variable for use in a program. Variables are so named because their contents can vary, meaning your program can change their values.
Variables can be very powerful. They let you create general-purpose routines that can act and make decisions based on the value within the variable. For example, consider the following logical sentence:
In this instance, the temperature is the variable. If it is high enough, you will do something; if not, you will not. Variables serve the same purpose in a program. Later in this chapter, you will learn how VBA uses variables to make decisions within your programs.
In Chapter 3, "Taking a Closer Look," you learned about data types in relation to information stored in a database. When you define a table, you need to define what type of information Access will store in each field of the table. In this way, variables are no different than fields. You need to be aware of what information your program is going to store in the variable, and then you need to use the correct data type so VBA knows how to handle the value. VBA supports eleven different data types, as indicated in Table 20-1.
| Data Type | Type of Values | |
|---|---|---|
| Boolean | Either logical True or logical False | |
| Byte | Numbers between 0 and 255 | |
| Integer | Numbers between -32,768 and 32,767 | |
| Long | Numbers between -2,147,483,648 and 2,147,483,647 | |
| Single | Numbers between -3.402823E38 and 3.402823E38 | |
| Double | Numbers between -1.79769313486232D308 and 1.79769313486232D308 | |
| Currency | Numbers between -922,337,203,685,477.5808 and 922,337,203,685,477.5807 | |
| Date | Date between January 1, 100 and December 31, 9999 | |
| String | Up to 65,535 characters (approximately) | |
| Object | Reference to a database object | |
| Variant | Varies based on the characteristics of the information |
Table 20-1 Data types supported by VBA.
Notice that some of the data types shown in Table 20-1 are numeric data types; this means they hold number values. When you take into account the setting of the Field Size property for the field, VBA data types correspond to field data types. As you can see from Table 20-1, the primary difference between the numeric data types is the size and accuracy of the values they can hold.
Scientific Notation
Very large and very small numbers are cumbersome to write. To get around this, you can use scientific notation. Using scientific notation, you can express very large or very small numbers easily. For example, you can write the number 7,884,000,000,000,000 as 7.884E15 (notice the E in the middle of the number). Likewise, you can write the number 0.000123 as 1.23E-4.
To convert a number to scientific notation, move the decimal point so that the resulting number is between 1 and 10. After the E, specify the number of places you moved the decimal point. If you move the decimal point to the left, use a positive number after the E; if you move it to the right, use a negative number. To convert a number from scientific notation, you reverse this process--move the decimal place the number of positions indicated by the number after the E.
The way VBA expresses scientific notation depends on the data type being used. VBA uses the E when you work with a single data type and the D when you work with a double data type. Thus, 9.14E12 refers to a single-precision data type, and 9.14D12 refers to a double-precision value.
The String data type is comparable to the text data type for fields. String type variables can contain any characters--letters, numbers, or symbols--which you can think of.
The Date data type is not really a date, as you might expect. For instance, humans represent a date such as June 11, 1999. VBA, however, works with date serial numbers. These are a numeric representation of a date, in the form of a number. The appearance of that number (for instance, June 11, 1999) is the format of the number, not the date serial number itself. Access uses the special Date data type to contain serial numbers which represent any date between January 1, 100 and December 31, 9999. (No Y2K bug here!)
Another interesting data type is Object. This data type is actually a reference to an object tracked by Access. Many of the actions you perform in VBA are upon objects, such as tables, forms, reports, and the pieces thereof. Each of these are objects, and can be addressed within VBA. Later in this chapter you will learn more about VBA objects in Access.
The last VBA data type is not supported directly in setting data types for fields. The Variant data type automatically adapts to the type of information it contains. If you store a number in a variant, it behaves like a numeric data type. If you store characters in it, it behaves like a string. In addition, you can store special values in a variant, such as dates and times. There are nine types of data you can store in variants:
Most of these types of data should look familiar by now. The first two, however, may not. A variant is empty if your program has never assigned it a value. Typically, a variant is empty at the beginning of your program, before you ever use it. A variant is Null if your program has initialized it but it currently contains no data.
Before you can use a variable within your program, you need to declare it. This simply means that you need to let VBA know what name you are going to use for the variable and what data type it will store. There are two ways you can declare a variable--either implicitly or explicitly.
Implicit declaration means you simply start using the variable. For example, if VBA encounters the following line in your program, and you have never used the variable LittleCount before, then VBA assumes you want to define a variable by the name of LittleCount.
LittleCount = 123.456
Because you are assigning a single precision value to the variable, you have implied the variable's type as single.
Explicit declaration means you declare the variable before actually using it. You explicitly declare using one of the following declaration statements
In general, you should use explicit declarations in your programs. In this way, VBA can check to make sure you are using your variables correctly. To enforce explicit variable declaration, you can put the following statement in the declarations section of your program (before you define any variables):
Option Explicit
In order to keep related information together, VBA lets you define variable arrays. Arrays use subscripts to differentiate between different elements of the same overall variable. For instance, you can declare a variable as a string and give it 15 subscripts, as shown here:
Dim Employee(15) As String
In this case, there are 15 occurrences of Employee, numbered 1 through 15, as in Employee(1), Employee(2), Employee(3), and so on through Employee(15). Each element of the array is a different variable. Because these elements share the same base name (Employee), your program can process the information in the array easier. Each element in the Employee array can store a character string. In this example, Employee has 15 elements, but only one dimension. You can also define arrays to have more dimensions. Thus, you could declare the following type of variable:
Dim Employee(15,20) As String
In this case, Employee has 300 elements (15 × 20 = 300), each one a separate variable, and two dimensions. You can use as many dimensions as you desire, but most programmers stick with one- or two-dimensional arrays simply because their needs never extend past that.
To inform VBA that a particular variable should contain a particular value, you need to assign the value to the variable. To assign a value to a variable, use the assignment operator (the equal sign) in an equation, as shown here:
Dim SpeedLimit As Integer
SpeedLimit = 65
After you program this assignment, you can use the variable SpeedLimit anywhere else in your program and VBA will understand you actually mean the number 65.
To assign a group of characters to a string variable, just surround the characters with quotation marks. As shown in the following, the quotation marks indicate to VBA where the string begins and ends:
Dim BookTitle As String
BookTitle = "Learn Access Now"
Naming Variables
When you choose names for your variables, you must follow VBA guidelines. If you disregard the guidelines, VBA will not behave in the way you want. The guidelines are:
Names must begin with a letter of the alphabet
Names cannot contain spaces
Names must be no longer than 40 characters
Names cannot use words reserved for VBA use
Reserved words are those which VBA or Access uses for other purposes--typically as statements, functions, or keywords. If you want to see the list of reserved words, search for the term reserved words in the online Help system within the VBA Editor.
An operator is a symbol that informs VBA about the operation you want to perform. For instance, the expression 2 + 2 uses an operator--the plus sign. There are 4 general categories of VBA operators:
There are 20 different operators spread among these 4 categories, as shown in Table 20-2.
| Operator | Category | Meaning | ||
|---|---|---|---|---|
| + | Math | Addition | ||
| - | Math | Subtraction | ||
| * | Math | Multiplication | ||
| / | Math | Division | ||
| ^ | Math | Exponentation | ||
| \ | Math | Integer division | ||
| Mod | Math | Modulus | ||
| = | Comparison | Equal | ||
| < | Comparison | Less than | ||
| > | Comparison | Greater than | ||
| <= | Comparison | Less than or equal | ||
| >= | Comparison | Greater than or equal | ||
| <> | Comparison | Not equal | ||
| & | String | Concatenation | ||
| AND | Logical | And | ||
| EQV | Logical | Equivalent | ||
| IMP | Logical | Implication | ||
| NOT | Logical | Not (or the logical opposite of) | ||
| OR | Logical | Or | ||
| XOR | Logical | Exclusive Or |
Table 20-2 Operators in VBA.
Technically, a function is a procedure that returns a value. VBA has a wide variety of built-in functions which you can use in your programs. These functions are important because they keep you from having to "reinvent the wheel" for common tasks.
There are many, many functions built into VBA. These functions are categorized by the type of work they do. The categories are:
Note: If you want to see information about a particular function, you can search for that function in the on-line Help system within the VBA Editor.
When you use a function, you generally use it in an equation or some other place where a value is needed. This is because functions, by definition, return a value. For instance, if you want to find the length of a string, you can use the Len function as follows:
Dim HowLong As Integer, MyString As String
HowLong = Len(MyString)
These statements set the variable HowLong equal to the length of the value in MyString. Thus, if there were 25 characters in MyString, the Len function will assign the value 25 to the variable HowLong.
Notice, also, that functions require the use of arguments (sometimes called parameters). In this example, the variable MyString is an argument for the Len function. Some functions require only a single argument; others can require quite a few. These arguments are similar to the arguments you use with macro actions. The number, type, and specifications for arguments will vary based on the function.
Throughout this book you have learned ways you can manually work with database objects--things like tables, forms, reports, macros, and the like. From a programming perspective, these objects fit into a technology known as DAO, which means data access objects. Once you understand the fundamentals of DAO, you can refer to any portion of any VBA object you desire, all under program control.
The DAO framework recognizes two types of database components: objects and collections. An object is typically a single database component (many of which you have already learned about), although it can also encompass other objects or collections. A collection is a group of related objects; it provides a way for you to reference an entire group of objects at one time. Table 20-3 lists the various collections and objects that belong to the DAO framework.
| Collection | Object | Purpose | ||
|---|---|---|---|---|
| Containers | Container | Hold information about other objects | ||
| Databases | Database | An open Access database | ||
| DBEngine | The Jet database engine | |||
| Documents | Document | Information about other objects in the database | ||
| Fields | Field | A column that is part of a table, query, recordset, index, or relation | ||
| Groups | Group | A group of user accounts in the current workgroup | ||
| Indexes | Index | A table index | ||
| Parameters | Parameter | A query parameter | ||
| Properties | Property | A property of an object | ||
| QueryDefs | QueryDef | A saved query definition | ||
| Recordsets | Recordset | The records defined by a table or query | ||
| Relations | Relation | A relationship between fields in tables or queries | ||
| TableDefs | TableDef | A saved table in a database | ||
| Users | User | A user account in the current workgroup | ||
| Workspaces | Workspace | An active session of the Jet database engine |
Table 20-3 DAO collections and objects recognized by VBA.
The collections and objects that make up DAO are defined by the Jet database engine, which is the heart of Access. In addition to DAO objects and collections, Access also recognizes the group of objects and collections (referred to as Access objects) shown in Table 20-4. (Note that not all objects have corresponding collections.)
| Collection | Object | Purpose | ||
|---|---|---|---|---|
| Application | The current instance of Access | |||
| Control | A control on a form or report | |||
| Debug | The VBA immediate window | |||
| Forms | Form | A form or subform | ||
| Reports | Report | A report or subreport | ||
| Screen | The video display |
Table 20-4 Microsoft Access objects
Earlier in this book, you learned about properties and how you use them to define objects in tables, forms, reports, and other database objects. Likewise, you use properties to define DAO objects and collections and Microsoft Access objects. Most of the object and collection properties are the same properties you can manipulate in a Properties window.
Objects and collections also have methods, which are special functions that act on an object or collection. For instance, a Workspace object has a CreateDatabase method; when you use the Workspace object, its method creates a new database. As with properties, the available methods will vary based on the object or collection.
Access provides a method that lets you address anything within your database. Since objects and collections form a hierarchical system, it may be helpful to compare the Access address system to a similar system you should already be familiar with--the directory structure on your disk drive.
As you move through different levels in a directory structure, you indicate a change in level using a backslash. For example, consider the following pathname:
C:\WINDOWS\SYSTEM\SYSEDIT.EXE
As you read the pathname from right to left, you learn that the SYSEDIT.EXE file is in the SYSTEM directory, which is within the WINDOWS directory, which is on the C: drive. Each backslash indicates you are moving down a level in the directory structure, when reading from left to right. In Access, you use exclamation points (!) and periods (.) to denote changes in the level. The exclamation points are called bangs, and the periods are called dots. The general rules which govern the use of bangs and dots are as follows:
How do you apply these rules? Simple. Suppose you want to refer to the Business Customers table you created earlier in this book. This table is part of the Customer database, so you can address it as follows:
Customer![Business Customers]
Notice the brackets around the table name and the bang between levels of the hierarchy. If you want to refer to a property within the table, you can use a dot and the property name, as in the following:
Customer![Business Customers].RecordCount
Earlier in this chapter, you learned that within your programs you use variables to represent actual values. These variables can be a specific data type, which indicates the type of information they contain. For instance, consider the following code:
Dim SalesTax As Single
SalesTax = .05
The purpose of the first line is to specify a new variable named SalesTax, which will have the characteristics of a Single data type. This line does not indicate what value the variable will contain; the second line assigns the variable's value. After Access executes the second line, VBA uses the value .05 whenever you refer to the SalesTax variable. Notice there is a distinct difference between an actual value (.05) and the label used to refer to that value (the variable SalesTax).
You refer to objects within VBA in the same manner. You can assign a label--a variable name--to the object. Access refers any references you make to the label to the actual object that the label represents. For instance, take a look at the following:
Dim WorkDB As Database
Set WorkDB = DBEngine.Workspaces(0).Databases(0)
In the first line, the Dim statement defines the characteristics of the variable WorkDB. This variable has the characteristics of a database object. The second line performs the actual assignment, using the Set statement. The (0) within parentheses--it appears twice--is an offset index into the collection being referenced. Since VBA starts counting at zero (not one), Workspaces(0) refers to the first Workspace object in the Workspaces collection. Likewise, Databases(0) refers to the first Database object in the Databases collection.
After Access executes the second line, VBA knows that WorkDB refers to a physical entity, using the full name you specified (the name is constructed according to the rules defined in the previous section). Again, notice there is a distinct difference between the actual object (the part to the right side of the equal sign in the Set statement) and the object variable subsequently used to reference the object (WorkDB).
Learn Access Now! Chapter 19 Previous Section in Chapter 20 Next Section in Chapter 20 Chapter 21