Previous Page TOC Index Next Page Home


Chapter 8

VBA 101: The Basics of VBA

Visual Basic for Applications (VBA) is the development language for Microsoft Access 95. It provides a consistent language for application development within the Microsoft Office suite. The core language, its constructs, and the environment are the same within Microsoft Access for Windows 95, Microsoft Visual Basic, Microsoft Excel, and Microsoft Project. What are different among these environments are the built-in objects specific to each application. For example, Access has a Recordset object, and Excel has a Workbook object. Each application's objects have appropriate properties (attributes) and methods (actions) associated with them. This chapter provides an overview of the VBA language and its constructs.

Simple Access applications can be written using macros. Macros are covered in Chapter 11, "What Are Macros and When Do You Need Them?" Although macros are great for quick prototyping and the most basic of application development, most serious Access development is done using the VBA language. Unlike macros, VBA provides the ability to

The VBA language allows you to use complex logic structures. Whereas macros allow you to perform only simple If...Then...Else logic, the VBA language provides a wealth of logic and looping constructs. These constructs are covered later in this chapter. The VBA language also enables you to declare and work with variables and constants. These variables can be scoped appropriately and passed as parameters to subroutines and functions. As you will see later in this chapter, variables and constants are an integral part of any Access application. These features are not available within macros.

Many important features of the VBA language are not available through macro actions. If you try to develop an application using only macros, you will not be able to take advantage of many of the rich features available in the VBA language. In addition, many of the actions that are available in both macros and modules can be performed much more efficiently using VBA code.

Complex Access applications often require that you are able to loop through a recordset, performing some action on each member of the set. There is no way to accomplish this task using Access macros. Using the VBA language, the process of looping through recordsets is not only possible but very flexible. Using Data Access Objects, you can add, delete, update, and manipulate data. Data Access Objects are covered in Chapter 15, "What Are Data Access Objects and Why Do They Matter?"

When manipulating sets of records, you usually want to ensure that all processing completes successfully before your data is permanently updated. Macros do not allow you to protect your data with transaction processing. Using the BeginTrans, CommitTrans, and Rollback methods, you can ensure that your data is updated only if all parts of a transaction complete successfully. Transaction processing, if implemented properly, can dramatically improve the performance of your application because no data is written to disk until the process completes. Transaction processing and its benefits are covered in Chapter 22, "Transaction Processing."

Using Access macros, you cannot create or modify database objects at runtime. There are many times when you will want to employ this aspect of the VBA language. Using VBA, you can create databases, tables, queries, and other database objects. You can also modify existing objects. The practical applications of this functionality are many. An example is when users are allowed to build queries on the fly. You might want to give the users the ability to design a query using a front-end that you provide, and then store the query so that they can run it again at a later time. The ability to create and modify database objects is covered in Chapter 15.

Access macros don't allow you to implement error handling. If an error occurs while an Access macro is executing in the runtime version of Access, the user is exited out of the application (and therefore the Access runtime). Using error-handling techniques, you can determine exactly what will happen when an error occurs during the execution of your application. Error handling is covered in Chapter 17, "Handling Those Dreaded Runtime Errors."

VBA also makes it easier for the developer to write code libraries of reusable functions and to design and debug complex processes. If you are developing even moderately complex applications, you should be able to create generic function libraries that can be used with all of your Access applications. It is extremely difficult, if not impossible, to accomplish this using macros.

Many powerful functions not available with the VBA language are available as part of Windows itself. The Windows API (Application Programming Interface) refers to the nearly 1,000 Windows functions Microsoft exposes us to as Access programmers. You cannot take advantage of these functions from an Access macro. Using VBA code, you can declare and call upon these functions, improving both the performance and functionality of your applications.

DDE (Dynamic Data Exchange)and OLE automation enable you to communicate between your Access applications and other applications. Although DDE is an older technology than OLE Automation, it is still used to communicate with many applications that do not support OLE Automation. OLE Automation is used to control OLE server applications. Examples of OLE server applications are Excel and Project. Using OLE Automation, you can control these applications' objects. OLE Automation is covered in Chapter 25, "OLE: Communicating with Other Applications."

In summary, although macros can provide a quick fix to a simple problem, their limitations necessitate the use of the VBA language for the development of complex solutions. To make the transition from macros to modules easier, Microsoft has provided a feature that allows you to convert any macro to VBA code (covered in Chapter 11).

Access Code Modules, Form Modules, and Report Modules Explained

VBA code is written in units called subroutines and functions. These subroutines and functions are stored in modules. Modules can be global, or they can be specific to a particular form or report. Global modules are often referred to as Access modules. They are created using the Modules tab of your Database window.

Modules specific to a form or report are generally referred to as Form and Report modules. Their code is often referred to as Code Behind Forms (CBF). The code written behind forms and reports is created and stored within that form or report and is triggered from events occurring within the form or report.

A subroutine (subprocedure) is a routine that responds to an event or performs some action. An Event routine is a special type of subroutine that automatically executes in response to an event such as a mouse click on a command button or the loading of a form. A function is a special type of routine in that it can return a value. A subroutine cannot return a value.

Anatomy of a Module

Whether you are dealing with an Access Module, Report Module, or Form Module, you will see a General Declarations section (see Figure 8.1). As the name implies, this is where you can declare variables and constants that you want to be visible to all the functions and subroutines within the module. These variables are referred to as module-level or Private variables. You can also declare Public variables within the General Declarations section of a module. Public variables can be seen and modified by any function or procedure in any module within the database.


