Introducing Visual Basic For Applications
CHAPTER 2
Introducing Visual Basic For Applications
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
When you're looking for a level of power and control over your application that goes beyond what you can find in the Microsoft® Access interface, Visual Basic® for Applications (VBA) is the place to find it. This chapter shows you how to use VBA to respond to events on forms and reports, and how to create custom functions. It also introduces you to the fundamentals of the VBA programming language.
Chapter Contents
What is VBA?
Creating Your First Event Procedure
Creating Your First Function
VBA Fundamentals
Getting Help
What Is VBA?
Visual Basic for Applications (VBA) is the programming language for Microsoft Office and its associated applications. You use it for the same reason you use macros—to tie the objects in your application together into a coherent system. The difference is that VBA provides more power and a finer degree of control than you get by using macros alone.
Some Familiar Territory for the Seasoned Programmer
VBA is a modern programming language that strongly resembles most of the popular, structured programming languages. If you're a Pascal or C programmer, you'll find all the program structures you're used to—loops, If...Then...Else statements, Select Case statements, functions, and subroutines—with only superficial differences. With all its improvements from earlier versions of Basic, VBA retains its English-like flavor and ease of use.
When to Use VBA Instead of Macros
With Access, you can accomplish many tasks with macros or through the user interface that require programming in other database systems. So, when do you turn to VBA? It depends on what you want to do.
Why Use VBA?
You'll want to use VBA instead of macros if you want to do any of the following:
Make your application easier to maintain Because macros are separate objects from the forms and reports that use them, an application containing a large number of macros that respond to events on forms and reports can become difficult for you, the application developer, to maintain. In contrast, when you use VBA to respond to events, your code is built into the form or report's definition. If you move a form or report from one database to another, the VBA code built into the form or report moves with it. (Code is a general term for the statements you write in a programming language.)
Create your own functions Access includes many built-in, or intrinsic, functions—such as the IPmt function that calculates an interest payment. You can use these functions to perform calculations without having to create complicated expressions. Using VBA, you can also create your own functions to either perform calculations that exceed the capability of an expression or replace complex expressions you've written in your application.
****See Also **** For more information about creating a function, see "Creating Your First Function" later in this chapter.
Mask error messages When something unexpected happens in your application and Access displays an error message, the message can be quite mysterious to your application's users, especially if they aren't familiar with Access. Using VBA, you can detect the error when it occurs and display your own message, or you can have your application do something else. Applications used by a variety of people almost always require some VBA code for handling errors.
****See Also **** For more information about handling errors in your application, see Chapter 8, "Error Handling and Debugging," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
Create or manipulate objects In most cases, you'll find that it's easiest to create and modify an object in that object's Design view. In some situations, however, you may want to manipulate the definition of an object in code. Using VBA, you can manipulate all the objects in a database, including the database itself. An Access wizard is a good example of an application that creates and modifies objects using code. For example, the Form Wizard is a collection of VBA functions that creates a form according to the specifications supplied by the user.
Perform system-level actions You can use the RunApp action in a macro to run another Microsoft® Windows®-based or MS-DOS®-based application from your Access application, but you can't use a macro to do much else outside Access. Using VBA, you can check to see if a file exists on the system, use Automation or dynamic data exchange (DDE) to communicate with other Windows-based applications such as Microsoft Excel, and call functions in Windows dynamic-link libraries (DLLs).
****See Also **** For more information, see Chapter 4, "Working with Objects and Collections." For more information about using DLLs in your application, see Chapter 10, "The Windows API and Other Dynamic-Link Libraries," and Chapter 11, "Add-Ins, Templates, Wizards, and Libraries," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
Manipulate records one at a time You can use VBA to step through a set of records one record at a time and perform an operation on each record. In contrast, macros work with entire sets of records at once.
Pass arguments to your code An argument is a value that supplies the additional information that some actions require. You set arguments for macro actions in the lower part of the Macro window when you create the macro; you can't change them when the macro is running. With VBA, however, you can pass arguments to your code at the time it runs. You can even use variables for arguments—something you can't do in macros. This gives you a great deal of flexibility in how your code runs.
****Tip **** Although you can have both macros and VBA code in your application, you may find it easier to use VBA exclusively once you get started programming. If you have macros in your application, Access can automatically convert them to event procedures or modules that perform all the equivalent actions in VBA code.
In form or report Design view, use the Convert Macros To Visual Basic command (Tools menu, Macro submenu). For global macros that aren't attached to a specific form or report, use the Save As command (File menu) to save the macro as a module. For more information, type convert macros in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
Why Use Macros?
After reading all the reasons for using VBA, you may wonder if there are any reasons left for using macros. However, macros do have their place in many applications. Macros are an easy way to take care of simple details such as opening and closing forms, showing and hiding toolbars, and running reports. Because you specify options for each action in the lower part of the Macro window, there's little syntax to remember, and developing applications can often be faster than with VBA.
In addition to the ease of use macros provide, creating a macro is the only way to make global key assignments.
****See Also **** For information about assigning keys with an AutoKeys macro, see Chapter 1, "Creating an Application."
How an Event-Driven Application Works
An event is an action recognized by a form, report, or control. Each type of object in Access automatically recognizes a predefined set of events. When you want a form, report, or control to respond to an event in a particular way, you can write a VBA event procedure for that event.
Here's what happens in a typical event-driven application:
A user starts the application and Access automatically opens the startup form specified in the Startup dialog box.
The startup form, or a control on the startup form, receives an event. The event can be caused by the user (for example, a keystroke or mouse click), or by your code (for example, an Open event when your code opens a form).
If there is an event procedure corresponding to that event, it runs.
The application waits for the next event.
****Note **** Some events automatically trigger other events. For example, when the MouseDown event occurs, the MouseUp and Click events immediately follow.
Event-Driven vs. Traditional Programming
In a traditional procedural program, the application rather than an event controls the portions of code that are run. It begins with the first line of code and follows a defined pathway through the application, calling procedures as needed.
In event-driven applications, a user action or system event runs an event procedure. Thus, the order in which your code is run depends on the order in which events occur; the order in which events occur is determined by the user's actions. This is the essence of graphical user interfaces and event-driven programming: The user is in charge, and your code responds accordingly.
Because you can't predict what the user will do, your code must make a few assumptions about "the state of the world" when it runs. It is important that you either test these assumptions before running your code or try to structure your application so that the assumptions are always valid. For example, if your application assumes that a text box has text in it before the user clicks a command button, you can write code to enable the command button only when the Change event for the text box occurs.
****See Also **** For information about events, see Chapter 5, "Responding to Events."
Creating Your First Event Procedure
You write VBA code in units called procedures. A procedure contains a series of VBA statements that perform an operation or calculate a value. An eventprocedure is a procedure that runs in response to an event. This section shows you how to create a simple event procedure that makes a command button and a text box work together on a form. The following illustration provides an example of how this interface may appear to the user.
.jpg)
****Note **** This example assumes that you have control wizards turned off in the form's Design view. To do this, make sure the Control Wizards tool in the toolbox is not pressed in.
Because an event procedure is part of the design of the form or report that runs it, the first step is to create the form and add the controls. In this case, you create a form that isn't based on a table or query and add a text box and a command button. Your code will refer to these controls by name, so it's a good idea to set the Name property of each control on your form to something more descriptive than the default settings that Access gives them. For example, Access names the text box Text0 and the command button Command1. To be more descriptive, you could name the text box Message and the command button OK.
.jpg)
****Tip **** When you name the tables, fields, and other objects in your database, keep in mind that you'll use these names to refer to the objects elsewhere in your application. Although descriptive names for objects with spaces are easier to recognize than more compact names, they can be difficult to use in expressions, SQL statements, and VBA code. Consider using short, consistent names that don't contain spaces and are easy to remember and type—for example, field names such as LastName and Phone.
After you've created your form and its controls and set their properties, you're ready to write your first event procedure in the Code window.
To write the event procedure for the OK command button
In Design view, right-click the object (form, report, section, or control) for which you want to write an event procedure, in this case, the OK command button. On the shortcut menu, click Build Event.
Access displays the Choose Builder dialog box.
In the list box, click Code Builder, and then click OK.
Access opens the Code window and creates a template for the default event procedure of the object you selected, in this case, the Click event procedure. (The default event procedure is the one for which Access thinks you're most likely to add code.) The template for the OK command button's Click event procedure is shown in the following illustration.
.jpg)
Enter the code for the event procedure between the Sub and End Sub statements. For the OK command button's event procedure, enter the following code:
Message = "Hello, World!"
This line of code sets the Message text box to the text string, "Hello, World!"
Save and close the module.
When you save the module, Access sets the command button's OnClick event property to [Event Procedure], as shown in the following illustration.
.jpg)
Now that you've written the event procedure, you're ready to run it. To do this, you make the event happen on the form.
To run the OK_Click event procedure
Click the Form View button on the toolbar to switch to Form view.
Click OK.
The event procedure runs and the text "Hello, World!" appears in the text box.
****See Also **** For more information about events and event procedures, see Chapter 5, "Responding to Events."
Working with a Form or Report Module
The "Hello, World!" example in the previous section shows you how to create a new event procedure by using the Build Event command on an object's shortcut menu. In addition to this method, Access provides a variety of other ways to open a form or report module and create or modify its event procedures. Note that a form or report doesn't have a module by default. A form or report without a module is called a lightweight object, and typically loads and displays faster than a form or report with a module. However, using the Build Event command or any of the following procedures automatically creates a form or report module.
To open a form or report and its module at the same time
In the Database window, select the form or report, and then click the Code button on the toolbar. You can also use ALT+F11 to open the Microsoft Visual Basic Editor.
Access opens the form or report and its module. You can also use the Code button in a form or report's Design view to open its module.
In the previous section, you learned how to open the default event procedure. You can create or open any event procedure directly from the property sheet.
To create or open any event procedure
Open the form or report in Design view.
Display the property sheet by right-clicking the form, report, section, or control, and then clicking Properties on the shortcut menu.
In the property sheet, click the Event tab.
Select the property box for the event procedure you want to open.
Click the Build button to the right of the property box.
If the event property already has an event procedure associated with it, Access opens the Code window and displays the event procedure.
If the event property is empty, Access displays the Choose Builder dialog box. In the list box, click Code Builder, and then click OK. Access opens the Code window and creates a template for the event procedure. If you don't want to see the Choose Builder dialog box, and always want to open the Code window by using an empty event property's Build button, you can select the Always Use Event Procedures check box on the Forms/Reports tab of the Options dialog box (Tools menu).
Writing and Editing Code
Editing the code in a module is much like editing text with any text editor. The blinking vertical line, or insertion point, marks the place on screen where you insert typed or pasted text.
As you begin using the Code window, you can take advantage of the following features designed to help you write VBA code efficiently:
Automatic statement building When you type certain VBA elements, Access automatically tries to assist you in writing code by displaying a drop-down list of appropriate choices for the code element you've typed. For example, if you type an object variable, and follow it with a period to indicate that you intend to enter a method or property, Access automatically displays a list of the methods and properties that apply to the object. (You use methods to perform operations on an object. You use properties to determine or change characteristics of an object.)
.jpg)
To complete the statement you're typing, you can either double-click an item in the list or continue typing your code. If you continue typing code, the list displays the closest match to what you've typed. To enter the selected item in the list at any time, press TAB. To make the list disappear, press ESC.
****Note **** You can press ENTER to enter the selected item in the list. However, pressing ENTER also moves the cursor to the next line, so you'll have to return to the line to enter any arguments or additional information.
To use automatic statement building, select the Auto List Members check box on the Editor tab of the Options dialog box (Tools menu).
When the Auto List Members check box is selected, the statement-building lists appear automatically as you type. However, you may also want to explicitly cause Access to display them, especially for lines of code you typed previously and want to edit. To display the list of methods and properties for an existing object, right-click the existing method or property name (to the right of the period) and then click List Properties/Methods on the shortcut menu.
Some methods, functions, and properties take a constant as an argument. To display a list of available constants when you're entering arguments for a method, right-click in the Code window where the constant setting would be entered and then click List Constants on the shortcut menu.
If you've typed part of a property, method, or constant and want Access to finish typing it for you, click Complete Word on the Edit menu.
Quick Info When you type a procedure or method name (followed by a space or an opening parenthesis), a tip automatically appears underneath the line of code you're writing. The tip gives syntax information about the procedure, such as the arguments you need to type to use it.
.jpg)
To display syntax tips, select the Auto Quick Info check box on the Editor tab of the Options dialog box (Tools menu).
In addition, you can view information about any variable, constant, or procedure in your code by right-clicking the item and then clicking Quick Info or Parameter Info on the shortcut menu.
Automatic syntax checking As you move the insertion point off a line, VBA checks the syntax of that line and displays a message if it finds an error. To enable automatic syntax checking, select the Auto Syntax Check check box on the Editor tab of the Options dialog box (Tools menu).
Drag and drop If you want to move code you've written from one place to another in a module or between windows, you don't need to bother with the Copy and Paste commands. Just select the code you want to move, and then drag it to the new location.
To enable drag-and-drop editing, select the Drag-and-Drop Text Editing check box on the Editor tab of the Options dialog box (Tools menu).
Undo command Access keeps track of the changes you make when editing code. By clicking the Undo button one or more times, you can reverse any changes you've made to a module since you opened it.
****See Also **** For more information about these features, or for a list of keyboard shortcuts in the Code window, type code window in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
****Note **** When several people are using the same database, they use separate versions of the forms, reports, and modules in the database. If one person changes code, the others must close and reopen the database in order to see those changes. More than one person can edit the same form, report, or module at the same time. If you attempt to save a form, report, or module that has already been changed by someone else, Access warns you that the module has changed since you opened the database.
Navigating Between Procedures
When you're editing code in the Code window, you can move between procedures in the module by pressing CTRL+PAGE DOWN and CTRL+PAGE UP. In addition, by selecting objects and their procedures or events in the Object and Procedure boxes, you can go directly to a procedure or create a new procedure.
Object box Displays the name of the selected object. Click the arrow to the right of the Object box to display a list of all objects associated with the form or report, then click an object in the list to display its procedures or events in the Procedure box.
Procedure box When (General) appears in the Object box, the Procedure box displays the name of the current procedure. When an object name appears in the Object box, the Procedure box displays the name of the event for the current event procedure. Click the arrow to the right of the Procedure box to display all the events for an object. Events that have event procedures appear in bold in the list. In the following illustration, for example, the Procedure box displays a list of all the events for the OK command button, and the Click event appears in bold.
.jpg)
Although the Code window normally displays one procedure at a time, you can also view all the procedures in a module at once. To switch between Procedure view and Full Module view, click the Procedure View and Full Module View buttons in the lower-left corner of the Code window, as shown in the following illustration.
.jpg)
Using Bookmarks
When working with a large application, it's easy to lose your place as you move between modules and procedures. To keep track of portions of code that you're working on, you can set a bookmark to mark your place. To set a bookmark on the current line of code, point to Bookmarks on the Edit menu, and then click Toggle Bookmark. A blue square in the left margin of the Code window indicates that your bookmark is set.
.jpg)
To return to bookmarks in a module, point to Bookmarks on the Edit menu, and then click Next Bookmark or Previous Bookmark. To clear all bookmarks in all modules, click Clear All Bookmarks on the Bookmarks submenu.
Creating Your First Function
If you discover that you're repeatedly using the same expression in forms, reports, or queries, you may want to write a custom function that calculates that expression, and then use the function in place of the expression. For example, suppose that you often need to calculate the date of the first day of the next month (perhaps this is the date that payment is due or that shipments go out). You can calculate this date with the following expression.
= DateSerial(Year(Now), Month(Now) + 1, 1)
However, this complicated expression is easy to mistype. Instead of typing this expression, you could substitute a custom Function procedure that performs this calculation. Writing a Function procedure to perform a calculation has several significant advantages over using the equivalent expression. Using a Function procedure, you can:
Be sure that the calculation is performed the same way every time, without the risk of a typing mistake.
Modify the calculation by changing it in only one place (the module in which the function is defined) rather than in every place the calculation is used.
Perform complex operations, such as If...Then logic or looping, which are difficult or impossible to handle in a simple expression.
Handle errors in ways that you define.
Include comments to document complicated expressions.
This section shows you how to create a simple function that calculates the date of the first day of the next month. You'll use this function to set the value of the BillingDate text box on the Orders form in the Orders sample application. You can download the Orders sample application and associated Help files from the Microsoft Developer Network (MSDN) Online Web site.
If you want to use this function in other forms and reports, you'll want to create a standard module to store it in. You create a standard module in the same way you create and open other database objects.
To create a standard module
In the Database window, click Modules under Objects, and then click New.
Access displays a new module in the Code window.
.jpg)
****Note **** When you open a new module, Access automatically includes two Option statements in the Declarations section, as shown in the preceding illustration. These statements tell Access how to sort data when running code and whether to warn you if you don't declare variables. For more information, type option in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
To create a new function
Below the Option Explicit statement (or any empty line in a module), type Function followed by a space and the name you want to give the function. In this case, name your new function FirstOfNextMonth.
****Note **** It's a good idea to give your functions relatively short names that describe their purpose or the value they return. Function names can't contain spaces or punctuation marks. For more information about names in VBA, see "Naming Conventions" later in this chapter.
Press ENTER.
When you press ENTER, Access scans your typing, checks it for obvious errors, formats it according to a consistent set of rules for capitalization and spacing, and displays it again. This occurs every time you enter a new line in the Code window. Access also adds a blank line and an End Function statement. The End Function statement is always the last line in a function.
.jpg)
Note that Access adds a set of parentheses after the name of the function. Use these parentheses to enclose any arguments the function takes, if you decide that the function should take arguments.
Performing Calculations in a VBA Function
You perform calculations in VBA the same way you perform calculations elsewhere in Access—by using an expression. The difference is in the way you specify where the result of the expression goes. When you create an expression for a control on a form or for a field in a query, the result of that expression is assigned to that control or that field.
When you perform a calculation in VBA, however, it isn't obvious where the results should go. You have to explicitly assign a destination to the expression. In the case of a function, you want the result of the calculation to be the value returned by the function, so you assign the calculation to the name of the function.
.jpg)
To make a function return the result of a calculation, add an expression to the function that assigns the calculation to the name of the function. For the FirstOfNextMonth function, you add the following line of code between the Function and EndFunction statements.
FirstOfNextMonth = DateSerial(Year(Now), Month(Now) + 1, 1)
Compiling Your Procedure
Before you can run a procedure you've written, Access must compile it. When it compiles a procedure, Access makes a final check for errors and converts the procedure into executable format. Because Access checks the syntax of each line as you enter it, your procedures compile very quickly.
You don't have to explicitly compile your procedures. If you've written a Function procedure, you can simply use it in an expression. Then, when Access evaluates the expression, it makes sure all the functions in the expression have been compiled, compiling any uncompiled functions. If any of those functions use other uncompiled procedures, Access compiles those as well, and so on, until it has compiled all the code required for it to evaluate the expression. If Access discovers an error at any point during the compilation process, it stops compiling and displays an error message.
Although automatic compiling is convenient, you can encounter error messages when you aren't expecting them. For example, if you write a function and then use it in a form without compiling it first, you may not discover an error in the function until Access attempts to compile it when you try to view data in the form.
To make sure that a procedure has been compiled, you can explicitly compile the code in your database.
To compile all code in the current database
On the Debug menu in the Code window, click Compile.
Access compiles all the procedures in the database. This may take time if you have a large number of procedures or modules. If it encounters an error, Access stops compiling, displays a message, and highlights the line of code that contains the error.
****Tip **** If you click Compile, Access saves all the code in your database in its compiled form. It's a good idea to save modules after you compile them, because this allows Access to run them more quickly when you first open them in the future.
****See Also **** For information about debugging errors in your code, see Chapter 8, "Error Handling and Debugging," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
Using Your Function
If you've followed the steps in this section, you now have a working function that you can use in an expression almost anywhere in Access. You may want to use your new function:
In other VBA procedures that you write.
In the expression that defines a calculated field in a form, report, or query.
In the expression that defines the criteria in a query or the condition in a macro.
The following procedure shows you how to create a calculated text box on the Orders form that shows the billing date of the order. When order takers take a new order, this text box will use the FirstOfNextMonth function to automatically display the first day of the next month as the order's billing date.
To display the result of a function in a calculated text box
Open the Orders form in Design view.
Add an unbound text box to the Orders form, and set its Name property to BillingDate.
Set its ControlSource property to the following expression:
=FirstOfNextMonth().jpg)
Now, when an order taker begins to enter a new order, the BillingDate text box automatically displays the first day of the month that follows the current month, as defined by the computer's system clock.
****Note **** When you use a function in the property sheet, you need to include the parentheses after the function name. If the function has required arguments, you must include them inside the parentheses. For more information, see the following section, "Supplying Arguments to Your Function."
Supplying Arguments to Your Function
Functions often take one or more arguments—values that you supply when you call the function and that the function uses to calculate the value it returns. Many of the functions supplied with Access take arguments. The functions you write can take arguments as well.
For example, the FirstOfNextMonth function currently returns the first day of the month that follows the current month, as defined by the computer's system clock. This works fine when an order taker enters a new order, but it's not what should be displayed in the BillingDate text box for orders that were taken in previous months. Instead, the value in the BillingDate text box should be the first day of the month that follows the value in the OrderDate text box.
You can change the function so that it accepts an argument and then calculates the first day of the month following a date you pass to that argument. You specify the arguments for a function by placing them inside the parentheses that follow the function name.
Function FirstOfNextMonth(dtmAny As Date) As Date
FirstOfNextMonth = DateSerial(Year(dtmAny), Month(dtmAny) + 1, 1)
End Function
In the ControlSource property box of the BillingDate text box, you pass the function the value in the OrderDate control, so that the function always returns the first day of the month following the month that the order was taken.
****Note ****You can dimension the data type of an argument in the function declaration. You can also (and normally should) dimension the data type for the function's return value. In the example above, the function's return value and the argument dtmAny are both dimensioned as the data type Date.
.jpg)
****See Also **** For information about syntax and naming rules for arguments, type function statement in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
Adding Comments to Your Procedure
Whenever you create new procedures or modify existing code, it's a good idea to add comments that describe what the code does. Comments don't change what your code does, but they help you and other programmers understand it and they make your code considerably easier to maintain.
Each line of a comment begins with an apostrophe ( ' ). This symbol tells VBA to ignore any words that follow on that line. You can enter comments on a line by themselves, as shown in the following code, or at the end of a line of code.
Function FirstOfNextMonth (dtmAny As Date) As Date
' This function calculates and returns the date of
' the first day of the month following the date passed by
' the argument.
' Note that this works even if Month(dtmAny) = 12.
FirstOfNextMonth = DateSerial(Year(dtmAny), Month(dtmAny) + 1, 1)
End Function
VBA Fundamentals
In a simple application, you may need to use VBA only to create event procedures and simple functions, as shown in the previous sections. However, as your applications get larger and more sophisticated, you'll want to use the full power of the VBA language. This section lays out the fundamental rules for writing VBA code in Access.
Standard Modules and Class Modules
You store your VBA code in modules in an Access database. Modules provide a way to organize your procedures.
Your database can contain two types of modules:
Standard modules You use standard modules to store code you may want to run from anywhere in the application. You can call public procedures in standard modules from expressions, macros, event procedures, or procedures in other standard modules.
To create a new standard module, you can either, click Modules under Objects in the Database window and then click New, or you can click Module on the Insert menu.
Class modules You use class modules to create your own custom objects. The Sub and Function procedures that you define in a class module become methods of the custom object. The properties you define with the Property Get, PropertyLet, and Property Set statements become properties of the custom object.
To create a new class module, click Class Module on the Insert menu. Saved class modules appear with saved standard modules in the Object list when you click Modules on the Objects bar in the Database window. You can distinguish an open class module from an open standard module by the title bar of the Code window—the title bar for a class module always includes the label Class Module.
Each form and report in your database can contain an associated form module or report module. Form and report modules are also class modules, but you can't save them separate from the form or report that they belong to. The class module that is associated with a form is especially useful because you can use it to create multiple instances of a form.
Most frequently, you'll use a form or report module to contain event procedures associated with the form or report. Each module can also contain other procedures that belong to the form or report. And, as with other class modules, you can use the Property Get, Property Let, and Property Set statements to create custom properties for the form or report.
A form or report module is part of the form or report's design. Thus, if you copy a form or report to another database, its module goes with it; if you delete a form or report, its module is deleted as well. Access creates the form or report module automatically when you first add VBA code to the form or report. All you need to do is write the event procedures and other procedures you want to store in the module.
Note that a form or report doesn't have a module associated with it when it's first created. These lightweight forms and reports typically load and display faster than forms or reports with modules. However, once you add any VBA code to the form or report, the form or report module is automatically created. You can use the HasModule property to specify or determine if a form or report has an associated module.
.jpg)
What's In a Module?
A module can contain:
Declarations These are statements that define variables, constants, user-defined types, and external procedures. The Declarations section of a module is separate from the procedures, and declarations in this section apply to every procedure in the module. You can also define variables and constants within a procedure, in which case they apply only to the procedure they're in.
Event procedures These are Sub procedures that apply to a specific object; they run in response to a user or system event, such as a mouse click. Event procedures are always stored with a form or report in the form or report module.
General procedures These are procedures that aren't directly associated with an object or event. You can include general procedures in a standard module or a class module. General procedures can be either Sub procedures (procedures that don't return a value) or Function procedures (procedures that do return a value).
Event Procedures
When Access recognizes that an event has occurred on a form, report, or control, it automatically runs the event procedure named for the object and event. If you want to run code in response to a particular event, you add code to the event procedure for that event.
****See Also **** For information about the Access event model, see Chapter 5, "Responding to Events." For more information about all events, type events in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
When you create an event procedure (by using the procedures described earlier in this chapter), Access automatically creates a code template for the event and adds it to the form or report module. The name of an event procedure for a form or report is a combination of the word "Form" or "Report," an underscore (_), and the event name. For example, if you want a form to run an event procedure when it's clicked, use the procedure Form_Click.
An event procedure for a control uses the same naming convention. For example, if you want a command button named MyButton to run an event procedure when it's clicked, use the procedure MyButton_Click. If a control name contains characters other than numbers or letters, such as spaces, Access replaces those characters with an underscore (_) in any event procedures for the control.
****Note **** If you want to change the names of your controls, it's a good idea to do so before you start writing event procedures for them. If you change the name of a control after attaching a procedure to it, you also must change the name of the procedure to match the control's new name. Otherwise, VBA can't match the control to the procedure. When a procedure name doesn't match a control name, Access makes it a general procedure. You can find general procedures in the Code window by clicking (General) in the Object box, and then clicking the procedure name in the Procedure box.
General Procedures
Access runs event procedures in response to a particular event on a form, report, or control. A general procedure, in contrast, runs only when you explicitly call it. A function, for example, is a type of general procedure.
Why use general procedures? One reason is to create your own functions to automate tasks you perform frequently. For example, you can create a function and then either create a custom menu command or custom toolbar button that runs the function, or use the function in an expression.
****See Also **** For information about customizing your menus and toolbars, see Chapter 1, "Creating an Application." For information about using functions in expressions, type expressions in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
Another reason to use general procedures is that you may want several different event procedures to perform the same actions. A good programming strategy is to put common code in a separate general procedure and have event procedures call it. This eliminates the need to duplicate code, making the application smaller and easier to maintain.
You can create general procedures either in a class module (which can be a form or report module) or in a standard module. If you want a general procedure that's always available from anywhere in your application, place it in a standard module. If a procedure applies primarily to a specific form or report, place it in the module for that form or report.
Creating and Calling Procedures
This section explains the syntax you use to create and call procedures in your application. Procedures can be either Sub procedures or Function procedures:
Sub procedures perform operations, but they don't return a value and can't be used in expressions. Sub procedures can accept arguments. An event procedure is a Sub procedure that's attached to a form or report. When Access recognizes that an event has occurred on a form, report, or control, it automatically runs the event procedure named for the object and event. For example, you can write an event procedure that sets the focus to a specified control when the user exits another control.
Function procedures return a value, such as the result of a calculation. Because they return values, Function procedures can be used in expressions. Like Sub procedures, Function procedures can accept arguments. For example, you can write a function that calculates the first day of the month that follows a date you pass the function in an argument. Then you can use that function in an expression on a form or report.
Sub Procedures
The syntax for a Sub procedure is:
[Private|Public|Friend]**[Static] Subprocedurename [(arguments)]
statements
End Sub
The statements are the VBA statements that make up the code you want to run each time the procedure is called. The arguments are argument names, separated by commas if there are more than one. Each argument looks like a variable declaration and acts like a variable in the procedure. The syntax for each argument is:
[Optional] [ByVal|ByRef] [ParamArray] variablename [( )] [Astype] [= defaultvalue]
Type can be any of the fundamental data types: Byte, Integer, Long, Single, Double, Currency, Decimal, String, Boolean, Date, Object, or Variant. If you don't provide a type, the argument takes the Variant type and can contain any kind of data. Parentheses after variablename indicate that the argument is an array.
By default, arguments to a procedure are passed by reference, meaning that changing the value of the variable changes it in the calling procedure as well. To pass arguments by value rather than by reference, use the ByVal keyword.
****See Also **** For information about the Optional keyword, see "Using a Variable Number of Arguments" later in this chapter. For information about the Static and Private keywords, type sub statement in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search. For information about passing arguments by value or by reference, see Chapter 7, "Getting the Most Out of Visual Basic for Applications," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
When you call a Sub procedure, you specify the arguments you want the procedure to use. For example, the following Sub procedure makes a beep sound the number of times you specify with the intBeeps argument.
Sub MultiBeep(intBeeps As Integer)
Dim intX As Integer, lngY As Long
For intX = 1 To intBeeps
Beep
For lngY = 1 To 100000 ' Short delay between beeps.
Next lngY
Next intX
End Sub
The following statement calls the MultiBeep Sub procedure by using an intBeeps argument of 3, making a beep sound three times.
MultiBeep 3
You don't enclose arguments in parentheses when you call a Sub procedure, as you do when you declare one, unless you use the Call statement to call the procedure, in which case the parentheses are required.
****Note **** To make your code more readable, you can pass arguments to Sub or Function procedures by name. For example, the following call to the MultiBeep Sub procedure passes the intBeeps argument by name:
MultiBeep intBeeps:=3
When you pass multiple arguments by name, you can include them in any order you want. For more information about passing arguments by name, type named arguments in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
Function Procedures
The syntax for a Function procedure is:
[Private|Public|Friend]**[Static] Functionprocedurename [(arguments)] [Astype]
statements
End Function
****See Also **** For information about the Static and Private keywords, type function statement in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
The arguments for a Function procedure work in exactly the same way as the arguments for a Sub procedure, and have the same syntax. Function procedures differ from Sub procedures in three ways:
You enclose arguments in parentheses both when declaring and when calling a Function procedure.
Function procedures, like variables, have data types that determine the type of the return value.
You return a value by assigning it to the procedurename itself. The value returned by the Function procedure can then be used as part of a larger expression.
For example, you could write a Function procedure that calculates the third side, or hypotenuse, of a right triangle given the other two sides.
Function Hypotenuse (dblA As Double, dblB As Double) As Double
Hypotenuse = Sqr(dblA ^ 2 + dblB ^ 2)
End Function
You call a Function procedure the same way you call any of the built-in functions in VBA. For example:
dblResult = Hypotenuse(dblWidth, dblHeight)
****Tip **** If you aren't interested in the result of a Function procedure, you can call it without including parentheses or assigning it to a variable, as you would a Sub procedure. For example, you can use the following code to call a function called DisplayForm and ignore its return value:
DisplayForm strMessage
Using a Variable Number of Arguments
You can declare optional arguments in a procedure definition with the Optional keyword. An optional argument is one that doesn't have to be passed every time you call the procedure. You must declare optional arguments after any required arguments in the argument list. They can be of any type.
If you include an optional argument in a procedure definition, then you need to consider what happens in the procedure when the argument is not passed. You can initialize an optional argument to a default value when you declare the argument, so that if the optional argument is not included when the procedure is called, the default value is used. If you don't initialize the argument to a default value, Access initializes it as it would initialize a variable of that type. If the argument is a number type, then it is initialized to zero. If it is a string, then it is initialized to a zero-length string ("").
In the following example, if a value is not passed for the optional argument, this argument is assigned the default value of 100.
Sub DisplayError(strText As String, Optional intNumber As Integer = 100)
If intNumber = 100 Then
MsgBox strText
Else
MsgBox intNumber & ": " & strText
End If
End Sub
You can call the procedure with either of the following lines of code.
DisplayError "Invalid Entry"
DisplayError "Invalid Entry", 250
****Note **** If an optional argument is of type Variant, then you can use the IsMissing function to determine whether an optional argument was included when the procedure was called. The IsMissing function only works with arguments of type Variant.
To write a procedure that accepts an arbitrary number of arguments, use the ParamArray keyword to pass an array of arguments with the Variant data type. With the ParamArray keyword, you can write functions like Sum, which calculates the sum of an arbitrary number of arguments.
Function Sum(ParamArray varNumbers() As Variant) As Double
Dim dblTotal As Double, var As Variant
For Each var In varNumbers
dblTotal = dblTotal + var
Next var
Sum = dblTotal
End Function
You can call the Sum function with the following line of code.
dblSum = Sum(1, 3, 5, 7, 8)
Calling Procedures from Other Modules
Unless you specify otherwise, general procedures you create are public, meaning that you can call them from anywhere in your application.
****Tip **** If you know you will use a procedure only within its module, you should declare it with the Private keyword to avoid confusion and to speed up compilation of your code. Event procedures are always declared with the Private keyword, because they normally apply only to the form or report in which they are stored. For information about procedures declared with the Private keyword, type private statement in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
When you call a procedure that isn't in the current module, Access searches other modules and runs the first public procedure it finds that has the name you called. If the name of a public procedure isn't unique in the database, you can specify the module it's in when you call the procedure. For example, to run a Sub procedure called DisplayMsg that's stored in a module called Utility, you use the following code:
Utility.DisplayMsg
You can call procedures in a class module from other modules as well. To do this, specify the name of the class module along with the procedure name. For example, to run a Function procedure called AddValues in a class module called Class1 and print the result to the Immediate window, use the following code:
Debug.Print Class1.AddValues
Because form and report modules are also class modules, you call a procedure in a form or report module in the same way. To call a procedure in a form or report module, specify the name of the form or report module along with the procedure name. The name of the form or report module includes the qualification Form_ or Report_ followed by the name of the form or report. For example, to run a Sub procedure called DisplayRecords that's stored with the Orders form, use the following code:
Form_Orders.DisplayRecords
Alternatively, you can call a procedure in a class module or a form or report module by referring to an object variable that points to an instance of either the class or the form or report. For example, the following code opens an instance of the Orders form, and then runs the DisplayRecords procedure.
Dim frmOrders As New Form_Orders ' Declare an object variable.
frmOrders.Visible = True ' Open and display the Orders form.
frmOrders.DisplayRecords ' Call the form's procedure.
.
.
.
Set frmOrders = Nothing ' Close the new instance of the Orders form.
By storing the DisplayRecords procedure in the Orders form module and making it public, you in effect create a custom method of the Orders form.
Sub DisplayRecords
' This procedure can be called from another form
' to cause the Orders form to update itself.
.
.
.
End Sub
Using Variables
Often you store values temporarily when performing calculations with VBA. For example, you may want to calculate several values, compare them, and perform different operations on them, depending on the result of the comparison. You want to retain the values so you can compare them, but because you need to store them only while your code is running, you don't want to store them in a table.
VBA uses variables to store values. Variables are like fields except that they exist within VBA rather than in a table. Like a field, a variable has a name, which you use to refer to the value the variable contains, and a data type, which determines the kind of data the variable can store. Before you use a variable, it's a good idea to declare it with a Dim statement, which tells Access to set aside space for the variable.
For example, in the following procedure, dtmAny, dtmYear, and dtmMonth are variables with the Date data type.
Function DueDate(dtmAny As Date)
Dim dtmYear As Date, dtmMonth As Date
dtmYear = Year(dtmAny)
dtmMonth = Month(dtmAny) + 1
DueDate = DateSerial(dtmYear, dtmMonth, 1)
End Function
****See Also **** For information about declaring and using variables, see Chapter 7, "Getting the Most Out of Visual Basic for Applications," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
Naming Conventions
While you are writing your VBA code, you declare and name many elements (Sub and Function procedures, variables and constants, and so forth). The names of the procedures, variables, and constants you declare in your VBA code must:
Begin with a letter.
Contain only letters, numbers, and the underscore character (_); punctuation characters and spaces aren't allowed.
Be no longer than 255 characters.
Contain no keywords.
A keyword is a word that VBA uses as part of its language. This includes predefined statements (such as If and Loop), functions (such as Len and Abs), methods (such as Close and FindFirst), and operators (such as Or and Mod).
Controlling Execution
VBA has several commands that help you control the execution of your code. For example, you can define groups of statements that may or may not be run, depending on the value of an expression, or you can define groups of statements that VBA runs repeatedly. You can also define groups of statements that each are applied to one or more objects in your application.
To run code conditionally, use the following statements:
If...Then
If...Then...Else
Select Case
To run one or more lines of code repetitively, use the following statements:
Do...Loop
For...Next
While...Wend
To apply one or more lines of code to an object or objects in your application, use the following statements:
For Each...Next
While...Wend
****See Also **** For information about using these statements, type any keyword in the statement in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
Using the DoCmd Object to Perform Macro Actions
Many common actions you perform in an application don't have a corresponding command in the VBA language. To perform the equivalent of a macro action, use methods of the DoCmd object. The syntax for the DoCmd object is:
[Application.]DoCmd.method [arguments]
Replace method with the name of a macro action. How many and what kind of arguments come after method depends on the specific macro action you want to run. You list the arguments in the same order they appear in the Macro window, or you can use named arguments. Specifying the Application object is optional; you can start a line with the DoCmd object.
For example, the Close method, which corresponds to the Close action, takes two arguments that specify the type and name of the database object you want to close. You use commas to separate the arguments when a method takes multiple arguments.
DoCmd.Close acForm, "Add Products"
The first argument, acForm, is an Access intrinsic constant specifying that the object to be closed is a form. Access automatically declares a number of intrinsic constants that you can use to represent a variety of objects, actions, or data types. For example, you often use intrinsic constants with methods of the DoCmd object to specify action arguments that you can enter in the lower part of the Macro window.
****See Also **** For information about intrinsic constants, see Chapter 7, "Getting the Most Out of Visual Basic for Applications," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library. You can also get more information about intrinsic constants by typing intrinsic constants in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
Some methods of the DoCmd object take optional arguments. If you leave these arguments unspecified, Access uses their default values. For example, if you leave both arguments for the Close method unspecified, Access closes the active database object (whatever it may be).
DoCmd.Close
If you omit an optional argument but specify an argument that follows that argument, you must include a comma as a placeholder for the omitted argument. For example, the syntax for the MoveSize method is:
DoCmd.MoveSize [right] [,down] [, width] [**,**height]
The following code uses the default (current) settings for its right and down arguments, while using the specified values for its width and height arguments.
DoCmd.MoveSize , , 5000, 3000
You can use methods of the DoCmd object to perform most macro actions, including the RunMacro action (which runs an existing macro as if it were a procedure). However, seven macro actions have no equivalent methods of the DoCmd object. In most cases, VBA provides equivalent functionality with built-in statements or functions.
| Action | VBA equivalent |
| MsgBox | MsgBox statement or function |
| RunApp | Shell function |
| RunCode | Function procedure call |
| SendKeys | SendKeys statement |
| SetValue | Assignment statement |
| StopAllMacros | Stop or End statements |
| StopMacro | Exit Sub or Exit Function statements |
****See Also **** For information about methods of the DoCmd object, type docmd object in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
Using the RunCommand Method to Perform Menu Commands
Occasionally, you may want your application to perform a command that's on an Access menu or toolbar. To perform a built-in command just as if the user clicked it, use the RunCommand method. The syntax for the RunCommand method is:
RunCommandcommand
Command is a constant that corresponds to the Access command you want to run. For example, the following line of code performs the Options command, causing Access to display the Options dialog box:
RunCommand acCmdOptions
****See Also **** To determine the constant for the command you want to run, type runcommand in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
Getting Help
As with any of the other windows in Access, you can get help in the Code window in any of several ways:
If the Office Assistant is currently displayed, click the Assistant, type your question in the text box, and then click Search. If the Assistant isn't displayed, click the Microsoft Visual Basic Help button or click Microsoft Visual Basic Help (Help menu) to display it. If the Assistant is turned on, it appears. Note that in the Code window, the Assistant provides help on programming topics only.
If the Assistant is turned off when you click the Microsoft Visual Basic Help button or Microsoft Visual Basic Help (Help menu), the Help window appears. To type a question in the Help window, click the Answer Wizard tab. To scroll through a table of contents for Help, click the Contents tab. You can access programming topics through the books near the end of the table of contents. You can even print a group of topics—just select a book and click Print. Help prints all the topics in the book you selected. When you want to search for specific words or phrases, click the Index tab.
While you are editing code in the Code window, position the insertion point on any built-in function, statement, method, or other keyword and press F1.
Use the List Properties/Methods, List Constants, Quick Info, and Parameter Info commands (Edit menu) to get assistance or information about elements of code as you enter them in the Code window.
****See Also **** For more information about these commands, see "Writing and Editing Code" earlier in this chapter.
****Note **** When you installed Access or Microsoft Office Professional, you may not have installed all the available Help files on your system. To add additional components to your Access installation, run the installation program for Access or Office again, click Custom, and select the components you want to add.
Navigating to Related Topics
When you're viewing programming Help topics, you can navigate to related topics by clicking the blue text in the top part of the topic window, such as See Also or Example.
Programming Help topics for VBA, ActiveX® Data Objects (ADO), and Data Access Objects (DAO) are designed to apply to other applications in addition to Access. For this reason, any available information about how keywords are used specifically in Access is contained in a subtopic. To view Access-specific information when a VBA, ADO, or DAO topic is displayed, click Specifics in the top part of the topic window. Also, because you can use language elements supplied by other Microsoft Office applications when programming with VBA in Access, these Microsoft Office language Help topics are structured in the same way.
The Help topic for most keywords contains a code example that demonstrates that function, statement, operator, or method. To see the code example, click Example in the top part of the topic window. For some VBA, ADO, and DAO topics, and for some Microsoft Office language topics, there are two or more code examples—a generic example and examples designed to show how the keyword is used in Access or other Microsoft Office applications.
To copy example code from a Help topic to a Code window, select the code in the topic window, press CTRL+C, open the module you want to include it in, and then press CTRL+V.
****Note **** When there are no related topics available for the currently displayed topic, the underlined text in the top part of the Help window isn't available and is displayed in gray.
Accessing Relevant Information on the Internet
Another valuable source of help is the Internet. On the Internet, you can find interesting and useful information about programming in Access. For example, there are articles that can answer your questions about Access, examples of how others are using Access to address their business needs, and up-to-date information about related technologies, seminars, and events.
In order to find information on the Internet, you need access to the Internet, over either a network or a modem, and a Web browser, such as Microsoft Internet Explorer.
You can access two useful Microsoft Web sites directly from within Access. On the Help menu, click Office on the Web to go to the Microsoft Office Update Web site. In the Code window, click MSDN on the Web (Help menu) to go to the Microsoft Developer Network (MSDN) Online Web site. These Web sites are updated regularly and therefore may contain more up-to-date information than can be found in this book or in Help.
The following list describes these and other helpful Microsoft Web sites.
Microsoft Office 2000/Visual Basic Programmer's Guide
The MSDN online library offers an online version of the Microsoft Office 2000/Visual Basic Programmer's Guide. This book describes how to use the applications and components in Office 2000 to build custom solutions. It is the definitive resource for developers who are creating custom solutions based on Office applications. From the overall design of your solution to the nitty-gritty details of implementing it, this book tells you what you need to know to get things done quickly and productively.
Microsoft Office Developer Web site
Offers articles and technical information, sample applications, programming tips, information about developer events and training, and case studies of real-world applications. For those developing solutions with Microsoft Office applications.
Microsoft Office Update Web Site
Offers product updates, downloads, assistance, and access to newsgroups. Office Update is a continually updated resource for Office 97 and Office 2000 users that focuses on ways to use Office more effectively.
MSDN Online Web Site
Offers articles and technical information, sample applications, programming tips, information about developer events and training, and case studies of real-world applications. For developers who use any Microsoft development tool.
Microsoft Knowledge Base
Offers featured articles and samples, troubleshooting tips, and answers to frequently asked questions about all Microsoft products. You can search for Access-specific information in the Knowledge Base.
Access Newsgroups
Offers a list of peer-to-peer newsgroups for discussion about Access.