IN THIS CHAPTER
All VBA applications require variables to hold data while the program executes. Variables are like a white board where important information can be temporarily written and read later on by the program. For example, when a user inputs a value on a form, you may need to use a variable to temporarily hold the value until it can be permanently stored in the database or printed on a report. Simply put, a variable is the name you've assigned to a particular bit of data in your application. In more technical terms, a variable is a named area in memory used to store values during program execution.
Variables are transient and do not persist after an application stops running. And, as you'll read in the “Understanding variable scope and lifetime” section later in this chapter, a variable may last a very short time as the program executes or may exist as long as the application is running.
In most cases, you assign a specific data type to each of the variables in your applications. For example, you may create a string variable to hold text data such as names or descriptions. A currency variable, on the other hand, is meant to contain values representing monetary amounts. You shouldn't try to assign a text value to a currency variable because a runtime error may occur as a result.
The variables you use have a dramatic effect on your applications. You have many options when it comes to establishing and using variables in your Access programs. Inappropriately using a variable can slow an application's execution or potentially cause data loss.
This chapter contains everything you need to know about creating and using VBA variables. The information in this chapter helps you use the most efficient and effective data types for your variables while avoiding the most common problems related to VBA variables.
One of the most powerful concepts in programming is the variable. A variable is a temporary storage location for some value and is given a name. You can use a variable to store the result of a calculation, hold a value entered by the user, or read from a table, or you can create a variable to make a control's value available to another procedure.
To refer to the result of an expression, you use a variable's name to store the result. To assign an expression's result to a variable, you use the =
operator. Here are some examples of expressions that assign values to variables:
counter = 1
counter = counter + 1
today = Date()
Figure 25.1 shows a simple procedure using several different variables. Although this is a very simple example of using variables, it effectively demonstrates the basics of using VBA variables:
Dim
keyword establishes the new variables—sFormName
and sCriteria
—within a procedure.Dim
statement. In Figure 25.1, the variable names are sFormName
and sCriteria
, indicating how the variables are used by the procedure.Dim
statement includes the data type of the new variable. In Figure 25.1, both variables are declared as the String
data type.=
operator to assign a literal value—frmContactLog
—to sFormName
. Notice that frmContactLog
is surrounded by quotation marks, making it a literal value. A value pulled from the txtContactID
text box on the form is combined with a literal string—"[ContactID]="
—and assigned to the sCriteria
variable. The data assigned to variables should always be appropriate for the variable's data type.&
) to combine [ContactID]=
and the value in txtContactID
.There is a number of ways to perform each of the tasks you see in Figure 25.1. For example, as you'll read in the “Declaring variables” section later in this chapter, the Dim
statement is not the only way to establish a variable. And, as you'll see throughout this book, the =
operator is not the only way to assign a value to a variable. Also, you don't need to use a variable like sCriteria
to temporarily hold the value generated by combining two values. The two values can just as easily be combined on the fly within the DoCmd.OpenForm
statement:
DoCmd.OpenForm "frmContactLog", _
"[ContactID] = " & Me![txtContactID]
Very few rules govern how you declare and use your variables. You should always strive for readability in your VBA code. In the small example shown in Figure 25.1, you can easily see that sFormName
holds the name of a form, especially because it's used as part of the DoCmd.OpenForm
statement.
Every programming language has its own rules for naming variables. In VBA, a variable name must meet the following conditions:
Sub
, Module
, or Form
.Although you can make up almost any name for a variable, most programmers adopt a standard convention for naming variables. Some common practices include the following:
TotalCost
.counter
.Total_Cost
.iCounter,
while a variable holding a string might be named sLastName
.The “Using a naming convention with variables” section later in this chapter goes into greater detail about the benefits of a naming convention.
When you need to use the contents of a variable, you simply reference its name. When you specify the variable's name, the computer program goes into memory, finds the variable, and gets its contents for you. This process means, of course, that you need to be able to remember and correctly reference the name of the variable.
There are two principle ways to add variables to your applications. The first method—called implicit declaration—is to let VBA automatically create the variables for you. As with most things that are not carefully controlled, you'll find that letting VBA prepare your variables for you is not a particularly good idea and can lead to performance issues in, and problems debugging, your programs (see the “Comparing implicit and explicit variables” section later in this chapter).
Implicit declaration means that VBA automatically creates an Empty variable for each identifier it recognizes as a variable in an application. In the following, there are two implicitly declared variables—sFirstName
and sLastName
. In this example, two variables (sFirstName
and sLastName
) are assigned the text contained in two text boxes (txtFirstName
and txtLastName
), and a third variable (sFullName
) is assigned the combination of sFirstName
and sLastName
, with a space between them.
Private Sub Combine_Implicit()
sFirstName = Me.txtFirstName.Text
sLastName = Me.txtLastName.Text
sFullName = sFirstName & Space(1) & sLastName
End Sub
The second approach is to explicitly declare them with one of the following keywords: Dim
, Static
, Private
, or Public
(or Global
). The choice of keyword has a profound effect on the variable's scope within the application and determines where the variable can be used in the program. (Variable scope is discussed in the “Understanding variable scope and lifetime” section later in this chapter.)
The syntax for explicitly declaring a variable is quite simple:
Dim VariableName As DataType
Static VariableName As DataType
Private VariableName As DataType
Public VariableName As DataType
In each case, the name of the variable and its data type are provided as part of the declaration. VBA reserves the amount of memory required to hold the variable as soon as the declaration statement is executed. Once a variable is declared, you can't change its data type, although you can easily convert the value of a variable and assign the converted value to another variable.
The following example shows the Combine_Implicit
sub rewritten to use explicitly declared variables:
Private Sub Combine_Explicit()
Dim sFirstName As String
Dim sLastName As String
Dim sFullName As String
sFirstName = Me.txtFirstName.Text
sLastName = Me.txtLastName.Text
sFullName = sFirstName & Space(1) & sLastName
End Sub
So, if there's often very little difference between using implicit and explicit variables, why bother declaring variables at all? The following code demonstrates the importance of using explicitly declared variables in your applications:
Private Sub Form_Load()
sDepartment = "Manufacturing"
sSupervisor = "Joe Jones"
sTitle = "Senior Engineer"
'Dozens of lines of code go here
Me.txtDepartment = sDepartment
Me.txtSupervisor = sSuperviser
Me.txtTitle = sTitle
End Sub
In this example code, the txtSupervisor
text box on the form is always empty and is never assigned a value. A line near the bottom of this procedure assigns the value of an implicitly declared variable named Superviser
to the txtSupervisor
text box. Notice that the name of the variable (Superviser
) is a misspelling of the intended variable (Supervisor
). Because the source of the assignment appears to be a variable, VBA simply creates a new variant named Superviser
and assigns its value (which is, literally, nothing
) to the txtSupervisor
text box. And, because the new Superviser
variable has never been assigned a value, the text box always ends up empty. Misspellings such as this are very common and easy to overlook in long or complex procedures.
Furthermore, the code shown in this example runs fine and causes no problem. Because this procedure uses implicit variable declaration, Access doesn't raise an error because of the misspelling, and the problem isn't detected until someone notices the text box is always empty. Imagine the problems you'd encounter in a payroll or billing application if variables went missing because of simple spelling errors!
When you declare a variable, Access sets up a location in the computer's memory for storing a value for the variable ahead of time. The amount of storage allocated for the variable depends on the data type you assign to the variable. More space is allocated for a variable that will hold a currency amount (such as $1,000,000) than for a variable that will never hold a value greater than, say, 255. This is because a variable declared with the Currency data type requires more storage than another variable declared as a Byte data type. (Data types are discussed later in this chapter, in the “Working with Data Types” section.)
Even though VBA doesn't require you to declare your variables before using them, it does provide various declaration commands. Getting into the habit of declaring variables is good practice. A variable's declaration assures that you can assign only a certain type of data to it—always a numeric value or only characters, for example. In addition, you attain real performance gains by pre-declaring variables.
To declare a variable, you use the Dim
keyword. (Dim is an abbreviation of the archaic Dimension programming term—because you're specifying the dimension of the variable.) When you use the Dim
keyword, you must supply the variable name that you assign to the variable. Here's the format for a Dim
statement:
Dim [VariableName] [As DataType]
The following statement declares the variable iBeeps
as an integer data type:
Dim iBeeps As Integer
Notice that the variable name follows the Dim
statement. In addition to naming the variable, use As
Data Type to specify a data type for the variable. The data type is the kind of information that will be stored in the variable—String, Integer, Currency, and so on. The default data type is Variant; it can hold any type of data.
Table 25.1 in the next section lists all the data types available.
Table 25.1 VBA Data Types
Data Type | Range | Description |
Boolean | True or False | 2 bytes |
Byte | 0 to 255 | 1-byte binary data |
Currency | –922,337,203,685,477,5808 to 922,337,203,685,477,5807 | 8-byte number with fixed decimal point |
Decimal | +/–79,228,162,514,264,337,593,543,950,335 with no decimal point +/–7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest nonzero number is +/–0.0000000000000000000000000001 | 14 bytes |
Date | 01 Jan 100 00:00:00 to 31 Dec 9999 23:59:59 | 8-byte date/time value |
Double | –1.79769313486231E308 to –4.94065645841247E–324 for negative values and 4.94065645841246544E–324 through 1.79769313486231570E+308 for positive values | 8-byte floating-point number |
Integer | –32,768 to 32,767 | 2-byte integer |
Long | –2,147,483,648 to 2,147,483,647 | 4-byte integer |
Object | Any object reference | 4 bytes |
Single | –3.402823E38 to –1.401298E–45 for negative values and 1.401298E–45 to 3.402823E38 for positive values | 4-byte floating-point number |
String (fixed length) | 1 to approximately 65,400 | Length of string |
String (variable length) | 0 to approximately 2,000,000,000 | 10 bytes plus length of string |
Variant (with characters) | 0 to approximately 2,000,000,000 | 22 bytes plus length of string |
Variant (with numbers) | Any numeric value up to the range of the Double data type (see earlier in this table) | 16 bytes |
When you use the Dim
statement to declare a variable in a procedure, you can refer to that variable only within that procedure. Other procedures, even if they're stored in the same module, don't know anything about the variable declared within a procedure. Such a variable is often described as local because it's declared locally within a procedure and is known only by the procedure that owns it. (You can read more about variable scope in the “Understanding variable scope and lifetime” section later in this chapter.)
Variables also can be declared in the declarations section of a module. Then all the procedures in the module can access the variable. Procedures outside the module in which you declared the variable, however, can't read or use the variable.
To make a variable available to all modules in the application, use the Public keyword when you declare the variable. Figure 25.2 illustrates declaring a public variable.
Although you can declare a public variable in any module, the best practice for declaring public variables is to declare them all in a single standard module that's used only to store public variables. Public variables, while necessary in some cases, should be limited. Because any procedure in your project can change a public variable's value, it can be difficult to find which procedure is making an unwanted change. With all the publically declared variables in one place, it's easy to locate them, and it's easy to see if you're using too many and may need to rethink your code structure.
The declarations section in Figure 25.2 shows the use of the Private
keyword to declare variables. Technically, there is no difference between Private
and Dim
when used in the declarations section of a module, but using Private
at the module level to declare variables that are available to only that module's procedures is a good idea. The Private
keyword ensures that all procedures in the module can access the variable, but all procedures in other modules cannot. Declaring private variables contrasts with:
Dim
, which must be used at the procedure levelPublic
, the other method of declaring variables in modules, making understanding your code easierWhen you declare a variable, you use the AS
clause to specify a data type for the new variable. Because Access is a database development system, it's not surprising that variable data types are similar to field data types in an Access database table.
When you declare a variable, you also specify the data type for the variable. Each variable has a data type. The data type of a variable determines what kind of information can be stored in the variable.
A string variable—a variable with a data type of String—can hold any character that you can type on a keyboard, plus a few others. Once created, a string variable can be used in many ways: comparing its contents with another string, pulling parts of information out of the string, and so on. If you have a variable defined as a String, however, you cannot use it to do mathematical calculations.
Table 25.1 describes the 12 fundamental data types supported by VBA.
Most of the time, you use the String, Date, Long, and Currency or Double data types. If a variable always contains whole numbers between –32,768 and 32,767, you can save bytes of memory and gain a little speed in arithmetic operations if you declare the variable an Integer data type.
When you want to assign the value of an Access field to a variable, you need to make sure that the type of the variable can hold the data type of the field. Table 25.2 shows the corresponding VBA data types for Access field types.
Table 25.2 Access and VBA Data Types
Access Field Data Type | VBA Data Type |
Attachment | — |
AutoNumber (Long Integer) | Long |
AutoNumber (Replication ID) | — |
Currency | Currency |
Calculated | — |
Date/Time | Date |
Long Text | String |
Number (Byte) | Byte |
Number (Integer) | Integer |
Number (Long Integer) | Long |
Number (Single) | Single |
Number (Double) | Double |
Number (Replication ID) | — |
OLE object | String |
Short Text | String |
Hyperlink | String |
Yes/No | Boolean |
Now that you understand variables and their data types, you're ready to learn how to use them when writing procedures.
The default data type for VBA variables is Variant. This means that, unless you specify otherwise, every variable in your application will be a Variant. As you read earlier in this chapter, although useful, the Variant data type is not very efficient. Its data storage requirements are greater than the equivalent simple data type (a String, for instance), and the computer spends more time keeping track of the data type contained in a Variant than for other data types.
Here's an example of how you might test for the speed difference when using implicitly declared Variant variables and explicitly declared variables. This code is found behind frmImplicitTest
in Chapter25.accdb
.
'Use a Windows API call to get the exact time:
Private Declare Function GetTickCount _
Lib "kernel32" () As Long
Private Sub cmdGo_Click()
Dim i As Integer
Dim j As Integer
Dim snExplicit As Single
Me.txtImplicitStart.Value = GetTickCount()
For o = 1 To 10000
For p = 1 To 10000
q = i / 0.33333
Next p
Next o
Me.txtImplicitEnd.Value = GetTickCount()
Me.txtImplicitElapsed.Value = _
Me.txtImplicitEnd.Value - Me.txtImplicitStart.Value
DoEvents 'Force Access to complete pending operations
Me.txtExplicitStart.Value = GetTickCount()
For i = 1 To 10000
For j = 1 To 10000
snExplicit = i / 0.33333
Next j
Next i
Me.txtExplicitEnd.Value = GetTickCount()
Me.txtExplicitElapsed.Value = _
Me.txtExplicitEnd.Value - Me.txtExplicitStart.Value
DoEvents
End Sub
In this small test, the loop using implicitly declared variables required approximately 2.7 seconds to run, while the loop with the explicitly declared variables required only 2.5 seconds. This is a performance enhancement of approximately 10 percent just by using explicitly declared variables.
The actual execution time of this—or any—VBA procedure depends largely on the relative speed of the computer and the tasks the computer is executing at the time the procedure is run. Desktop computers vary a great deal in CPU, memory, and other resources, making it quite impossible to predict how long a particular bit of code should take to execute.
Access provides a simple compiler directive that forces you to always declare the variables in your applications. The Option Explicit
statement, when inserted at the top of a module, instructs VBA to require explicit declaration of all variables in the module. If, for example, you're working with an application containing a number of implicitly declared variables, inserting Option Explicit
at the top of each module results in a check of all variable declarations the next time the application is compiled.
Because explicit declaration is such a good idea, it may not come as a surprise that Access provides a way to automatically ensure that every module in your application uses explicit declaration. The Editor tab of the Options dialog box (shown in Figure 25.3) includes a Require Variable Declaration check box. This option automatically inserts the Option Explicit
directive at the top of every module created from this point in time onward.
The Require Variable Declaration option doesn't affect modules already written. This option applies only to modules created after this option is selected, so you'll have to type the Option Explicit
statement yourself in existing modules. Require Variable Declaration is not set by default in current versions of Access. You must set this option yourself to take advantage of having Access add Option Explicit
to all your modules.
Like most programming languages, applications written in VBA tend to be quite long and complex, often occupying many thousands of lines of code. Even simple VBA programs may require hundreds of different variables. VBA forms often have dozens of different controls on them, including text boxes, command buttons, option groups, and other controls. Keeping track of the variables, procedures, forms, and controls in even a moderately complicated VBA application is a daunting task.
One way to ease the burden of managing the code and objects in an application is through the use of a naming convention. A naming convention applies a standardized method of supplying names to the objects and variables in an application.
The most common naming convention used in Access applications uses a one- to four-character prefix (a tag) attached to the base name of the objects and variables in a VBA application. The tag is generally based on the type of control for controls and the type of data the variable holds or the scope for variables. For example, a text box containing a person's last name might be named txtLastName
, while a command button that closes a form would be named cmdClose
or cmdCloseForm
.
The names for variables follow a similar pattern. The string variable holding a customer name might be named sCustomer
, and a Boolean variable indicating whether the customer is currently active would be bActive
.
Using a naming convention is not difficult. Most of the code in this book uses one-character prefixes for variables and three-character prefixes for control names. The actual naming convention you use is not important. The important point is that you use the convention consistently. As you write more VBA code, the right convention for you will become obvious. Table 25.3 shows one naming convention.
Table 25.3 A Sample Naming Convention
Control/Data Type | Prefix | Example |
Control: Text Box | txt | txtFirstName |
Control: Label | lbl | lblFirstName |
Control: Command Button | cmd | cmdClose |
Control: Frame | frm | frmOptions |
Control: Combo Box | cbx | cbxCustomers |
Control: List Box | lbx | lbxProducts |
Control: Check Box | chk | chkActive |
Control: Option Button | opt | optFemale |
Type: Byte | bt | btCounter |
Type: Boolean | b | bActive |
Type: Integer | i | iCounter |
Type: Long | l | lCustomerID |
Type: Single | sn | snTaxRate |
Type: Double | d | dGrossPay |
Type: Currency | c | cNetSales |
Type: Date | dt | dtHired |
Type: Object | o | oControl |
Type: String | s | sLastName |
Type: Variant | v | vCompany |
Scope: Local | None | sState |
Scope: Private | m | msState |
Scope: Public | g | gsState |
One benefit to using shorter prefixes for variables and longer prefixes for controls is that it becomes easy to tell them apart when you're reading your code. Also note that more commonly used data types get the one-character prefixes. You'll typically use Booleans more often than Bytes, so a shorter prefix for Booleans saves typing.
Some developers don't use any prefixes for variables. There's nothing wrong with that. There are some advantages to using prefixes, however. The first advantage is that you can identify the data type at the point you're using the variable. It's easy to see that a statement like sCustomer = chkActive
may cause a problem. You know sCustomer
is a String data type and chkActive
, being a check box control, returns a Boolean value. Another advantage is variable name uniqueness. Recall that the variable naming rules state that all variable names must be unique and that you can't use reserved keywords for variable names. That means that you can't have a Boolean variable named Print
that determines whether to print a report. By using a prefix, bPrint
does not violate any rules.
Including an additional prefix for the scope conveys similar advantages. Knowing the scope of the variable in the portion of code you're working helps debug the code when things go wrong. It also allows you to use similar variables with different scopes. For example, you could have a private module-level variable mbIsEnabled
that applies to all the code in your module and still have a local procedure-level variable bIsEnabled
for use in only that procedure.
One final advantage to a naming convention that uses a mix of uppercase and lowercase letters is that you can detect spelling errors in your variable names very quickly. VBA will change the case of the variable name to match the case you use when you declare it. If you declare a variable using Dim sFirstName As String
and later type
(all lowercase), as soon as you complete that line of code, your variable will change to sfirstname = "Larry"
sFirstName = "Larry"
. That immediate feedback will help you catch spelling errors before they become problems.
A variable is more than just a simple data repository. Every variable is a dynamic part of the application and may be used at different times during the program's execution. The declaration of a variable establishes more than just the name and data type of the variable. Depending on the keyword used to declare the variable and the placement of the variable's declaration in the program's code, the variable may be visible to large portions of the application's code. Alternatively, a different placement may severely limit where the variable can be referenced in the procedures within the application.
The visibility of a variable or procedure is called its scope. A variable that can be seen and used by any procedure in the application is said to have public scope. A variable that is available to any procedure in one module is scoped private to that module. A variable that is usable by a single procedure is said to have scope that is local to that procedure.
There are many analogies for public and private scope. For example, a company is likely to have a phone number that is quite public (the main switchboard number) and is listed in the phone book and on the company's website; each office or room within the company might have its own extension number that is private within the company. A large office building has a public street address that is known by anyone passing by the building; each office or suite within that building will have a number that is private within that building.
Variables declared within a procedure are local to that procedure and can't be used or referenced outside that procedure. Most of the listings in this chapter have included a number of variables declared within the procedures in the listings. In each case, the Dim
keyword was used to define the variable. Dim
is an instruction to VBA to allocate enough memory to contain the variable that follows the Dim
keyword. Therefore, Dim iMyInt As Integer
allocates less memory (2 bytes) than Dim dMyDouble As Double
(8 bytes).
The Public
(or Global
) keyword makes a variable visible throughout an application. Public
can be used only at the module level and can't be used within a procedure. Most often, the Public
keyword is used only in standard (stand-alone) modules that are not part of a form. Figure 25.4 illustrates variables declared with three very different scopes. This code can be found in the modScope
module in Chapter25.accdb
.
Every variable declared in the declarations section of a standard module is private to that module unless the Public
keyword is used. Private
restricts the visibility of a variable to the module in which the variable is declared. In Figure 25.4, the gsAppName
variable declared with Public
scope at the top of the module will be seen everywhere in the application while the mbIsComplete
variable declared in the next statement is accessible only within the module. The sMessage
variable is declared inside a procedure, so only that procedure can see it.
Misunderstanding variable scope is a major cause of serious bugs in many Access applications. It's entirely possible to have two same-named variables with different scopes in an Access VBA project. When ambiguity exists, Access always uses the “closest” declared variable.
Consider two variables named MyVariable
. One of these variables is global (Public
) in scope, while the other is a module-level variable declared with the Private
keyword. In any procedure Access uses one or the other of these variables. In a module where MyVariable
is not declared, Access uses the public variable. The private variable is used only within the module containing its declaration.
The problem comes when multiple procedures use a variable with the same name as the multiple-declared MyVariable
. Unless the developer working on one of these procedures has diligently determined which variable is being used, a serious error may occur. All too easily, a procedure might change the value of a public variable that is used in dozens of places within an application. If even one of those procedures changes the public variable instead of a more local variable, a very difficult-to-resolve bug occurs.
Variables are not necessarily permanent citizens of an application. Just as their visibility is determined by the location of their declaration, their lifetime is determined by their declaration as well. A variable's lifetime determines when it's accessible to the application.
By default, local variables exist only while the procedure is executing. As soon as the procedure ends, the variable is removed from memory and is no longer accessible. As already discussed, the scope of procedure-level variables is limited to the procedure and cannot be expanded beyond the procedure's boundaries.
A variable declared in the declarations section of a form's module exists as long as the form is open regardless of how it's declared (Public
, Private
, Dim
, and so on). All the procedures within the form's module can use the module-level variables as often as they need, and they all share the value assigned to the variable. When the form is closed and removed from memory, all its variables are removed as well.
The greatest variable lifetime is experienced by the variables declared as Public
in standard modules. These variables are available as soon as the VBA application starts up, and they persist until the program is shut down and removed from memory. Therefore, public variables retain their values throughout the application and are accessible to any of the procedures within the program. Private variables (declared with the Private
keyword) declared at the top of standard modules endure throughout the application, but following the rules of variable scope, they're accessible only from within the module.
There is one major exception to the general rule that procedure-level variables persist only as long as the procedure is running. The Static
keyword makes a procedure-level variable persist between calls to the procedure. Once a value has been assigned to a static variable, the variable retains its value until it's changed in another call to the procedure.
An alternative to using static variables is to declare a global or module-level variable and use it each time a particular procedure is called. The problem with this approach is that a global or module-level variable is accessible to other procedures that are also able to modify its value. You can experience undesirable side-effect bugs by unwittingly changing the value of a widely scoped variable without realizing what has happened. Because of their procedure-limited scope, static variables are one way to avoid side-effect bugs.
Incidentally, declaring a procedure with the Static
keyword makes all variables in the procedure static as well. In the following listing, both variables—iStatic
and iLocal
—in the StaticTest
sub are static, in spite of their local declarations within the procedure. The Static
keyword used in the procedure's heading makes both variables static in nature.
Private Static Sub StaticTest()
'Both variables are static because of the
' 'Static' keyword in the procedure declaration
Static lStatic As Long
Dim lLocal As Long
lStatic = lStatic + 1
lLocal = lLocal + 1
Me.txtLocal.Value = lLocal
Me.txtStatic.Value = lStatic
End Sub
Now that you know how declaring a variable affects its scope and lifetime, you may be wondering how you decide what scope to make a particular variable. The answer is easy: Always limit the scope of your variables as much as possible. That means that most of your variables will be at the procedure level and declared with the Dim
keyword. If you find that you need to retain the value of a variable for the next time you call the procedure, change the Dim
to Static
. By doing that, you'll increase the lifetime but not the scope. Limiting the scope reduces the number of places that a variable can change, which makes it easier to track down problems when they occur.
If another procedure in the same module needs to use a variable, pass that variable to the other procedure as a parameter. Parameters are discussed later in this chapter. When passed as a parameter, the variable is local to the procedure it's declared in and local to the procedure it's passed to, but no other procedures can see it. If the value of your variable changes unexpectedly, you have only two procedures to debug to find out why.
Sometimes you find yourself passing variables from procedure to procedure within the same module. When more than a few procedures in a module have the same variable passed to them, it may be time to declare that variable as Private
to the module.
The next level of variable scope is when a procedure outside the module needs to use a variable. It's tempting to make the variable global with the Public
keyword at this point, but in trying to follow the rule to keep the variable's scope as limited as possible, there are a couple of other considerations. First, consider whether that other procedure belongs in the module with the variable. Modules should be designed to contain related procedures, and it's possible this “outsider” procedure should be moved. If that's not the case, consider passing the variable to the other procedure as a parameter. If you were to pass a module-level variable to a procedure outside the module, the variable would be available to any procedure in its own module and only the one other procedure it was passed to. The scope of such a variable is starting to grow but is still as limited as you can make it.
Global variables, declared in standard module with the Public
keyword, should be kept to a minimum. However, almost all projects have at least one global variable. The name of your application, your application's version, and a Boolean flag that determines if the current user of the application has special permissions are all good examples of data to store in global variables.
Constants differ from variables in one major respect: A constant's value never changes. The value is assigned to the constant when it's declared, and attempts to change that value in code will result in an error.
Constants are declared with the Const
keyword. The format of a constant declaration is as follows:
[Public | Private] Const constname [As type] = constvalue
Using constants improves the readability of your code. Constants can also aid in error-proofing your code if you use the same value in more than one place. Figure 25.5 shows a procedure, found in modConstants, that uses a constant.
If the procedure in Figure 25.5 did not use a constant for the discount rate, it might contain a line that looks like this:
dFinalPrice = dFullPrice * (1 – 0.15)
Because of the variable names, you might be able to decipher that 0.15
is a discount rate. By using a constant like dDISCOUNT
, its purpose is obvious to anyone reading the code.
The scope and lifetime of a constant are very similar to variables. Constants declared inside a procedure are available only within that procedure. Constants declared with the Private
keyword in a module are available to all the procedures in that module and none of the procedures in other modules. Global constants, declared with the Public
keyword, are available throughout the project. The values of constants never change, so the Static
keyword is not available, and unnecessary, when declaring constants.
It's a good practice to use the same naming convention for constants that you use with variables. Prefixing a constant's name with a g
for public constants and an m
for private constants allows you to know the scope of the constant at the point of use. Also including a prefix identifying the data type of the constant helps keep your constant names unique and prevents errors, such as using a String constant in a mathematical operation.
In Figure 25.5, the constant name is all uppercase except for the prefix. There is no requirement to use uppercase with constants. Constants can be declared with any combination of uppercase and lowercase letters. The rules regarding naming variables and procedures also apply to constants. However, the all-uppercase convention is used by many developers.
dFinalPrice = dFullPrice * (1 – dDISCOUNT)
In the preceding line, it's easy to see what's a variable and what's a constant by using a proper-case naming scheme for variables and an uppercase naming scheme for constants.
Unlike variables, using global constants poses no risk to the maintainability of your code. Constant values never change, so there's no need to track down which procedures use them. Like variables, it's a good practice to put all globally scoped constants in a single module used only for global variables and constants. If you see a module named modGlobals
in a project, it's a good bet the developer is using that convention.
Numbers used in a procedure are sometimes referred to as magic numbers. The term doesn't imply that the numbers have any special powers, but that another developer reading your code may not be able to determine where the number came from. To the other developer—or to you reading your own code months or years later—the numbers seem to appear magically, without explanation. Many developers strive to remove any magic numbers from their code except the numbers 0 and 1 and numbers being assigned to variables. This keeps the code well organized and easy to maintain.
In the following code, a rewrite of the procedure in Figure 25.5, the magic numbers have been removed and replaced with constants:
Sub DiscountedAmount2()
Dim dFullPrice As Double
Dim dFinalPrice As Double
Const dDISCOUNT As Double = 0.15
Const dDISCTHRESHOLD As Double = 5000
Const sPROMPT As String = "The price is "
dFullPrice = 8000
If dFullPrice > dDISCTHRESHOLD Then
dFinalPrice = dFullPrice * (1 - dDISCOUNT)
Else
dFinalPrice = dFullPrice
End If
MsgBox sPROMPT & dFinalPrice
End Sub
If you want to change the message that's displayed, the discount rate, or the discount threshold, you don't have to look through the code to find where those values are used. All the important values used in the procedure can be found in the declarations section's Const
statements. Changing the values in the Const
statements changes them anywhere they're used in the procedure. The line If dFullPrice > dDISCTHRESHOLD Then
is easily understood as a comparison of the full price to a discount threshold. You can get carried away removing magic numbers from your code. The best practice is to use a constant for any number that's used more than once and to read your code as if you're reading it for the first time and deciding if a descriptive comment name is a better choice over a magic number.
An array is a special type of variable. One array variable actually holds multiple pieces of data. Instead of reserving one block of memory, like a variable, an array reserves several blocks of memory. The size of an array can be fixed or dynamic. With dynamic arrays, you can increase or decrease the size in a procedure. The code in this section can be found in the modArrays
module in Chapter25.accdb
.
When you declare a fixed array, you specify the size in the Dim
statement, and that size cannot be changed later. The simplest way to declare a fixed array is by putting the upper bound index in parentheses after the variable name:
Dim aCustomers(10) as Long
In this example, aCustomers
is an array that can hold 11 long integers, perhaps from a CustomerID field. Why 11? By default, the lower bound of arrays declared in this way is zero. That means a value can be stored at aCustomers(0)
, aCustomers(1)
, all the way up to aCustomers(10)
.
Another way to declare a fixed array is to specify both the lower and upper bound indices. It's a good practice to include the lower bound in the declaration statement even if you intend to use the default. Use the To
keyword to specify the lower and upper bound indices of an array:
Dim aCustomers(1 to 10) as Long
Unlike the previous example, this array has only enough room to hold ten long integers. Long integers use 8 bytes of memory, and declaring this array reserves 80 bytes to hold all ten values. The memory is used when the array is declared, so even if you never assign any values to the array, nothing else can access that memory. If you're having performance problems or your application is using a lot of memory, one place you can look is your arrays to make sure they're not larger than you need. However, with modern computers, 80 bytes here and there probably isn't an issue.
Assigning values to an array is just like assigning them to any other variable except that you must specify in which index you want the variable. The following procedure assigns floating point numbers (Doubles) to an array in a loop:
Sub ArrayAssignment()
Dim aCustomers(1 To 5) As Double
aCustomers(1) = 0.2
aCustomers(2) = 24.6
aCustomers(3) = 7.1
aCustomers(4) = 99.9
aCustomers(5) = 14.7
End Sub
Just as with a variable, the array name goes on the left of the equal sign and the value goes on the right. Unlike variables, however, each assignment includes the index of the array that is being assigned the value.
Reading values from an array will look familiar. Like reading values from variables, you simply use the variable name. With arrays, you always must include the index you want to read. The following procedure stores five random numbers in an array, multiplies those numbers by 10, and finally prints the numbers to the Immediate window:
Sub ArrayRandom()
Dim aRandom(1 To 5) As Double
Dim i As Long
For i = 1 To 5
aRandom(i) = Rnd
Next i
For i = 1 To 5
aRandom(i) = aRandom(i) * 10
Next i
For i = 1 To 5
Debug.Print aRandom(i)
Next i
End Sub
Because array indexes increase by one, For…Next
loops are a common way to access all the elements in an array.
The arrays we've looked at so far are known as one-dimensional arrays. One-dimensional arrays are like lists—they have many rows, but only one column. You can also have two-dimensional arrays. Two-dimensional arrays are like tables—they have many rows and columns. Declare a two-dimensional array using a comma to separate the bounds of the first dimension from the bounds of the second dimension:
Dim aContact(1 to 10, 1 to 3) As String
The aContact
array has 30 places to store data. This array might be used to store three pieces of data for ten contacts. Reading and writing to a two-dimensional array requires that you specify the index for both dimensions:
Sub TwoDArray()
Dim aPotus(1 To 2, 1 To 3)
Dim i As Long
aPotus(1, 1) = "George"
aPotus(1, 2) = "Washington"
aPotus(1, 3) = "1789-1797"
aPotus(2, 1) = "John"
aPotus(2, 2) = "Adams"
aPotus(2, 3) = "1797-1801"
For i = 1 To 2
Debug.Print aPotus(i, 1) & Space(1) & aPotus(i, 2) & Space(1) & _
"was President in the years" & Space(1) & aPotus(i, 3)
Next i
End Sub
Dynamic arrays are declared without any indices and can be resized later in the procedure. Other than the lack of index numbers, they're declared in the same way as fixed arrays:
Dim aProductIDs() as Long
With a dynamic array declaration, no memory is allocated until the array is initialized by providing dimensions. You can't assign values to this array until it's initialized. To initialize a dynamic array, use the ReDim
keyword.
ReDim aProductIDs(1 to 100)
Note that the data type is not included in the ReDim
statement. The data type is set when the array is declared and cannot be changed. Use a dynamic array when you don't know the size of array you'll need until run time. In this example, all the open forms' names in a database are put into an array. Since you can't know which forms will be open, declare a dynamic array and resize it when the procedure executes:
Sub FormArray()
Dim aForms() As String
Dim frm As Form
Dim lFrmCnt As Long
Dim i As Long
If Application.Forms.Count > 0 Then
ReDim aForms(1 To Application.Forms.Count)
For Each frm In Application.Forms
lFrmCnt = lFrmCnt + 1
aForms(lFrmCnt) = frm.Name
Next frm
For i = LBound(aForms) To UBound(aForms)
Debug.Print aForms(i) & " is open."
Next i
End If
End Sub
The Forms.Count
property is used to size the dynamic array. Then a For…Each
loop puts each open form's name into a different index in the array. Finally, the procedure loops through the array and prints each form's name to the Immediate window.
If you know the size of the array at design time, it's best to create a fixed array. If you must use a dynamic array, you'll get the best performance by determining the array size you need and issuing a ReDim
statement to resize the array. There are times, however, where you don't know how many elements you'll need until you start filling the array. VBA provides the Preserve
keyword to resize a dynamic array without losing any of the data that's already in the array. Using ReDim
without Preserve
resizes the array, as you've seen, but the array is re-initialized and any existing data is lost.
ReDim Preserve aCustomerIDs(1 to x) As Long
The Preserve
keyword makes a new array of the new size and then copies all the data from the old array to the new one. Even for moderately sized arrays, this can be a performance killer. Use Preserve
only when there is no other option.
VBA provides several useful functions to use with arrays. We don't have room to cover all of them, but we'll cover the most used and most interesting.
VBA provides two functions, LBound
and UBound
, to determine the size of an array. LBound
returns the lower bound, and UBound
returns the upper bound. These functions are most useful when used to loop through all the elements of an array:
For i = LBound(aContacts) To UBound(aContacts)
Debug.Print aContacts(i)
Next i
If aContacts
is declared as Dim aContacts(1 to 5) As String
, the LBound
will return 1
and UBound
will return 5
. The real benefit comes when you revise the code to Dim aContacts(1 to 6) As String
. If you had hardcoded the boundaries in the For…Next
loop, you would've needed to change the upper bound in two places. By using LBound
and UBound
, you only have to make the change in the Dim
statement.
For two-dimensional arrays, LBound
and UBound
require a second argument for the dimension. The following example is a typical method for looping through all the elements of a two-dimensional array:
For i = LBound(aBounds, 1) To UBound(aBounds, 1)
For j = LBound(aBounds, 2) To UBound(aBounds, 2)
Debug.Print aBounds(i, j)
Next j
Next i
The Array
function allows you to create an array by supplying all the values for the array in one statement. The array returned by the Array
function is known as a variant array—a Variant data type holding an array. To return the results of the Array
function to a variable, that variable must be declared as a Variant. The syntax for the Array
function is:
Array(ParamArray ArgList() as Variant)
The ParamArray
keyword indicates that there can be one or more arguments, but the number is not known ahead of time. The arguments of the Array
function are separated by commas, with each argument becoming an element of the array.
Sub ArrayFunction()
Dim vaRates As Variant
Dim i As Long
vaRates = Array(0.05, 0.055, 0.06, 0.065, 0.07)
For i = LBound(vaRates) To UBound(vaRates)
Debug.Print vaRates(i)
Next i
End Sub
In the preceding example, the vaRates
variable is a Variant containing an array with five elements (the five numbers from the Array
function). Since the variable is a Variant, you don't specify the size of the array beforehand. The number of arguments in the Array
function determines the size. Because of this, the lower and upper bounds are determined by VBA. The default lower bound is zero, and the default upper bound is one less than the number of arguments in the Array
function. For vaRates
in the preceding example, the bounds would be 0
to 4
. The lower bound of an array returned by the Array
function is determined by the Option Base
directive, if one exists, at the top of the module.
The Split
function converts text into a Variant array. VBA can't know the size of the array the Split
function will return, so the variable holding the array must be declared as a Variant. The Split
syntax is as follows:
Split(string_expression, [delimiter],[limit],[compare])
The first argument is the string you want to split into an array. The delimiter
argument tells the Split
function on which characters to split the string. The limit
argument determines how large the resulting array is. Once the array reaches the limit defined, Split
stops splitting the string even if more delimiters are present.
Sub TheSplitFunction()
Dim vaWords As Variant
Dim i As Long
vaWords = Split("Now is the time.", Space(1))
For i = LBound(vaWords) To UBound(vaWords)
Debug.Print vaWords(i)
Next i
End Sub
The vaWords
variable will be a variant array containing four elements: Now
, is
, the
, and time.
The delimiter—in this case, a single space—is not included in the elements. The period at the end is included, making the last element time.
(with a period) rather than time
(without a period).
The Join
function is the opposite of Split
. Join
takes an array and returns a string. The syntax for Join
is:
Join(source_array, [delimiter])
The first argument is the one-dimensional array to be converted into a String. The source_array
can be any data type that VBA can convert into a String, even numbers and dates. The delimiter
is the character or characters to be inserted between the elements of the array.
Sub TheJoinFunction()
Dim sResult As String
Dim aWords(1 To 5) As String
aWords(1) = "The"
aWords(2) = "quick"
aWords(3) = "brown"
aWords(4) = "fox"
aWords(5) = "jumped"
sResult = Join(aWords, Space(1))
Debug.Print sResult
End Sub
The sResult
variable will contain the String The quick brown fox jumped
. Each element of the array is concatenated together with the delimiter inserted between them.
The code in a VBA application lives in containers called modules. As you learned in Chapter 24, modules exist behind the forms and reports in an Access application, as well as in stand-alone modules. The modules themselves contain many procedures, variable and constant declarations, and other directives to the VBA engine.
The code within the modules is composed of procedures. There are two main types of procedures in VBA: subroutines or subprocedures (often called subs) and functions.
The general rules for procedures include the following:
bas
or mod
, you don't run the risk of an error occurring from having a procedure and module with the same name.Because of the rules governing procedure scope, you can't have two public procedures both named MyProcedure
, although you can have two private procedures, both named MyProcedure
, or one public procedure named MyProcedure
and one private procedure named MyProcedure
, but not in the same module. The reason it's a bad idea to use the same procedure name for multiple procedures, even when the procedures have different scopes, should be obvious.
The following sections cover some of the specifics regarding VBA procedures. Planning and composing the procedures in your modules is the most time-consuming part of working with VBA, so it's important to understand how procedures fit into the overall scheme of application development.
Subroutines and functions both contain lines of code that you can run. When you run a subroutine or function, you call it. Calling, running, and invoking are all terms meaning to execute (or run) the statements (or lines of code) within the procedure or function. All these terms can be used interchangeably (and they will be, by different developers). No matter how you invoke a VBA procedure—using the Call
keyword, referencing the procedure by its name, or running it from the Immediate window—they all do the same thing, which is to cause lines of code to be processed, run, executed, or whatever you want to call it.
The only real difference between a procedure and a function is that, when it's called, a function returns a value—in other words, it generates a value when it runs, and makes the value available to the code that called it. You can use a Boolean function to return a True
or False
value indicating, for example, where the operation the procedure performed was successful. You can see if a file exists, if a value was greater than another value, or anything you choose. Functions return dates, numbers, or strings; functions can even return complex data types such as recordsets.
A subroutine does not return a value. However, although a function directly returns a value to a variable created as part of the function call, there are other ways for functions and subroutines to exchange data with form controls or declared variables in memory.
You create procedures in one of two places:
VBA procedures are called in a variety of ways and from a variety of places. They can be called from events behind forms and reports, or they can be placed in module objects and called simply by using their name or by using the Call
statement. Here are some examples:
SomeSubRoutineName
Call SomeSubRoutineName
Somevalue = SomeFunctionName
Only functions return values that may be assigned to variables. Subroutines are simply called, do their work, and end. Although functions return a single value, both subroutines and functions can place values in tables, in form controls, or even in public variables available to any part of your program. You can see several examples of different ways to use subroutines and functions throughout this chapter.
The syntax used for calling subroutines with parameters changes depending on how you call the procedure. For example, when using the Call
keyword to call a subroutine that includes arguments, the arguments must be enclosed in parentheses:
Call SomeSubRoutineName(arg1, arg2)
However, when the same procedure is called without the Call
keyword it requires no parentheses:
SomeSubRoutineName arg1, arg2
Also, using the Call
keyword with a function tells Access your code is not capturing the function's return value:
Call SomeFunctionName
Or, when arguments are required:
Call SomeFunctionName(arg1, arg2)
In this case, the function is treated as if it is a subroutine.
Conceptually, subroutines are easy to understand. A subroutine (usually called a sub and sometimes called a subprocedure) is a set of programming statements that is executed as a unit by the VBA engine. VBA procedures can become complex, so this elementary description of subroutines is quickly overwhelmed by the actual subroutines you'll compose in your Access applications.
Figure 25.6 shows a typical subroutine. Notice the Sub
keyword that begins the routine, followed by the name of the subroutine. The declaration of this particular subroutine includes the Private
keyword, which restricts the availability of this subroutine to the module containing the subroutine.
The subroutine you see in Figure 25.6 contains most of the components you'll see in almost every VBA sub or function:
Sub
keyword identifies this procedure as a subroutine.End
keyword followed by the type of procedure that is ending. In Figure 25.6, the terminator is End Sub
. Functions are terminated with End Function
.At the conclusion of a subroutine, program flow returns to the code or action that originally called the sub. The subroutine shown in Figure 25.6 may be called from a form's Load
event, so control is returned to that event.
As an example of a useful VBA subroutine, the next several paragraphs describe building an event procedure for a control on an Access form. This procedure retrieves a value from one of the cboCustomerID
combo box columns and uses it to find a record. The RowSource
of the cboCustomerID
combo box is a SQL statement that returns the CustomerID and the Company fields. Here's the SQL statement:
SELECT DISTINCT tblCustomers.CustomerID, tblCustomers.Company
FROM tblCustomers
INNER JOIN tblSales
ON tblCustomers.CustomerID = tblSales.CustomerID
ORDER BY tblCustomers.Company;
The tblCustomers
table is inner-joined with the tblSales
table so that only those customers with an invoice are displayed in the combo box. The DISTINCT
keyword is used so that each customer is only returned once.
The objective of this exercise is to learn about procedures, but it also serves to teach you some additional VBA commands. The code is added to the form as the cboCustomerID_AfterUpdate
event.
To create an event procedure in a form, follow these steps:
Private Sub cboCustomerID_AfterUpdate()
and End Sub
in the VBA code editor:
Me.txtCustomerID.SetFocus
If Not IsNull(Me.cboCustomerID.Value) Then
DoCmd.FindRecord Me.cboCustomerID.Value
End If
Me.txtInvoiceDate.SetFocus
The code first moves the focus to the txtCustomerID text box to make that field the current field. The Me.
refers to the current form and substitutes in this example for Forms!frmSales!
.
The first If
statement checks to make sure a Customer ID was selected by making sure the current value of the combo box's bound column—CustomerID
—is not null.
The heart of the procedure is the FindRecord
method of the DoCmd
object. FindRecord
searches through the recordset and returns a record that matches the arguments. There are several arguments to FindRecord
, but we supply only the first, FindWhat
. The FindWhat
argument is what FindRecord
searches for through the records. In this case, it's searching for Me.cboCustomerID.Value
. The other arguments to FindRecord
are optional, and we have accepted the defaults. By setting the focus to Me.txtCustomerID
, we made that field the current field. By default, FindRecord
only searches in the current field, and setting the current field before calling FindRecord
achieves our aims.
The final line of code sets the focus to the txtInvoiceDate
text box. When the user locates a record, it's a good practice to set the focus to a good starting point for navigating through the record. While not required, it provides a good user experience.
Figure 25.7 shows the procedure created in the code editor after entering the procedure described earlier. After you finish entering these statements, press the Save button on the toolbar to save your code before closing the VBA window.
The procedure behind this form runs each time the user selects a different customer in cboCustomerID
. This code shows the first invoice for that customer.
Functions differ from subroutines in that functions return a value. In the examples in this section, you'll see functions that calculate the extended price (quantity × price) for a line item, create a function to calculate the total of all the taxable line items, and then apply the current tax rate to the total.
Although functions can be created behind individual forms or reports, usually they're created in standard modules. This first function will be created in a new module that you'll name modSalesFunctions
. Putting this function in a standard module makes it available to all parts of the applications. To do this, follow these steps:
modSalesFunctions (Code)
in the title bar.Public Function CalcExtendedPrice( _
lQuantity As Long, _
cPrice As Currency, _
dDiscount As Double _
) As Currency
Dim cExtendedPrice As Currency
cExtendedPrice = lQuantity * cPrice
CalcExtendedPrice = cExtendedPrice * (1 - dDiscount)
End Function
The first statement declares the variable cExtendedPrice
as the Currency data type. cExtendedPrice
is used in an intermediate step in the function. The next line of code performs a calculation assigning the product of two variables, lQuantity
and cPrice
, to the cExtendedPrice
variable. You might notice that the lQuantity
and cPrice
variables are not declared within the function; these variables are explained in the next section, “Handling parameters.”
Finally, the last line of code performs one more calculation to apply any discount to cExtendedPrice
. The function's name is treated as if it were a variable and is assigned the value of the calculation. This is how a function gets the value that it returns to the calling program.
Now that you've entered the function, you can use it in various ways, as described in the following sections.
Now, the question you should be asking is: Where did the lQuantity
, cPrice
, and dDiscount
variables come from? The answer is simple. They're the parameters passed from another procedure, as you may have already guessed.
Parameters (often called arguments) passed to a procedure are treated like any other variable by the procedure. Parameters have a name and a data type and are used as a way to send information to a procedure. Parameters are often used to get information back from a procedure, as well.
The following table shows the names and data types of the arguments used in the CalcExtendedPric
e
function:
Parameter Name | Data Type |
lQuantity |
Long |
cPrice |
Currency |
dDiscount |
Double |
These parameter names can be anything you want them to be. Think of them as variables you would normally declare. All that's missing is the Dim
statement. They don't have to be the same name as the variables used in the call to the function. Very often, you'll pass the names of fields in a table or controls on a form or variables created in the calling procedure as parameters to a procedure.
The completed CalcExtendedPrice
function is shown in Figure 25.8. Notice how this function's parameters are defined in the function's declaration statement. The parameters are separated by continuation characters (a space followed by an underscore) to make the code easier to read.
Now that you've completed the function, it's time to test it.
Normally, a function call comes from a form or report event or from another procedure, and the call passes information as parameters. The parameters passed to a procedure are often variables or data taken from a form's controls. You can test this function by going to the Immediate window and using hand-entered values as the parameters.
Follow these steps to test the function:
lQuantity
, dPrice
, and dDiscount
parameters, respectively. CalcExtendedPrice
returns 16.625
using those values, as shown in Figure 25.9.
The next task is to use the function to calculate the extended price (price multiplied by quantity) of each item included in a sales invoice. You can add a call to the function from the Amount box on fsubSalesLineItems
. This is a subform embedded on frmSales
. Follow these steps:
This expression passes the values from three controls—txtQuantity
, txtPrice
, and txtDiscountPercent
—in the subform to the CalcExtendedPrice
function in the module and returns the value back to the control source of the txtAmount
control each time the line is recalculated or any of the parameters change. The references to txtQuantity
, txtPrice
, and txtDiscountPercent
are enclosed in calls to the Nz
function, which converts null values to zero. This is one way to avoid Invalid use of null
errors that would otherwise occur.
The sales form (frmSales
) enforces a business rule that the extended price is recalculated any time the user changes the quantity, price, or discount on the sales form.
In Figure 25.10, notice that the Control Source
property for txtAmount
simply calls the CalcExtendedPrice
function. The call does not specify the module that contains the function. Because CalcExtendedPrice
was declared with the Public
keyword, Access easily finds it and passes the required arguments to it.
In the Collectible Mini Cars application, whenever you add a line item to a sales invoice, you specify whether the item is taxable. The sales form adds up the extended prices for all the taxable line items to determine the sales tax for the sale. This total can then be multiplied by the tax rate to determine the tax.
The Collectable Mini Cars sales form (frmSales
) includes a Text Box control for the tax amount. You can simply create an expression for the control's value such as:
=fSubSalesLineitems.Form!txtTaxableTotal * txtTaxRate
This expression references txtTaxableTotal
in the subform (fSubSalesLineitems
) and multiplies it by the tax rate (txtTaxRate
) from the main form (frmSales
).
However, although this expression displays the tax amount, the expression entered into the txtTaxAmount
control would make the txtTaxAmount
control read-only because it contains an expression. You wouldn't be able to override the calculated amount if you wanted to. The tax applied to a sale is one of the fields that needs to be changed once in a while for specific business purposes.
Better than using a hard-coded expression is creating a function to calculate a value and then place the value of the calculation in the control. This way, you can simply type over the calculated value if needed.
You can enter the following lines of code to the AfterUpdate
events behind the txtQuantity
, txtPrice
, txtDiscountPercent
, and chkTaxable
controls. This way, each time one of those controls' values is changed, the tax is recalculated after the contact's tax rate is retrieved on the frmSales
form.
txtTaxAmount = _
fSubSalesLineitems.Form!txtTaxableTotal * txtTaxRate
Actually, better would be to place this statement in the AfterUpdate
event of fsubSalesLineitems
. This way, the tax is recalculated each time a value is updated in any record of this form. Because fsubSalesLineitems
is displayed as a datasheet, the AfterUpdate
event fires as soon as the user moves to another line in fsubSalesLineitems
.
Although you can use a simple expression that references controls on forms and subforms, this technique works only behind the form containing the code. Suppose you also need to calculate tax in other forms or in reports. There's a better way than relying on a form.
This is an old developer's expression: “Forms and reports lie; tables never lie.” This means that the controls of a form or report often contain expressions, formats, and VBA code that may make a value seem to be one thing when the table actually contains a completely different value. The table containing the data is where the real values are stored, and it's where calculations and reports should retrieve data from.
You can easily use VBA code to extract data from a table, use the data in a complex calculation, and return the result to a control on a form, on a report, or to another section of code.
Figure 25.11 shows the completed CalcTax
function.
The function is called from the AfterUpdate
event behind the frmSalesLineitems
subform. The CalcTax
function calculates the sum of the taxable line items from the tblSalesItems
table. The SQLstatement
is combined with a bit of ADO code to determine the total. The calculated total amount is then multiplied by the dTaxPercent
parameter to calculate the tax. The tax is set to the cReturn
variable, which is set to CalcTax
(the name of the expression) at the end of the function.
An important feature of this example code is that it combines data extracted from a database table (Quantity
,
, RetailPrice
DiscountPercent
) with data passed as parameters (dTaxPercent
, lInvoiceNum
). All the extraction and calculations are automatically performed by the code, and the user is never aware of how the tax amount is determined.
Another significant feature of Access VBA is the use of named arguments for procedures. Without named arguments, the arguments passed to procedures must appear in the correct left-to-right order. With named arguments, you provide the name of each parameter passed to a subroutine or function, and the subroutine or function uses the argument based on its name rather than on its position in the argument list.
Also, because every parameter passed to a procedure is explicitly named, you can omit an unused parameter without causing an error. Named arguments are a great way to clean up your code while making it much easier to read and understand.
Assume your application includes the function shown here:
Function PrepareOutput(sStr1 As String, sStr2 As String, _
sStr3 As String) As String
PrepareOutput = sStr1 & Space(1) & sStr2 & Space(2) & sStr3
End Function
This function, of course, does nothing more than concatenate sStr1
, sStr2
, and sStr3
and return it to the calling routine. The next example shows how this function may be called from another procedure:
Private Sub cmdForward_Click()
Me.txtOutput.Value = PrepareOutput( _
Me.txtFirstName.Value, _
Me.txtLastName.Value, _
Me.txtHireDate.Value)
End Sub
The arguments required by PrepareOutput()
must be passed in the same order they're listed in the procedure declaration. The results of this function are shown in Figure 25.12. The text in the Function Output text box on this form shows the arguments in the order in which they appear in the text boxes on the left side of this form.
Each argument can be specified by its name as you pass it to functions. Naming arguments makes them position independent.
Examine the code in the following listing to see how named arguments work:
Private Sub cmdBackward_Click()
Me.txtOutput.Value = PrepareOutput( _
sStr2:=Me.txtLastName.Value, _
sStr3:=Me.txtFirstName.Value, _
sStr1:=Me.txtHireDate.Value)
End Sub
The thing to notice in cmdBackward_Click
is that the arguments are not passed to PrepareOutput()
in the order specified by the procedure's argument list. As long as the name used for an argument matches an argument in the PrepareOutputs
argument list, Access VBA correctly uses the arguments in PrepareOutput()
.