Figure 8.1. The General Declarations section of a module is used to declare Private and Public variables.


Public variables in Access 95 replace Access 2.0's Global variables. Although Global variables are still supported in Access 95, subtle differences exist between Public and Global variables. These differences are discussed later in this chapter.

A module is also made up of user-defined subroutines and functions. Figure 8.2 shows a subroutine called SayHello. Notice that the Object drop-down shown in the figure says General. This is because the subroutine called SayHello is not associated with a particular object.


Figure 8.2. An example of a user-defined subroutine called SayHello.


Access 95 has an environment option called Full Module View. This option, when checked, enables you to see multiple subroutines and functions within a module at one time. Notice the difference between Figure 8.2 and Figure 8.3. In the code window shown in Figure 8.2, only one subroutine is visible at a time. The code window shown in Figure 8.3 illustrates the effects of Full Module View. Notice that multiple subroutines are visible, each separated by a thin horizontal line. To use the Full Module View environmental setting, select Tools|Options and then click the Module tab. Check Full Module View.


Figure 8.3. Full Module View enables you to view multiple routines simultaneously.

Option Explicit

Option Explicit is a statement that can be included in the General Declarations section of a module, form, or report. When Option Explicit is placed in a General Declarations section, all variables within that module, form, or report must be declared before they are used.


In Access 2.0, it was necessary to manually enter the Option Explicit statement into each module, form, and report. You can globally instruct Access 95 to insert the Option Explicit statement in all new modules, forms, and reports. To do this, select Tools|Options. Under the Modules tab, click Require Variable Declaration. It is important that the Option Explicit command be placed in all your modules. Make sure you set this option to True.

In addition to a General Declarations section and user-defined procedures, forms and reports contain event procedures. These procedures are associated with a particular object on a form. Notice in Figure 8.4 that the Object drop-down says cmdHello. This is the name of the object whose event routines you are viewing. The drop-down on the right shows all the events that can be coded for a command button. Each of these events creates a separate event routine. You will have the opportunity to write many event routines as you read through this book.


Figure 8.4. An Event routine for the Click event of the cmdHello command button.

Event Procedures Made Easy

Event procedures are automatically created when you write event code for a control. For example, the routine Private Sub cmdHello_Click is created when you place code in the Click event of the command button named cmdHello (See Figure 8.4).

To get to the event code of an object, follow these steps:

  1. Click on the object in Design view and click on the Properties button on the toolbar, or right-click on the object and select Properties from the context-sensitive menu.

  2. Click on the Event properties tab.

  3. Select the property for which you want to write code (for example, the On Click event).

  4. Select [Event Procedure] from the drop-down list.

  5. Click on the Ellipse button. You are placed in the event code for that object.

Creating Functions and Subroutines

You can also create your own subroutines. These are not tied to a particular object or event. Depending on how and where they are declared, they can be called from anywhere in your application or from a particular Code module, Form module, or Report module.

To create a user-defined routine within a code module, follow these steps:

  1. Click on the Modules tab of the Database window.

  2. Start a new module or select an existing module and click Design.

  3. Select Insert Procedure from the toolbar or select Procedure from the Insert menu. The dialog shown in Figure 8.5 appears.

  4. Type the name of the procedure and select whether you are creating a function, subroutine, or property. Indicate whether you want the procedure to be public to your entire application or private to this module only. Finally, indicate whether you want all the variables within the routine to be static. (Static variables are discussed later in this chapter under "Scope and Lifetime of Variables: Exposing Your Variables as Little as Possible.") Then click OK.


Figure 8.5. The Insert Procedure dialog enables you to specify the name, type, and scope of the procedure you are creating.

To create a user-defined routine within a Form or Report module, follow these steps:

  1. While in Design view of a form or report, view the code behind the form or report by clicking on the Code button on the toolbar or by selecting Code from the View menu.

  2. Select Insert Procedure from the toolbar or select Procedure from the Insert menu. The dialog shown in Figure 8.5 appears.

  3. Type the name of the procedure and select whether you are creating a function, subroutine, or property. Indicate whether you want the procedure to be public to your entire application or private to this module only. Finally, indicate whether you want all the variables within the routine to be static. Then click OK.


Whether you are creating a procedure within an Access module, a Form module, or a Report module, you are now ready to enter the code for your procedure. A great shortcut for creating a procedure is to type Sub Whatever or Function Whatever directly in the code window. A new subroutine or function is instantly created.

Calling Event and User-Defined Procedures

Event procedures are automatically called when an event occurs for an object. For example, when a user clicks on a command button, the Click event code for that command button executes.

The standard method for calling user-defined procedures is to use the Call keyword—for example, Call SayHello.

You can also call the same procedure without using the Call keyword—SayHello.

This violates standards, however, because the Call keyword indicates that you are calling a user-defined routine or event routine. The Call keyword makes the statement self-documenting and easier to read. A user-defined procedure can be called from an event routine or from another user-defined procedure or function.

Scope and Lifetime of Procedures

Procedures can be Public, Private, or Static. Whether a procedure is Public, Private, or Static determines its scope (where it can be called from) and its lifetime (how long it will reside in memory). The placement of a procedure can dramatically affect the functionality and performance of your application.

Public Procedures

