| Microsoft Access 2000: Building Applications with Forms and Reports |
CHAPTER 2
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
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.
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.
You'll want to use VBA instead of macros if you want to do any of the following:
See Also For more information about creating a function, see "Creating Your First Function" later in this chapter.
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.
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.
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.
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."
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:
Note Some events automatically trigger other events. For example, when the MouseDown event occurs, the MouseUp and Click events immediately follow.
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."
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 event procedure 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.
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.
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
Access displays the Choose Builder dialog box.
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.
Message = "Hello, World!"
This line of code sets the Message text box to the text string, "Hello, World!"
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
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."
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 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
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).
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:
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.
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.
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).
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.
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.
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.
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.
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.
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:
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
Access displays a new module in the Code window.
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
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.
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.
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.
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.
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 End Function statements.
FirstOfNextMonth = DateSerial(Year(Now), Month(Now) + 1, 1)
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
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.
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:
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
=FirstOfNextMonth()
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."
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.
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.
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
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.
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:
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.
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.
A module can contain:
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.
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.
This section explains the syntax you use to create and call procedures in your application. Procedures can be either Sub procedures or Function procedures:
The syntax for a Sub procedure is:
[Private|Public|Friend] [Static] Sub procedurename [(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 [( )] [As type] [= 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.
The syntax for a Function procedure is:
[Private|Public|Friend] [Static] Function procedurename [(arguments)] [As type]
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:
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
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)
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
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.
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:
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).
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:
To run one or more lines of code repetitively, use the following statements:
To apply one or more lines of code to an object or objects in your application, use the following statements:
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.
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.
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:
RunCommand command
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.
As with any of the other windows in Access, you can get help in the Code window in any of several ways:
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.
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.
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.
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.
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.
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.
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.
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.
Offers a list of peer-to-peer newsgroups for discussion about Access.