Chapter 2: The Basics of Writing and Testing VBA Code (Part 1 of 2)

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

This article is an excerpt from Beginning Access 2007 VBA by Denise M. Gosnell from Wrox (ISBN 978-0-470-04684-5, copyright John Wiley & Sons, Inc. 2007, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Next part: Chapter 2: The Basics of Writing and Testing VBA Code (Part 2 of 2)

Chapter 1 introduced the Microsoft Office Access 2007 VBA programming environment and some general software development concepts. At this point, you are ready to learn the basics of writing and testing code using VBA. This chapter will cover:

• Creating code modules

• Creating and calling procedures

• Using variables to store values

• Controlling the flow of programs

• Debugging and handling errors

This chapter will serve as a building block for the more advanced VBA concepts covered in later chapters. The techniques covered in this chapter and in most of the remaining chapters are used during the development and testing phases of the Systems Development Life Cycle, as defined in Chapter 1.

Contents

• Creating Modules

• Standard Modules versus Class Modules

• Creating and Calling Procedures

• Using Variables and Constants to Store Values

Creating Modules

Modules are best described as containers for holding VBA code. Modules can contain declarations and procedures. VBA code that is placed in one or more modules can be called from an Office Access 2007 application to perform a specified task. I discuss this in greater detail throughout.

Figure 2-1. Sample module displayed in the Visual Basic Editor

 

Note that the figure has four areas labeled by number. Each area will now be mentioned briefly.

1. Object navigation box—Use to select the object to work with

2. Declarations/Procedure navigation box—Use to navigate to the general declarations section or to a particular procedure

3. Declarations—Contains the declarations for the module

4. Procedures—Contains the sub procedures and functions for the module

When you select the Declarations/Procedure navigation box, a list appears that displays the existing procedures of the module, as shown in Figure 2-2.

Figure 2-2. Viewing the list of procedures in the module

 

Now that you have a basic idea of what a module is (container for code), let’s turn to the different types of modules.

Standard Modules versus Class Modules

There are two types of modules: standard modules and class modules. Standard modules are modules that contain procedures that are not associated with any particular object. Class modules are modules that are associated with a particular object. Class modules can be used either for form or report modules or for custom objects accessible throughout your VBA application. For example, form and report modules can contain code that corresponds to a particular form or report. An example of a form module is an event procedure, such as the Click event for a control on a form. Since Access 97, class modules can also be created independently of a form or report to create a definition for a custom object. The current chapter will focus on standard modules and class modules that are associated with a form or report. Custom objects are discussed in detail in Chapter 4.

Tip
In general, it is a good programming practice to write most or all of your business logic and data access for the application in procedures that are contained in standard modules and keep the presentation logic in class modules associated with the form or report. This separation of code concept was briefly introduced in Chapter 1 in the architecture section.

One way to create a module is to select the Modules tab in the database window and click the New button. Another way to create a module is to select Insert ➪ Module. Let’s now create a sample database with a form and a module that can be used for the examples throughout this chapter. This will also illustrate how to create a new module. I will continue with the discussion on modules and procedures after the database has been created.

Try it out: Create a Database to Use with Examples in the Chapter

Let’s jump right in by creating the database that can be used with the examples in this chapter.

1. Create a new database. To do so, click the Office Button (where the File menu used to be) ➪ New ➪ Blank Database. Next, navigate to the path where you want to save the database and specify Ch2CodeExamples.accdb as the File Name field. Click the Create button and the new database will be created. An empty table is displayed on the screen. You can simply close the table view by selecting the X in the right corner, since a new table is not used in this example.

   Note
   The default format for databases in Access is 2007 (ACCDB file format), but if you need the compatibility with Access 2002 or 2003 (MDB file format), you should change it to 2002–2003 format. To change this option, click the Office Button ➪ Access Options, and then on the Personalize Tab, change the Default File Format option from Access 2007 to Access 2002–2003.

2. Add a new form to your database. To do so, select the Create ribbon and then select the Blank Form option.

3. The form opens in Layout View. To switch to Design View, select a blank area of the form, right-click, and then choose Design View from the pop-up menu that appears. Alternatively, you can change the view by selecting View ➪ Design View from the toolbar.

4. Use the controls shown in the toolbar to select and draw two text boxes and one command button onto the form, as shown in Figure 2-3. You can select Cancel to exit the Button Wizard that appears after you draw the button on the form.

   Figure 2-3. Add two textboxes and a button to the form

   

    

5. Select the Design ribbon, and then Property Sheet in the Tools area of the toolbar to display the Properties dialog box for the form. Alternatively, you can press Alt+ Enter to open the Properties dialog box. Change the Name properties of the first text box, second text box, and command button to txtValue1, txtValue2, and cmdRun, respectively. If you select a particular control, such as the first text box, you can set its properties in the Properties dialog box. Figure 2-4 provides an example of how to change the Name property of the selected control, which in this example is txtValue1.

   Figure 2-4. Changing the Name property of the selected control

   

    

6. Change the Name property of the first text box label to lblValue1 and its Caption property to Value 1. Change the Name property of second text box label to lblValue2 and the Caption property to Value 2. Change the cmdRunCaption property to “Run”. After these changes have been made, the finished form should look like that shown in Figure 2-5.

   Figure 2-5. Finished form, with updated control captions

   

    

7. Save the form. Select Office Button ➪ Save, or click the Save icon on the top right, or click X in the upper-right corner of the form itself. When prompted, specify the name for the form as frmTest. Close the form by clicking the X in the upper-right corner of the form itself, if you have not already done so.

   Note
   After frmTest is saved, it appears in the left navigation pane as an “Unrelated Object.”

8. Create a new module. Select the Create ribbon from the toolbar, and then select Macro in the area of the toolbar labeled Other, as shown in Figure 2-6. The drop-down with the Create Module option displays Macro, Module, and Class Module as the options in the list.

   Figure 2-6. Create Module options on the Create ribbon

   

    

9. The Visual Basic Editor Window appears. Use the Properties Window of the Visual Basic Editor to rename the module to modBusinessLogic, as shown in Figure 2-7. If the Properties Window is not already displayed, you can select View ➪ Properties Window to display it.

   Figure 2-7. Properties Window of the Visual Basic Editor

   

    

Figure 2-7 contains an Option Explicit statement in the declarations section of the module. If the module you just created does not have the Option Explicit statement, you should select Tools ➪ Options and select the Require Variable Declaration option in the Code Settings area of the Editor tab and then reinsert the module. The Option Explicit statement then appears. If it still doesn’t appear, type “Option Explicit” after the line “Option Compare Database.” This Option Explicit option requires variables to be defined before they can be used and promotes better coding habits.

How It Works

You just created a new Access 2007 database, added a new form with three controls, and added a new module. No real code is written yet, so the database will not really do anything at this point. But you will be writing code in the new database throughout the rest of this chapter to illustrate various coding concepts.

Creating and Calling Procedures

Now that you have a sample database, I return to a discussion of modules and procedures. You already saw a procedure inside the sample module illustrations provided previously, but let’s now analyze what the procedures are and how they can be used.

Procedures are the building blocks of modules. Each module contains one or more sub procedures or functions, and each sub procedure or function contains one or more VBA code statements.

Sub versus Function Procedure

Procedures can be sub procedures or function procedures. A sub procedure performs an action but does not return a particular result. A function performs an action and returns a particular result. I will provide various examples to illustrate these differences. Sub procedures and functions can be called both from within other procedures and functions and when an event associated with an object is triggered (for example, button_click), as I describe in more detail in Chapter 3.

Create and Call a New Sub Procedure

The syntax for declaring a new sub procedure without parameters is:

Sub ProcedureName
' code for the procedure
End Sub

The syntax for a new sub procedure that contains variables as parameters is:

Sub ProcedureName(Variables)
' code for the procedure
End Sub

Variables are containers for storing data. If a procedure must use the value stored in a particular variable, that variable can be passed as a parameter to the procedure. Let’s create a new procedure to see how this works.

Try It Out: Creating the VerifyCreditAvail Procedure

Next, you will create a VerifyCreditAvail procedure that will compare two numbers to see if enough credit is available for the purchase.

1. Insert the following code in the modBusinessLogic module underneath the declarations section (underneath Option Explicit statement). See Figure 2-2 for an example if you are not sure where to put the code.

   Sub VerifyCreditAvail(curTotalPrice As Currency, curAvailCredit As Currency)
   
   ' inform user if not enough credit for purchase
   
   If curTotalPrice > curAvailCredit Then
     MsgBox "You do not have enough credit available for this purchase."
   End If
   End Sub
   

2. Run the procedure from the Immediate Window. If the Immediate Window is not already open, select View ➪ Immediate Window. Type the command (shown in Figure 2-8) in the Immediate Window and then click Enter.

   Figure 2-8. Immediate Window with command displayed

   

    

   Important

   If you receive an error message that says “the Macros in this project are disabled,” then you must take a few steps to authorize the modules that you just created to run. To do so, close the VBA Editor Window that you are in and return to Access. Locate the area underneath the ribbon toolbar that says “Security Warning – certain content in the database has been disabled.” Click the Options button to the right of this warning. Select the Enable this Content option from the pop-up that appears, and click OK. Return to the VBA Editor and repeat step 2 above to run the procedure. It should run this time.

3. You should see a message box similar to the one shown in Figure 2-9.

   Figure 2-9. Microsoft Office Access 2007 message box

   

    

How It Works

The VerifyCreditAvail sub procedure accepts two variables as parameters, curTotalPrice and curAvailCredit:

Sub VerifyCreditAvail(curTotalPrice As Currency, curAvailCredit As Currency)

The procedure contains an If statement that compares the value of curTotalPrice to curAvailCredit to see if curTotalPrice is larger:

' inform user if not enough credit for purchase
If curTotalPrice > curAvailCredit Then
  MsgBox "You do not have enough credit available for this purchase."
End If

From the Immediate Window, you call the VerifyCreditAvail sub procedure and pass it the values 5.0 and 4.93. The value 5.0 represents curTotalPrice and the value 4.93 represents curAvailCredit. When the preceding If statement was evaluated, Access determined that 5.0 was greater than 4.93, and thus the message box indicating insufficient available credit was displayed.

Call a Sub Procedure from Another Procedure

After a procedure has been created, it can be called from within another procedure if desired. Here’s a simple example to illustrate the syntax:

Sub FirstProcedure
'This code calls a procedure called SecondProcedure
SecondProcedure
End Sub

Now that you know the syntax for calling a procedure from within another procedure, you can write code to call the VerifyCreditAvail procedure you created previously from another procedure.

Try It Out: Calling VerifyCreditAvail from Another Procedure

You can now create a new procedure, called FinalizeOrder, that will call the VerifyCreditAvail procedure.

1. Add the following procedure, called FinalizeOrder, to the modBusinessLogic module underneath VerifyCreditAvail:

   Sub FinalizeOrder()
   
   ' declare variables for storing Price and Credit Avail
   Dim curPrice As Currency
   Dim curCreditAvail As Currency
   
   ' give variables values here for illustration purposes
   curPrice = 4.5
   curCreditAvail = 3.75
   
   ' call VerifyCreditAvail procedure
   VerifyCreditAvail curPrice, curCreditAvail
   End Sub
   

2. Run the FinalizeOrder procedure from the Immediate Window as shown in Figure 2-10.

   Figure 2-10. Immediate Window with command displayed

   

    

3. The same message box you received earlier is again displayed to indicate that available credit is not sufficient.

How It Works

The FinalizeOrder procedure uses two local variables: curPrice and curCreditAvail.

Sub FinalizeOrder()
'declare variables for storing Price and Credit Avail
Dim curPrice As Currency
Dim curCreditAvail As Currency

The variables are then assigned values for illustration purposes.

' give variables values here for illustration purposes
curPrice = 4.5
curCreditAvail = 3.75

Note that, in most cases, you would not hard-code the values in the procedure, but instead you would retrieve them from a database, another variable, or another source. After the variables were assigned values, you call the VerifyCreditAvail procedure, with those variables passed as parameters:

' call VerifyCreditAvail procedure
VerifyCreditAvail curPrice, curCreditAvail

You then run the FinalizeOrder procedure from the Immediate Window to test it. The FinalizeOrder procedure executes the code to declare the variables, assign the variable values, and call the VerifyCreditAvail procedure. Because the value of 4.5 specified for curPrice was greater than the value specified for curCreditAvail, the VerifyCreditAvail procedure caused a message box to be displayed, as in the prior example.

Call a Sub Procedure from an Event

In addition to being called from another procedure, sub procedures can also be called from an event. As discussed in detail in Chapter 4, events occur in response to an action taken by the user or the system. Recall that earlier I discussed the two types of modules: standard modules and class modules. The code examples that you have written so far were written in the standard module called modBusinessLogic. You will now write code in a class module associated with a particular form.

Creating a Procedure for an Object Using the Code Builder

The first time that you write code for a class module of a particular form or report, you must generate the empty procedure using the Code Builder. You may recall that in Chapter 1 there was an example of creating a new event procedure for cmdTest_Click using the Code Builder. We’ll now revisit the Code Builder idea to further investigate how it works and to let you try it out for yourself. The concept of events and their associated objects is described in greater detail in Chapter 3.

Try It Out: Create New Procedure for frmTest Using Code Builder

Let’s now create a new procedure for frmTest using the Code Builder.

1. Return to the database window and open frmTest in design view. To do so, select Object Type ➪ Forms from the Objects list, select frmTest, right-click, and then click the Design View option. Alternatively, select the Home ribbon on the toolbar, and then select View ➪ Design View from the drop-down menu.

2. Select the cmdRun button on the form. You should see the Properties Window. If the Properties Window is not visible, select the Design ribbon, and then Property Sheet in the Tools area of the toolbar and select the cmdRun button again.

3. Select the Event tab from the cmdRun Properties Window. Click the On Click event from the list, as shown in Figure 2-11.

   Figure 2-11. On Click event selected in the Event tab of the Properties Sheet

   

    

4. Click the ellipsis (...) button in the Event tab, and the screen in Figure 2-12 will appear.

   Figure 2-12. Choose Builder window

   

    

5. Select the Code Builder option from the list and then click OK.

6. The Visual Basic Editor will now be displayed with the cursor flashing in a newly created empty procedure called cmdRun_Click (see Figure 2-13).

   Figure 2-13. New procedure in the Visual Basic Editor

   

    

7. Add the following code to the newly created procedure:

   Private Sub cmdRun_Click()
   ' declare variables to store price and avail credit
   Dim curPrice As Currency
   Dim curCreditAvail As Currency
   ' assign variables from current values in text boxes on Form
   curPrice = txtValue1
   curCreditAvail = txtValue2
   ' call VerifyCreditAvail procedure
   VerifyCreditAvail curPrice, curCreditAvail
   End Sub
   

8. After adding the preceding code to the cmdRun_Click procedure, click the Save button in the Visual Basic Editor to make sure that all code so far has been saved. It is a good idea to select the Save option periodically to ensure that you do not lose any of your work.

9. Return to the frmTest form and open it in View mode to run it. To do so, select the Home ribbon on the toolbar, and then click View ➪ Form View from the toolbar menu with the form open. Input a value of 2000 for Value 1 and 1500 for Value 2, as shown in Figure 2-14.

   Figure 2-14. Form with input values for Value 1 and Value 2

   

    

10. You should see the same message box that appeared before, indicating that not enough credit is available (see Figure 2-15).

    Figure 2-15. Microsoft Office Access 2007 message box

    

     

How It Works

To create the empty procedure, you used the Code Builder from the frmTest form. Because you selected the Click event on the Properties Window for the cmdRun button and then chose the Code Builder option, Access automatically generated an empty event procedure called cmdRun_Click. This event procedure will be called anytime the cmdRun button is clicked when the frmTest form is running.

When the code in cmdRun_Click runs, it first declares some local variables and assigns values to the local variables that the user specified in the text boxes. For example, the value of 2000 specified for the first text box is assigned to the curPrice variable. The value of 1500 for the second text box is assigned to the curCreditAvail variable.

' declare variables to store price and avail credit
Dim curPrice As Currency
Dim curCreditAvail As Currency
' assign variables to current values in text boxes on Form
curPrice = txtValue1
curCreditAvail = txtValue2

The VerifyCreditAvail procedure is then called using the local variables as parameters.

'call VerifyCreditAvail procedure
VerifyCreditAvail curPrice, curCreditAvail

When the VerifyCreditAvail procedure executed, it evaluated the available credit and displayed the same error message that you saw earlier because the value that you input for curPrice was greater than the value for curCreditAvail.

Creating Additional Procedures for an Object Using the Code Window

After the first event procedure is created for a form or report, an associated form or report module is automatically created. You can then create additional procedures for that particular form easily from within the code window itself. For example, after you added the cmdRun_Click event, the Form_frmTest object appears in the Project window below. When you select Form_frmTest in the Project window and then select the Object navigation drop-down list, all the objects associated with frmTest are displayed (see Figure 2-16).

Figure 2-16. Objects associated with the selected form

 

In the preceding example, txtValue1 is selected. This is the name of the first text box on the frmTest form. After you select the txtValue1 value from the Object navigation list, the values in the declarations/procedures navigation list are then updated to correspond with those available for the txtValue1. Figure 2-17 displays some of these available procedures that can be created for the txtValue1 text box.

Figure 2-17. Procedures that can be created for txtValue1

 

When you select any of the procedures in the list, such as BeforeUpdate, a corresponding empty procedure will automatically be created with the cursor flashing, ready for you to input your code (see Figure 2-18).

Figure 2-18. Empty BeforeUpdate procedure for txtValue1

 

Writing code for object events will be explored in detail in Chapter 3, but at this point you should just know what a procedure is and that it can be called from various places, such as from an event or another procedure.

Create and Call a New Function Procedure

So far, all the examples you have reviewed have dealt with creating and calling sub procedures. Earlier, you learned that a function procedure is used to perform a particular action and also return a particular result. Let’s look at that in more detail now.

The syntax for declaring a new function procedure without parameters is:

Function ProcedureName() As DataType
' code for the function
End Function

The syntax for a new sub procedure that contains variables as parameters is:

Function ProcedureName(Variables) As DataType
' code for the procedure
End Function

Note that in both the preceding cases, the Function keyword is used instead of the Sub keyword in two places. Also note that a data type is required as part of the function declaration. The data type should be specified to correspond to the type of the value you want the function to output.

Now you can create a new function to illustrate these procedures.

Try It Out: Creating the CalculateSalesTax Function

The CalculateSalesTax function that you create next accepts a price and a tax rate as parameters and calculates the amount of tax.

1. In the modBusinessLogic standard module, add the following function, called CalculateSalesTax, after the FinalizeOrder sub procedure.

   Function CalculateSalesTax(curPrice As Currency, dblTaxRate As Double) As Currency
   
   ' declare variable for storing calculated tax
   Dim curTaxAmt As Currency
   
   ' calculate amt of tax based on price and rate
   curTaxAmt = curPrice * dblTaxRate
   
   ' return the calculated amt
   CalculateSalesTax = curTaxAmt
   End Function
   

2. After the function has been added, the Visual Basic Editor Window should look something like Figure 2-19.

   Figure 2-19. Added function in the Visual Basic Editor window

   

    

3. Run the new function from the Immediate Window using the syntax shown in Figure 2-20 and then press ENTER. You can select View ➪ Immediate Window if it is not already displayed.

   Figure 2-20. Immediate Window with command syntax displayed

   

    

4. After you press ENTER, the 0.25 value is displayed in the Immediate Window.

How It Works

The CalculateSalesTax function has a declaration containing two variables as parameters. It uses Currency as the return data type.

Function CalculateSalesTax(curPrice As Currency, dblTaxRate As Double) As Currency
' declare variable for storing calculated tax
Dim curTaxAmt As Currency

The amount of tax is calculated by multiplying the curPrice value by the dblTaxRate value.

' calculate amt of tax based on price and rate
curTaxAmt = curPrice * dblTaxRate

Because the procedure is a function procedure, a particular value should be returned. In this case, it is desirable to return the amount of sales tax that was calculated. The name of the function is assigned to the curTaxAmt value. This, in effect, returns the curTaxAmt value to the calling procedure for further use and processing.

' return the calculated amt
CalculateSalesTax = curTaxAmt

When the function procedure was tested using the Immediate Window, the preceding code was executed using the values 5.0 and .05 that you specified as the parameters. The value of 5.0 was assigned to the curPrice variable, and the value of .05 was assigned to the dblTaxRate variable. The result of 0.25 was then displayed as the result.

Did you happen to notice that this time you had to use a question mark and put the parameters in parentheses? The prior examples did not use the question mark and parenthesis when calling sub procedures from the Immediate Window. As you just learned, the syntax differs slightly depending on whether you want to call a sub procedure or function from the Immediate Window.

Call a Function from Another Procedure

Just like sub procedures, functions can be called from other procedures. The procedures that you follow are basically the same as those described in the prior example that illustrates calling a sub procedure from another sub procedure. The main difference is that when you call a function from another function or sub procedure, you typically assign the return value of the function to a local variable and then use it appropriately. For example, to call the CalculateSalesTax function from within another function or sub procedure, you could use the following code:

Dim curSalesTax As Currency
' call the CalculateSalesTax function and assign the result to the local variable
curSalesTax = CalculateSalesTax(100,.06)

Calling a Function from an Event

Again, just as with sub procedures, you can call a function from a class module associated with a form or report. The procedures are basically the same as those described in the prior example that illustrates calling a sub procedure from an event. A more detailed explanation of events can be found in Chapter 3.

Scope and Lifetime of Sub Procedures and Functions

Sub procedures and functions have a particular life cycle that determines how long they will live. They also have characteristics that determine from where they can be called. This is referred to as scope. How the procedure is declared determines how long the procedure will live and from where the procedure can be called.

First, let’s look at the procedure life cycle. Traditionally, a procedure executes and then everything it contained—such as the values in the variables—are released from memory. In circumstances where you must preserve all the values within a procedure after it finishes executing, you can declare the procedure with the Static keyword.

Using the Static keyword in the declaration will basically keep the procedure in memory from the last time the procedure ran, including all associated values of the variables. As you may be aware, there are other ways to preserve particular values after a procedure ends. Examples include using public variables or storing certain values in a database for later retrieval.

Let’s now look at how to determine and specify procedure scope from where a procedure is called. Event procedures are private by default, but all other procedures and functions are public by default. In the examples used so far in this chapter, all the procedures were standard public declarations. The sub and function declaration syntax did not always use the word “Public” per se, but those declarations were indeed private declarations. When a procedure is declared as private, it can only be called from within the same object. The Public keyword can be used to make modules available from outside the same object.

Using Built-In Functions

You have looked at some examples of creating your own functions. You should also know that VBA has an extensive list of built-in functions that you can use instead of writing the code yourself for certain common actions. One example of a built-in function that you have already used is the MsgBox function. You can obtain a complete list of available functions in the VBA Help documentation, as in the help topic shown in Figure 2-21.

Tip
You can also view a list of available functions using the Expression Builder. Consult the VBA Help documentation to learn how to access the Expression Builder.

Figure 2-21. Help window in Access

Macros versus Procedures

Now that you are familiar with procedures and functions, it is worth mentioning that you can use macros instead of procedures for some very basic tasks. Procedures provide you with much greater functionality and flexibility than do macros. Thus, in most cases, you should start using procedures instead of macros.

However, with the many improvements in Access 2007 for macros, you may want to start using macros for some of the simpler programming-related tasks, and then use VBA for the more complex tasks. With Access 2007, macros now support simple variables, limited looping, and even some error handling. The applications you build in later chapters will use both macros and VBA code so you can get a better idea of when to use one versus the other. For now, let’s look at a simple example of a macro.

The AutoExec macro is one example of a macro that you can use to control which form loads when the database opens. Let’s create a new AutoExec macro to open the frmTest form that you created earlier any time the Ch2CodeExamples.ACCDB file is opened. To create a new macro, select the Create ribbon on the toolbar, and then select Macro from the group called Macro, as shown in Figure 2-22.

Figure 2-22. Macro option in the Macro group on the Create tab

 

A new empty macro is displayed. To have a form load, select the OpenForm action from the action list, as shown in Figure 2-23.

Figure 2-23. OpenForm option selected on the Action list

 

Next, to specify which form to open, select frmTest from the Form Name field, as shown in Figure 2-24.

Figure 2-24. frmTest specified in the Form Name field

 

Finally, you need to save the macro, such as by selecting the Save icon or by selecting the X in the right corner of the macro window (not of Access 2007 overall or you will close Access). You will be prompted to save the macro. Name the macro AutoExec, as shown in Figure 2-25, so it will run when the database opens.

Figure 2-25. Macro Save As dialog box

 

Whenever an Access database opens, it looks for a macro named AutoExec, and if one is found, the commands it contains are executed automatically.

Now, anytime you open the database, the AutoExec macro will load and open the frmTest form. Other types of macros will be illustrated throughout this book. Give it a try on your database. Close and reopen the database you created in this chapter and see if the form now loads automatically.

Important

As mentioned earlier in the chapter, if a security warning appears, you may need to enable macros and VBA code when you open Access in order for the code to execute. The Trust Center can be used to further customize the security settings in Access. To access the Trust Center, click the Office Button, and then choose Access Options near the bottom. Select the group called Trust Center from the left navigation area.

Using Variables and Constants to Store Values

Now that you have a basic understanding of how to create procedures, turn your attention to learning how to use variables and constants in those procedures. Variables and constants can be used to store values that are used by your Access program. You have already seen a few examples of using variables to store values. This chapter covers variables in a bit more detail because they are so important to writing VBA code.

Types of Variables

Various types of variables can be declared and used in your procedures. The most common variables are probably String, Integer, Long, Currency, and Date, although other variables are also commonly used. The following table illustrates the various data types that are available in VBA and offers an explanation of the type of value each can store.

Table 1. Variable types and what each stores

Data Type	What It Stores
Attachment	File attachment
Boolean	True or false
Byte	Positive integers from 0 to 255
Currency	Positive and negative currency values with four decimal places
Date	Date and time from 1/1/0100 to 12/31/9999
Double	8-byte decimal values
Hyperlink	URL for a hyperlink
Integer	2-byte integers from –32,768 to +32,768
Long	4-byte integers from –2 billion to +2 billion
Object	Access object reference
Single	4-byte decimal values
String (variable length)	From 0 to 2 billion characters
String (fixed length)	From 1 to 65,000 characters
User-defined (with Type)	Same as its associated data type
Variant (numbers)	Numeric value up to range of Double
Variant (characters)	From 0 to 2 billion characters

Note that the VBA data type does not correspond perfectly to the field data types listed in database tables you create in Access. The following table shows examples of how each VBA data type maps to a particular Access field data type. This table is presented to help you evaluate which variable data types to use when reading and using values from particular fields in your Access databases.

Table 2. VBA data types and their corresponding Access data types

Visual Basic Data Type	Corresponding Access Field Data Type
Attachment	Attachment
Boolean	Yes/No
Byte	Number (Byte)
Currency	Currency
Date	Date/Time
Double	Number (Double)
Hyperlink	Hyperlink
Integer	Number (Integer)
Long	Number (Long Integer) or AutoNumber (Long Integer)
Single	Number (Single)
String	Text or Memo

Declaring and Using Variables

The previous examples briefly illustrated that variables can be declared using the Dim statement. Here are some additional examples:

Dim strMessage As String
Dim rsSales As Adodb.Recordset
Dim intCounter As Integer
Dim blnResponse As Boolean

After it is declared, a variable obtains its value (is assigned) by setting the variable equal to a value or to an expression that evaluates to a value. The expression can contain operators (such as = , > , or < ), other variables, constants, key words, formulas, and so on.

Further examples illustrating variable assignments are provided at the end of this chapter.

Declaring and Using Arrays

Arrays are indexed elements that have the same data type. Each array element has a unique index number. Arrays can be static or dynamic. Static arrays have a fixed number of elements, whereas dynamic arrays have the option to grow in size. The lowest index of an array is 0 by default.

Here is an example of how to declare and use an array.

Sub DemoFixedArray()
' declare an array with 5 elements
Dim arstrPictureFile(4) As String
' populate each array element with a value
arstrPictureFile(0) = "Christmas.jpg"
arstrPictureFile(1) = "Thanksgiving.jpg"
arstrPictureFile(2) = "WinterVacation.jpg"
arstrPictureFile(3) = "SummerVacation.jpg"
arstrPictureFile(4) = "Anniversary.jpg"
End Sub

The preceding array illustrates how to declare a fixed-size array. In some cases, you might not know exactly how big the array needs to be. In such cases, you use a dynamic array. A dynamic array is declared without an upper bound index, as shown in the following example.

Sub DemoDynamicArray()
' declare an dynamic array
Dim arstrPictureFile() As String
' once you have determined how big the array needs to be
' then specify a size
' for the arrayReDim arstrPictureFile(50) As String
' populate the first 5 array elements with a value
arstrPictureFile(0) = "Christmas.jpg"
arstrPictureFile(1) = "Thanksgiving.jpg"
arstrPictureFile(2) = "WinterVacation.jpg"
arstrPictureFile(3) = "SummerVacation.jpg"
arstrPictureFile(4) = "Anniversary.jpg"
End Sub

The ReDim statement is used to define the size of the array when it has already been defined with an unknown size but now is known. Any values stored in the array when the ReDim statement are used are lost because the array is reset. The Preserve statement can be used in circumstances where you want to preserve the prior values in the array when using the ReDim statement.

Declaring and Using User-Defined Data Types

User-defined types allow you to create your own data types. User-defined types can contain various pieces of information of the same or varying data types. Here is an example:

Public Type typTripInfo
strTripLocation As String
dtTripStartDate As Date
dtTripEndDate As Date
strPhotoPath As String
End Type

The preceding code can be placed in the General Declarations section of the module. Now, let’s look at a sample procedure that uses the typTripInfo user-defined type.

Sub TestUserDefinedType()
' declare a variable as the user defined type typTripInfo
Dim typRecentTrip As typTripInfo
' assign values to the typRecentTrip user defined type
typRecentTrip.strTripLocation = "Italy"
typRecentTrip.dtTripStartDate = "3-18-04"
typRecentTrip.dtTripEndDate = "3-27-04"
typRecentTrip.strPhotoPath = "c:\trips\Italy"
End Sub

In the preceding procedure, a local variable is declared as the custom data type typTripInfo. Then, values for each of the variables in the user-defined type are specified.

Declaring and Using Object Variables

Object variables are variables that reference objects, such as databases, recordsets, forms, or controls. Object variables allow you to create references with shorter names than the original object and to pass objects as parameters to procedures.

Here is an example of how to declare and use a text box object variable from a form module.

Sub TestObjectVariable()
' declare an object variable
Dim txtPrice As TextBox
' point the object to the txtValue1 text box
Set txtPrice = Me.txtValue1
' set the text value of the text box
txtPrice.Text = "2000"
End Sub

The preceding procedure declares a new variable as a text box object and then points the new variable to the existing txtValue1 text box. A value of 2000 is then assigned to the new variable, which actually ends up physically setting the value in the original txtValue1 text box on the form to which you have pointed the new variable.

Constants

VBA allows you to create your own constants or use built-in constants.

Declaring and Using Constants

A constant is a type of variable that maintains a constant value that does not change. Unlike traditional variables, constants are assigned values when you create them. Constants are declared with the Const statement instead of the Dim statement. Here are some examples:

Const conWelcomeMessage as String = "Welcome to my first VBA application."
Const conRate as Double = 5.5

Constants can help improve the readability of your code. For example, a line of code is much cleaner and more understandable if it uses the constant conRate than if it is hard-coded with a value of 5.5:

dblTotalSales = conRate

Constants can also make your code easier to maintain. For example, because the constant is declared in one place, you do not have to search for every instance where the rate with a hard-coded value of 5.5 is used in the code. Instead, you simply use a constant to store the current rate and modify that one item if the rate later changes.

Using Built-In Constants

VBA has numerous intrinsic constants that can be used in your code to save you from writing the code yourself. For example, the msgbox function has various constants that can be used instead of the particular integers. The constant vbOK represents a value of 1 and is used to test or indicate that an OK button was clicked. The constant vbCancel represents a value of 2 and is used to indicate or test whether a Cancel button was clicked. Please refer to help documentation for more information on the constants available.

Scope and Lifetime of Variables and Constants

Variables and constants have a scope and life cycle and scope similar to procedures. With respect to life cycle, the variables within sub and function procedures generally "live" while the procedure is executing. The Static keyword can be used when necessary to alter this traditional lifetime and preserve the value of the variable after the last procedure that uses it finishes executing.

The scope of a variable determines from where the variable can be seen or used. A variable created locally within a procedure can be seen only by that procedure. A variable that is declared in the General Declarations section can be seen by all procedures in that particular module or by procedures in all modules, depending on whether it is declared as public or private. If the variable is declared with the Public keyword, all procedures in all modules can use and see it. If it is not, the variable is private and only the procedures in the particular module where the declaration is located can see it.

Try It Out: Declaring a Public Variable

Next, you can declare a public variable to illustrate how to make variables visible from procedures in any module.

1. In the modBusinessLogic standard module, go to the General Declarations section (see Figure 2-26).

   Figure 2-26. General Declarations section in the code module

   

    

2. Add a public constant called strTest to the General Declarations section, as shown in the previous figure.

How It Works

Declaring the variable strTest as Public means that it can be used and seen from procedures in any module now. It is important that you make sure to only use the same public variable name once. The Option Explicit statement discussed earlier in this chapter will warn you if you use the same public variable name more than once.

Naming Conventions

You may have noticed that all the examples covered so far prefixed each variable and object declaration with a two- or three-character prefix, such as the variable strTest in the prior figure. These prefixes are used as a naming convention to provide a standardized way of naming objects and variables. Various types of naming conventions are in use today, and one may be just as good as the other.

Following some type of naming convention is a valuable practice. For example, if you name a variable with a prefix that indicates its data type, you do not have to weed through lines and lines of code looking for the place it was declared to see what data type it stores.

The following table illustrates some naming conventions that I like to use for my variables. Other conventions could also be used, as was mentioned previously.

Table 3. Typical naming convention scheme for data types

Prefix	Data Type	Example
Bln	Boolean	blnResult
Byt	Byte	bytResponse
Cur	Currency	curTotalSales
Dt	Date	dtBirth
Dbl	Double	dblGPA
Int	Integer	intCount
Lng	Long	lngTrackingNum
Obj	Object	objControl
Sng	Single	sngResult
str	String	strMessage
typ	User-Defined Type	typExample
var	Variant	varOutput

The following table lists some naming conventions that I like to use for objects. Some objects and variables are not listed in these naming convention tables, but these are the most common to give you the general idea.

Table 4. Typical naming convention scheme for objects

Prefix	Object	Example
Cls	Class Module	clsProject
Frm	Form	frmMain
fsub	SubForm	fsubMainDetail
Mcr	Macro	mcrAutoExec
Mod	Module	modBusinessLogic
Qry	Query	qryCalculateSales
Rpt	Report	rptAnnualSales
Rsub	Subreport	rsubAccountExecutives
Tbl	Table	tblSales

Tip
If you would like additional ideas for naming conventions, see Microsoft Consulting Services Naming Conventions for Visual Basic.

Next part: Chapter 2: The Basics of Writing and Testing VBA Code (Part 2 of 2)