A Public procedure can be called from anywhere in the application. Procedures declared in an Access module are automatically Public. This means that, unless you specify otherwise, procedures that you place in a Code module can be called from anywhere within your application.

You might think that two Public procedures cannot have the same name. Although this was true in earlier versions of Access, it is not true in Access 95. If two Public procedures share a name, the procedure that calls them must explicitly state which of the two routines it is calling. This is illustrated by the following code snippet, contained within a form:

Private Sub cmdSayGoodBye_Click()
   Call basUtils.SayGoodBye
End Sub

The SayGoodBye routine is found in two Access code modules. The prefix basUtils indicates that the routine that you want to execute is contained within the Access code module called basUtils.

Procedures declared in a Form or Report module are automatically Private to the form or report in which they are declared. This means that they can be called only from another routine within that form or report. Procedures in Form and Report modules can be explicitly declared as Public using the Public keyword. This means that they can be called from anywhere within the application. The procedure called cbfIAmPublic, shown in Figure 8.6, is found in the form called frmHello. Although the procedure is found within the form, it can be called from anywhere within the application. The only caveat is that the form containing the procedure must be open. The following cbfIAmPublic procedure can be called from anywhere within the application using the following syntax:

Sub PublicFormProc()
   Call Forms.frmHello.cbfIAmPublic
End Sub


Figure 8.6. A Public Form procedure is visible to any subroutine or function in the database.

Private Procedures

Procedures declared in a Form or Report module are automatically Private. This means that they can be called only from within the form or report where they are declared. If you want a procedure that is declared in an Access module to have the scope of that module, meaning that it can be called only from another routine within the module, you must explicitly declare it as Private (see Figure 8.7).


Figure 8.7. A Private procedure is visible only to subroutines and functions with the basAnother module.

The procedure shown in Figure 8.7, called IAmPrivate, is within the module called basUtils. Because the procedure is Private, it can be called only from other routines in basUtils.

Scope Precedence

Private procedures always take precedence over Public procedures. If a Private procedure has the same name as a Public procedure, the code of the Private procedure is executed if it is called by any routine within the module where it was declared. Naming conflicts do not occur between Public and Private procedures.


Developers often wonder where to place code: in forms and reports or Access modules. There are pros and cons to each method. Placing code in Access modules means that the code can be called easily from anywhere within your application, without loading a specific form or report. Public routines placed in Access Modules can also be called from other databases. For this reason, Access modules provide a great place to put generic routines that you want readily available to you as part of a library.

All Access modules are automatically loaded when your database is opened, which is both good and bad. They are taking up memory whether their code is executed or not. On the other hand, Form and Report modules are loaded into memory only when the form or report is opened. This saves memory and resources, but if a lot of code is placed behind a form or report, the form or report will take a long time to load. Users usually prefer a long application load time to slow execution while they are within the program, so you should place most of your code in Access modules. Another advantage of placing code behind forms and reports is that the form or report is very self-contained and therefore is portable. You can import the form or report into any other database and it still operates as expected. This object-oriented approach means that the form requires nothing from the outside world.

As you can see, there are pros and cons to each methodology. As a general rule, if a routine is specific to a particular form or report, place that routine within the form or report. If it is widely used, place it in a module.

Static Procedures

If a procedure is declared as Static, all the variables declared within the procedure maintain their values between calls to the procedure. This is an alternative to explicitly declaring each variable within the procedure as Static. Here is an example of a Static procedure:

Static Sub IncrementThem()
   Dim intCounter1 As Integer
   Dim intCounter2 As Integer
   Dim intCounter3 As Integer
   intCounter1 = intCounter1 + 1
   intCounter2 = intCounter2 + 1
   intCounter3 = intCounter3 + 1
   MsgBox intCounter1 & " - " & intCounter2 & " - " & intCounter3
End Sub

Ordinarily, each variable in this procedure is reinitialized each time the procedure is run. This means that all 1s appear in the message box each time the procedure is run. Because the procedure is declared as Static, the variables within it retain their values from call to call. Each time the procedure is run, the values within the message box increase. This factor should become much clearer after the discussion of variables later in this chapter.

Naming Conventions for Procedures

The LNC (Leszynski) naming conventions suggest that all form and report procedure names be prefixed with the tag cbf. LNC standards add an optional scoping tag of s for Static procedures, m for Private procedures, and p for Public procedures. LNC standards suggest that you use the scoping tag only if you are creating software that will be widely distributed or released as public domain.

Working with Variables

You need to consider many issues when creating VBA variables. The way you declare a variable determines its scope, lifetime, and more. The following topics will help you to better understand the declaration of variables in VBA.

Declaration of Variables

Of the several ways to declare variables in VBA, three are nonstandard and one is standard. For example, you can simply declare x=10.

With this method of variable declaration, you really are not declaring your variables at all; you are essentially declaring them as you use them. This method is quite dangerous; it lends itself to typos and other problems. You can also type Dim intCounter.

The Dim statement declares the variable. The only problem with this method is that you have not declared the type of the variable to the compiler. It is therefore declared as a variant variable.

Another common mistake is declaring multiple variables on the same line:

Dim intCounter, intAge, intWeight As Integer.

In this scenario, only the last variable is explicitly declared as an integer variable. The other variables are implicitly declared as variants.

The most efficient and bug-proof way to declare your variables is to strong-type them to the compiler and declare only one variable per line of code, as in the following example:

Dim intCounter As Integer
Dim strName As String

As you can see, this type of declaration declares the name of the variable as well as the type of data it can contain. This allows the compiler to catch careless errors such as storing a string into an integer variable. If implemented properly, by selecting the shortest practical data type for each variable, this method can also reduce the resources required to run your programs.


Generally, you should try to eliminate the use of variants whenever possible. Besides requiring a significant amount of storage space, variants are also slow because they must be resolved by the compiler at runtime. Certain situations warrant the use of a variant. These situations include variables that need to contain different types of data at different times, and instances where you must be able to differentiate between an empty variable (one that has not been initialized) and a variable containing a zero or a zero-length string. Also, variant variables are the only type of variable that can contain the special value of Null. Empty and Null values are covered in Chapter 9, "Advanced Visual Basic for Applications Techniques."

VBA Data Types

VBA offers several data types for variables. Table 8.1 shows a list of the available data types, the standard for naming them, the amount of storage space they require, the data they can store, and their default values.

Data Type

Naming Conv.

Example

Storage of Data

Range

Default Value

Byte

byt

bytValue

1 byte

0 to 255

0

Boolean

bln

blnAnswer

2 bytes

True or False

False

Integer

int

intCounter

2 bytes

-32768 to 32767

0

Long Integer

lng

lngAmount

4 bytes

-2147483648 to 2147483647

0

Single

sng

sngAmount

4 bytes

Very large

0

Double

dbl

dblValue

8 bytes

Extremely large

0

Currency

cur

curSalary

8 bytes

Very large

0

Date

dtm

dtmStartDate

8 bytes

1/1/100 to 12/31/9999

Object Reference

obj

objExcel

4 bytes

Any object

Fixed String

str

strName

10 bytes + String

0 to 2 billion

""

Var. String

str

strName

String

1 to 65,400

""

Variant /W Numbers

var

varData

16 bytes

Any numeric to double

Empty

Variant /W Characters

var

varData

22 bytes

Same as var. string

Empty

Type

typ

typEmp

Varies

Based on elements

Scope and Lifetime of Variables: Exposing Your Variables as Little as Possible

You have read about the different types of variables available in VBA. Variables can be declared as Local, Private (Module), or Public in scope. You should always strive to include mostly Local variables in your code because they are shielded from being accidentally modified by other routines. Let's take a closer look at how you can determine the scope and lifetime of variables.

Local Variables

Local variables are available only in the subroutine within which they were declared. Consider this example:

Private Sub cmdOkay_Click
  Dim strAnimal As String
  strAnimal = "Dog"
  Call ChangeAnimal
  Debug.Print strAnimal 'Still Dog
End Sub
Private Sub ChangeAnimal
  strAnimal = "Cat"
End Sub

This code behaves in one of two ways. If Option Explicit is in effect, meaning that all variables must be declared before they are used, this code yields a compiler error. If Option Explicit is not in effect, strAnimal is changed to Cat only within the context of the subroutine ChangeAnimal.

Static Variables: A Special Type of Local Variables

The following examples illustrate the difference between Local and Static variables. Local variables are reinitialized each time the code is called. Each time you run this procedure, the numeral 1 is printed in the Debug (Immediate) window:

Private Sub cmdLocal_Click()
  Dim intCounter As Integer
  intCounter = intCounter + 1
  Debug.Print intCounter
End Sub

Each time this code runs, the Dim statement reinitializes intCounter. This is quite different from the following code, which illustrates the use of a Static variable:

Private Sub cmdStatic_Click()
  Static sintCounter As Integer
  sintCounter = sintCounter + 1
  Debug.Print sintCounter
End Sub

Each time this code executes, the variable called sintCounter is incremented and retained.

Private Variables

So far, this discussion has been limited to variables that have scope within a particular procedure. Private (module-level) variables can be seen by any routine in the module within which they were declared. Private variables are declared by placing a Private statement in the General Declarations section of a Form, Report, or Access module:

[General Declarations]
Option Explicit
Private mintCounter As Integer

The value of a variable declared as Private can be changed by any subroutine or function within that module. For example, the following subroutine changes the value of the Private variable mintCounter to 20. Notice the naming convention of using the letter m to prefix the name of the variable, which makes the variable stand out as Private. You should use Private declarations only for variables that need to be seen by multiple routines. You should strive to make most of your variables Local to make your code modular and more bullet-proof.

Private Sub cmdModule_Click()
  mintCounter =
  Debug.Print mintCounter
End Sub
Public Variables

Public variables can be accessed from anywhere within your application. They are usually limited to things such as log-in IDs, environment settings, and other variables that must be seen by your entire application. Declarations of Public variables can be placed in the General Declarations section of an Access module or of a Form or Report module. The declaration of a Public variable looks like this:

Option Explicit
Public gintCounter As Integer

Notice the prefix g, the proper prefix for a Public variable declared within an Access module. This standard is used because Public variables declared in an Access module are visible not only to the database within which they were declared but also to other databases. The prefix p should be used for Public variables declared in a Form or Report module. This prefix indicates that the variable is Public to the database but is not visible to other databases. The following code, placed in the Click event of the cmdPublic command button, changes the value of the Public variable pintCounter to 50.

Private Sub cmdPublic_Click()
  pintCounter =
  Debug.Print pintCounter
End Sub

Adding Comments to Your Code

Comments are added to Access, Form, or Report modules using an apostrophe. The keyword Rem can also be used, but standards recommend the use of the apostrophe rather than Rem. The apostrophe can be placed at the beginning of the line of code or anywhere within the line of code. Anything following the apostrophe is considered a comment. Comments are color-coded in Access 95. Figure 8.8 shows code containing comments.


Figure 8.8. Code containing comments that clarify what the subroutine is doing.

The Line Continuation Character

Prior to Access 95, no line continuation character existed for VBA code, necessitating a lot of scrolling as well as a bag of tricks used to simulate the continuation of a line of code. Access 95 solves this problem; the line continuation character is an underscore. Use of this character is illustrated in Figure 8.9.


Figure 8.9. The line continuation character is used to improve the readability of a long line of code.

VBA Control Structures and When to Use Them

VBA provides the developer with several different constructs for looping and decision processing. The most commonly used ones are covered here.

If...Then...Else

The If...Then...Else construct evaluates whether a condition is true. In the following example, anything between If and Else occurs if the statement evaluates to true. Any code between Else and End If is executed if the statement is false. The Else is optional.

Private Sub cmdIf_Click()
 If IsNull(Me!txtValue) Then
    MsgBox "You must Enter a Value"
  Else
    MsgBox "You entered " & Me!txtValue
  End If
End Sub

This code tests to see whether the text box called txtValue contains a null. A different message is displayed depending on whether the text box value is null.

One-line If statements are also permitted. A one-line If statement looks like this:

If IsNull(Me!txtvalue) Then MsgBox "You must Enter a Value" End If

This format for an If statement is not recommended because it significantly reduces readability.

Another valid part of an If statement is ElseIf. An ElseIf enables you to evaluate an unlimited number of conditions within one If statement. The following code is an example:

Sub MultipleIfs(intNumber As Integer)
   If intNumber = 1 Then
      MsgBox "You entered a One"
   ElseIf intNumber = 2 Then
      MsgBox "You entered a Two"
   ElseIf intNumber >= 3 And intNumber <= 10 Then
      MsgBox "You entered a Number Between 3 and 10"
   Else
      MsgBox "You Entered Some Other Number"
   End If
End Sub

The conditions within an If statement are evaluated in the order in which they appear. For this reason, it is best to place the most common conditions first within the If statement. When a condition is met, execution continues immediately after End If. If no conditions are met, and there is no Else statement, execution also continues immediately after End If.


If multiple conditions exist, it is almost always preferable to use a Case statement rather than an If statement. The exception to this rule is when you are using the TypeOf keyword to evaluate the type of an object. The TypeOf keyword is covered in Chapter 10, "The Real Scoop on Objects, Properties, and Events."

Immediate If (IIf)

An Immediate If (IIf) is a variation on an If statement. It is a function that returns one of two values depending on whether the condition being tested is true of false. Here's an example:

Function EvalSales(curSales As Currency) As String
   EvalSales = IIf(curSales >= 100000, "Great Job", "Keep Plugging")
End Function

This function evaluates the parameter that is passed to see whether the parameter's value is greater than or equal to $100,000. If the value passed is greater than or equal to $100,000, the string "Great Job" is returned from the function; otherwise, the string "Keep Plugging" is returned.


Both the true and false portions of the IIf are evaluated. This means that if a problem exists with either part of the expression (for example a divide by zero condition exists), an error occurs.

The IIf function is most often used in a calculated control on a form or report. Probably the most common example is an IIf expression that determines whether the value of a control is null. If it is, you can have the expression return a zero or an empty string; otherwise, you can have the expression return the value contained within the control. The following is an example of an expression that evaluates the value of a control on a form:

=IIf(IsNull(Forms!frmOrders!Freight),0,Forms!frmOrders!Freight)

This expression displays either a zero or the value for freight in the control called Freight.

The Conditional If: Conditional Compilation

New to Access 95 is Conditional Compilation. Conditional Compilation enables you to selectively execute blocks of code. This feature is useful in several situations:

Conditional compilation is accomplished by using the #If...Then...#Else directive:

Sub ConditionalIf()
   #If Language = "Spanish" Then
      MsgBox "Hola, Que Tal?"
   #Else
      MsgBox "Hello, How Are You?"
   #End If
End Sub

The compiler constant, in this case Language, can be declared in one of two places: in the General Declarations section of a module or in Tools|Options. A compiler constant declared in the General Declarations section of a module looks like this: #Const Language = "Spanish".

The disadvantage of this constant is that it cannot be declared as Public. It is not possible to create Public compiler constants using the #Const directive. This means that any constants declared in the Declarations section of a module can only be used within that module. The major advantage of declaring this type of compiler constant is that it can contain a string. For example, the compiler constant Language, defined in the previous paragraph, is given the value Spanish.

Public compiler constants can be declared only by selecting Tools|Options. Because they are Public in scope, compiler constants defined in Tools|Options can be referenced from anywhere within your application. The major limitation on compiler directives set up in Tools|Options is that they can contain only integers. For example, you would have to say Spanish = 1.

To set up a compiler directive using Tools|Options, select the Module tab from the Options dialog. The Module tab is shown in Figure 8.10. Add the compiler directive to the Conditional Compilation Arguments text box. You can add more than one directive by separating each one with a colon. As mentioned, compiler directives entered in this way can contain only integers.


Figure 8.10. Adding compiler directives using Tools|Options.

With the compiler directive that appears in the dialog, the code would look like this:

Sub ConditionalIf()
   #If Language = 1 Then
      MsgBox "Hola, Que Tal?"
   #Else
      MsgBox "Hello, How Are You?"
   #End If
End Sub

Notice that ConditionalIf now evaluates the constant Language against the integer of 1.

It is important to understand that using conditional constants is not the same as using regular constants or variables along with the standard If...Then...Else construct. Regular constants or variables are evaluated at runtime, requiring processing time each time that the application is run, conditional constants and conditional If...Then...Else statements control which sections of code are actually compiled. All resolution is completed at compile time. This eliminates the need for unnecessary processing at runtime.

Select Case

Rather than employing multiple If...Then...Else statements, it is often much clearer to use a Case statement.

Private Sub cmdCase_Click()
    Dim intResponse As Integer
    If IsNull(Me!txtValue) Then
        intResponse = 0
    Else
        intResponse = Val(Me!txtValue)
    End If
    Select Case intResponse
      Case 0
        MsgBox "You Must Enter a Number"
      Case 1 To 5
        MsgBox "You Entered a Value Between 1 and 5"
      Case 7, 11,
        MsgBox "You Entered 7, 11, or 21"
      Case Else
        MsgBox "You Entered an Invalid Number"
    End Select
End Sub

This subroutine first uses an If statement to evaluate whether the txtValue control contains a null. If txtValue contains a null, the routine stores a zero into the variable named intResponse; otherwise, the value contained within txtValue is stored into intResponse. The Case statement evaluates intResponse. If the value is 0, a message box is displayed with You Must Enter a Number. If the value is between 1 and 5 inclusive, a message box is displayed saying You Entered a Value Between 1 and 5. If the user enters 7, 11, or 21, an appropriate message is displayed; otherwise, the user gets a message indicating that he or she entered an invalid number.

Looping

Several looping structures are available in VBA. Most of them are discussed here.

Sub DoWhileLoop()
  Dim intCounter As Integer
    intCounter = 1
    Do While intCounter < 5
      MsgBox intCounter
      intCounter = intCounter + 1
    Loop
End Sub

This structure does not ensure that the code within the loop is executed at least once. If intCounter is greater than or equal to 5, the code within the loop is never executed. If you want the code to execute unconditionally at least one time, you need to use the following construct:

Sub DoLoopWhile()
  Dim iCounter As Integer
    iCounter = 5
    Do
        MsgBox iCounter
        iCounter = iCounter + 1
    Loop While iCounter < 5
End Sub

This code executes one time, even though intCounter is set to 5. The Do While...Loop evaluates before the code is executed and therefore does not ensure code execution. The Do...Loop While is evaluated at the end of the loop and is therefore guaranteed execution.

Alternatives to the Do While...Loop and the Do...Loop While are Do Until...Loop and Do...Loop Until. Do Until...Loop works like this:

Sub DoUntilLoop()
   Dim intCounter As Integer
   intCounter = 1
   Do Until intCounter = 5
      MsgBox intCounter
      intCounter = intCounter + 1
    Loop
End Sub

This loop sets intCounter equal to 1. It continues to execute until intCounter becomes equal to 5. The Do...Loop Until construct is another variation:

Sub DoLoopUntil()
   Dim intCounter As Integer
   intCounter = 1
   Do
      MsgBox intCounter
      intCounter = intCounter + 1
   Loop Until intCounter = 5
End Sub

As with the Do...Loop While construct, the Do...Loop Until construct does not evaluate the condition until the end of the loop. The code within the loop is therefore guaranteed to execute at least once.


It is easy to unintentionally cause a loop to execute endlessly. The following code shows an example:

Sub EndlessLoop()
Dim intCounter As Integer
intCounter = 5
Do
Debug.Print intCounter
intCounter = intCounter + 1
Loop Until intCounter = 5
End Sub

This code snippet sets intCounter equal to 5. The code within the loop increments intCounter and then tests to see whether intCounter equals 5. If it does not, the code within the loop executes another time. Because intCounter never becomes equal to 5, the loop executes endlessly. You need to use Ctrl+Break to exit out of the loop. Ctrl+Break does not work in the runtime version of Access.

For...Next

The For...Next construct is used when you have an exact number of iterations you want to perform. It looks like this:

Sub ForNext()
    Dim intCounter As Integer
    For intCounter = 1 To 5
        MsgBox intCounter
    Next intCounter
End Sub

Note that intCounter is self-incrementing. The start value and the stop value can both be variables. A For...Next construct can also be given a step value:

Sub ForNextStep()
    Dim intCounter As Integer
    For intCounter = 1 To 5 Step 2
        MsgBox intCounter
    Next intCounter
End Sub

With...End With

The With...End With statement executes a series of statements on a single object. Here is an example:

Private Sub cmdWithEndWith_Click()
   With Me!txtHello
      .BackColor = 16777088
      .ForeColor = 16711680
      .Value = "Hello World"
      .FontName = "Arial"
   End With
End Sub

This code performs four operations on the command button called txtHello, found on the form on which it is run. The BackColor, ForeColor, Value, and FontName properties of the txtHello text box are all modified by the code.


The With...End With statement provides two major benefits. The first is simply a matter of less typing: You do not need to repeat the object name for each action that you want to perform on the object. The more important benefit involves performance. Because the object is referenced once rather than multiple times, this code runs much more efficiently. The benefits are even more pronounced when the With...End With construct is found within a loop.

For Each...Next

The For Each...Next statement executes a group of statements on each member of an array or collection. The following example illustrates the use of this powerful construct:

Private Sub cmdForEachNext_Click()
   Dim ctl As Control
   For Each ctl In Controls
      ctl.ForeColor = 16711680
   Next ctl
End Sub

This code loops through each control on a form. The ForeColor property of each control on the form is modified. The With...End With construct is often used in conjunction with the For Each...Next construct. Here's an example:

Private Sub cmdForEachWith_Click()
   Dim ctl As Control
   For Each ctl In Controls
      With ctl
         .ForeColor = 16711680
         .FontName = "Arial"
         .FontSize =
      End With
   Next ctl
End Sub

This code loops through each control on the form. Three properties are changed for each control: ForeColor, FontName, and FontSize.


Before you put all this good information to use, remember that no error handling has yet been implemented within the code. If one of the controls on the form in the example does not have a ForeColor, FontName, or FontSize property, the code renders an error. In Chapter 10, you learn how to determine the type of an object before you perform a command on it. Knowing the type of an object before you attempt to modify its properties can help you to prevent errors when dealing with the object.

Passing Parameters and Returning Values: An Introduction

Both subroutines and functions can receive arguments (parameters), but only functions can return values. The following subroutine receives two parameters: txtFirst and txtLast. It then displays a message box with the first character of each of the parameters that was passed.

Private Sub cmdSendNames_Click()
  Call Initials(Me!txtFirstName, Me!txtLastName)
End Sub
Sub Initials(strFirst As String, strLast As String)
  MsgBox "Your Initials Are: " & Left$(strFirst, 1) _
   & Left$(strLast, 1)
End Sub

Notice that the text within the controls txtFirstName and txtLastName from the current form (Me) are passed to the subroutine called Initials. The parameters are received as strFirst and strLast. The first left character of each parameter is displayed in the message box.

The preceding code simply passes values and then operates on those values. This next example uses a function to return a value.

Private Sub cmdNameFunc_Click()
    Dim strInitials As String
    strInitials = ReturnInit(Me!txtFirstName, _
          Me!txtLastName)
    MsgBox "Your initials are: " & strInitials
End Sub
Function ReturnInit(strFName As String, strLName As String) As String
    ReturnInit = Left$(strFName, 1) & Left(strLName, 1)
End Function

Notice that this example calls a function ReturnInit, sending values contained within the two text boxes as parameters to the function. The function sets ReturnInit (the name of the function) equal to the first two characters of the strings. This returns the value back to the calling routine (cmdNameFunc_Click) and sets strInitials equal to the return value.


Notice that the function ReturnInit is set to receive two string parameters. You know this because of the As String keywords that follow each parameter. The function is also set to return a string. You know this because the keyword As String follows the list of the parameters, outside of the parentheses. If you do not explicitly state that the function will return a particular type of data, it will return a variant.

Working with Built-In Functions

Visual Basic for Applications contains a rich and comprehensive function library. Some of the more commonly used functions and examples are listed here. On some rainy day, go through the online Help or the Microsoft Access Language Reference to become familiar with the rest.

Format

The Format function formats expressions in the style specified. The first parameter is the expression you want to format. The second parameter is the type of format you want to apply.

Sub FormatData()
   Debug.Print Format$(50, "Currency")
   'Prints $50.00
   Debug.Print Format$(Now, "Short Date")
   'Prints 2/4/96
   Debug.Print Format$(Now, "DDDD")
   'Displays the word for the day
   Debug.Print Format$(Now, "DDD")
   'Displays 3 - CHAR Day
   Debug.Print Format$(Now, "YYYY")
   'Displays 4 - digit Year
   Debug.Print Format$(Now, "WW")
   'Displays the Week Number
End Sub

Instr

The Instr function returns the position where one string resides within another string.

Sub InstrExample()
  Debug.Print InStr("Alison Balter", "Balter") 'Returns 8
  Debug.Print InStr("Hello", "l") 'Returns 3
End Sub

Left

Left returns the left-most number of characters in a string.

Sub LeftExample()
  Debug.Print Left$("Hello World", 7) 'Prints Hello W
End Sub

Right

Right returns the right-most number of characters in a string.

Sub RightExample()
 Debug.Print Right$("Hello World", 7) 'Prints o World
End Sub

Mid

Mid returns a substring of a specified number of characters in a string. This example starts at the fourth character and returns five characters:

Sub MidExample()
    Debug.Print Mid$("Hello World", 4, 5) 'Prints lo Wor
End Sub

UCase

UCase returns a string that is all uppercase.

Sub UCaseExample()
    Debug.Print UCase$("Hello World") 'Prints HELLO WORLD
End Sub

DatePart

DatePart returns the specified part of a date.

Sub DatePartExample()
    Debug.Print DatePart("YYYY", Now)
    'Prints the Year
    Debug.Print DatePart("M", Now)
    'Prints the Month Number
    Debug.Print DatePart("Q", Now)
    'Prints the Quarter Number
    Debug.Print DatePart("Y", Now)
    'Prints the Day of the Year
    Debug.Print DatePart("WW", Now)
    'Prints the Week of the Year
End Sub

DateDiff

DateDiff returns the interval of time between two dates.

Sub DateDiffExample()
  Debug.Print DateDiff("d", Now, "12/31/99")
  'Days until 12/31/99
  Debug.Print DateDiff("m", Now, "12/31/99")
  'Months until 12/31/99
  Debug.Print DateDiff("yyyy", Now, "12/31/99")
  'Years until 12/31/99
  Debug.Print DateDiff("q", Now, "12/31/99")
  'Quarters until 12/31/99
End Sub

DateAdd

DateAdd returns the result of adding or subtracting a specified period of time to a date.

Sub DateAddExample()
    Debug.Print DateAdd("d", 3, Now)
    'Today plus 3 days
    Debug.Print DateAdd("m", 3, Now)
    'Today plus 3 months
    Debug.Print DateAdd("yyyy", 3, Now)
    'Today plus 3 years
    Debug.Print DateAdd("q", 3, Now)
    'Today plus 3 quarters
    Debug.Print DateAdd("ww", 3, Now)
    'Today plus 3 weeks
End Sub

Functions Made Easy with the Object Browser

The Object Browser enables you to easily browse Access' functions and add a function to your code. It even adds the function's parameters for you. The following steps enable you to browse the available functions, select the function you want, and insert it into your code:

  1. Select Object Browser from the toolbar or press F2. The Object Browser window appears (see Figure 8.11).

  2. Use the Libraries/Databases drop-down to select the library or database whose properties and methods you want to view.

  3. The Object Browser window is divided into two parts. Select the module or class from the left list box.

  4. Select a property or method associated with that module or class from the right list box. In Figure 8.11, the basAnother module is selected from the list box on the left, and the EvalSales function is selected from the list box on the right. Notice that the function and its parameters appear below the list boxes.

  5. Click Paste Text to paste the function and its parameters into your code.


Figure 8.11. The Object Browser showing all modules and classes in the Chapter8Figures database and all methods and properties in the basAnother module.

The example in Figure 8.11 shows the selection of a user-defined function selected from a module in a database. You can also select any built-in function. Figure 8.12 shows an example in which the DatePart function is selected from the Visual Basic for Applications library. The Object Browser exposes all libraries referenced by the database. It is covered in more detail in Chapters 10 and 24.


Figure 8.12. The Object Browser with the VBA Library selected.

Practical Examples: Event Routines, User-Defined Functions, and Subroutines Required by the Time and Billing Application

This example uses a form, a query, and a report to retrieve criteria and then preview sales information by client for a specific date range. The report called rptClientInformationByProject is based on a query called qryBillingDetailByClient. The query requires information from a form called frmPrintClientBilling. The frmPrintClientBilling form must be open for the process to complete successfully because the criteria for the query is gathered from the report. The frmPrintClientBilling form is shown in Figure 8.13. The code behind the two command buttons looks like this:

Sub cmdRunReport_Click()
   If IsNull(Me!txtStartDate) Or IsNull(Me!txtEndDate) Then
      MsgBox "Both the Start Date and End Date Must Be Filled In"
   Else
      Call RunReport("rtpClientInformationByProject")
   End If
End Sub
Sub cmdClose_Click()
    DoCmd.Close
End Sub


Figure 8.13. The Report Criteria form requesting required information for the query underlying the Billing Detail Report.

The first routine evaluates the two text boxes to ensure that they are filled in. If either text box contains a null, a message is displayed. If neither text box is null, a user-defined routine called RunReport is executed. The second routine simply closes the criteria form.

The RunReport subroutine is included in a module called basUtils. It looks like this:

Sub RunReport(strReportName As String)
      DoCmd.OpenReport strReportName, acPreview
End Sub

This routine receives the name of any report as a parameter. It runs the report whose name is passed as the parameter.

The other code is found in the report itself. It is placed in the Open event of the report and looks like this:

Private Sub Report_Open(Cancel As Integer)
   If Not IsLoaded("frmPrintClientBilling") Then
      MsgBox "Print Client Billing Form Must Be Open to Run This Report", _
         vbCritical, "Error!!"
      Cancel = True
   End If
End Sub

This routine calls a user-defined function named IsLoaded. If the function returns a false, indicating that the required criteria form is not open, a message is displayed and the report is canceled. The IsLoaded function looks like this:

Function IsLoaded(ByVal strFormName As String) As Integer
    IsLoaded = False
    Dim frm As Form
    For Each frm In Forms
      If frm.Name = strFormName Then
         IsLoaded = True
      End If
    Next frm
 End Function

This function loops through the forms collection. It tries to match the name of each open form with the name of the report criteria form. If a match is found, a True is returned; otherwise, a False is returned.

Summary

A strong knowledge of the VBA language is an imperative ingredient for the Access developer. This chapter covered all of the basics of the VBA language. You learned the differences between code, form, and report modules, and how to effectively employ the use of each. You also learned the difference between event procedures and user-defined subroutines and functions. So that you can get the most mileage out of your subroutines and functions, you learned how to pass parameters to and receive return values from procedures.

Variables are used throughout your application code. Declaring each variable with the proper scope and lifetime helps to make your application bullet-proof and easy to maintain. Furthermore, selecting an appropriate variable type ensures that a minimal amount of memory is consumed and that your application code will protect itself. Finally, effectively employing the use of control structures and built-in functions, gives you the power, flexibility, and functionality required by even the most complex of applications.

Previous Page TOC Index Next Page Home