Chapter 11

Expert Approaches to VBA


If you're still relying on macros in your application development, I suspect that Access 97 will cause you to change your style. The enhanced power, flexibility, and usability of the latest release of VBA are impossible to ignore, and a well-constructed expert solution is usually teeming with custom objects and procedures. In this chapter, I'll show you how to use VBA effectively by showing you the following:

Basic Priorities


"Removing Embedded Basic (the Access 2 code engine) from Access 95 and inserting Visual Basic for Applications in its place was akin to a heart, lung, and liver transplant on our product all at once. The effort was extraordinarily complex and required more than five person-years of development time.

"Yet, when most users look at the result, they get more excited about the color-coded syntax in the code editor than the more substantial-and more painful-enhancements that VBA brought to the product."

Tod Nielsen, General Manager, Microsoft Access Business Unit

As Tod's introductory sidebar implies, changing the coding engine in Access from Access Basic (also called Embedded Basic or EB) to Visual Basic for Applications (VBA) was no simple task. EB had much in common syntactically with its siblings Visual Basic 3 and the Visual Basic for Applications in Excel and Project, but was based on a different engine. EB was woven into Access at a very low level, and some of it was written in Assembler for the best performance. VBA, in contrast, is a service external to Access, much like the Jet Database Engine, and is written entirely in C.

Microsoft was highly motivated to move to one common Basic dialect and engine for all of its development applications. The benefits of this standardization to you as a developer are threefold:

The Access team took a courageous leap by pulling VBA into Access. To understand the magnitude of the effort, you must first understand that VBA thinks in terms of projects, or integrated work sets of classes (code and objects). The project-centric metaphor integrates well with Visual Basic and other hosts, but in the case of Access proved particularly problematic. The Access environment provided an especially difficult environment for the following assumptions made by VBA:

Pulling VBA into Access meant teaching the two components to get along despite these and other differences in approach and structure.

While the Basic language and its interface may now be mostly standardized, the way people write code certainly is not. Throughout this book I use the term style to refer to your unique approach to application development. One of the aspects of your personal style is how you write code.

For example, there is more than one way to name objects, more than one way to compose code comments, and more than one way to perform file operations. The more time you spend writing application code, the more likely you are to develop standardized approaches to development of Basic code routines.

Standardizing your approach to VBA coding requires that you first understand the best way to apply its features. This chapter provides a discussion of the issues involved in creating consistent and reliable VBA code. Here, we explore the important aspects of VBA development and investigate techniques that make your development work more productive.


This chapter is not a VBA tutorial. It assumes that you have some familiarity with writing Basic code in Access and want to learn new features and approaches. You can prepare for this chapter by reading the VBA lessons in Que's Access 97 Power Programming, by F. Scott Barker.

Implementing Major New VBA Features

There's a lot more to VBA in Access than just color-coded syntax. There were some very important changes and additions to Access' coding language between versions 2 and 95. I'll summarize them in case you missed them:


A type library is a file that lists descriptions of the object model (objects, properties, and methods) of a program or service provider. A type library can be embedded within a dynamic link library (DLL) or ActiveX control (OCX) file, or can be stored in a standalone (TLB or OLB) file.

The Access 97 release of VBA offers the following additional enhancements:

Learning about these new VBA features comes faster than actually understanding how to apply them. The new traits have broad applicability and can be mixed and matched in various ways when you are concocting creative code solutions. In the next few sections, I focus on the new features that are the most useful in application development.

Using the Application Object Model Enhancements

Access 95 introduced the Application object, which is a pointer to the current application (database file). The Application object allows Access to support the Automation object model and exposes more of Access to calling programs. As a result, you can now automate an Access application from any VBA code (Access or non-Access).

The Forms collection (pointers to each open form in an application) and the Reports collection are now contained within the new Application object, and the DoCmd and Screen objects have also been attached to it. These are all objects that you are familiar with from the days of Access 1, with these notable changes:

Application.DoCmd.OpenForm "frmAutomation"

Refer to the DoCmd or Screen objects from code in a controlling application by directing your code toward their Application object parent. For example, you can use the following lines of code to work with these objects in an Access application serving as an Automation server whose instance is referenced by the Application object variable accappNwind:

accappNwind.DoCmd.SelectObject acForm, "Main Switchboard"

If accappNwind.Screen.ActiveForm.Name = " Main Switchboard" Then...

Putting Access to Work Via Automation

You refer to the Application object or a variable built on it when you need to address the entire application, one of its settings, or one of its primary subordinate objects. Access exposes the Application object to code within the current database file so that you can affect the current database and also uses this object type as the primary interface to an "instance" of Access acting as an Automation server.

You can refer to an Application object from three different places:

Listing 11.1 Three Different Ways to Address a Member of the
Application Object' The shortest variation

Debug.Print CurrentObjectName

' The explicit variation

Debug.Print Application.CurrentObjectName

' Utilizing an object variable

Dim appCurr As Application

Set appCurr = Application ' Points to the current Application object

Debug.Print appCurr.CurrentObjectName

You drive Access as an Automation server by using the Application object as the entry point to instantiate (create an instance) of Access from VBA code. From within Access, Office, Project, or Visual Basic VBA code, create an object variable using the Access.Application object with Dim. Directly referencing an application's type in the Dim statement is called early binding, as contrasted to using an Object variable (late binding). Early binding produces the fastest-executing code:

Dim accappNwind As Access.Application

Set accappNwind = CreateObject("Access.Application.8")

Your VBA project must have a reference to the Access object library MSACC8.OLB in order to bind to the Access.Application object. The code shown previously is best in an environment where users could have more than one version of Access on their workstation. The explicit version number in the CreateObject call assures that the code instantiates the desired Access version.

If versioning is not a concern, your code can use the New keyword to launch an instance of whatever the Windows registry considers the default version of Access:

Dim accappNwind As New Access.Application


The AutoExec macro and/or startup form declared in the Startup properties execute in an Automation instance of Access as they would in a normal user instance. You generally will not want an application used as an Automation server to have either of these objects. (For example for purposes of the sample code in this section, you need to disable the startup form in Northwind.)


Access is an out-of-process OLE server, defined as, "a standalone, executable program that runs in its own address space." Access meets this definition because it must serve the needs of controller applications as well as desktop users. (In contrast, an ActiveX control is an example of an in-process server, defined as a DLL, OCX, or other file that always requires a host or controlling application.)


An Automation session between a controller application and Access as a server requires a connection (a communication pipeline) between the two players. You can improve the performance of Automation operations by optimizing connection usage. See the section "Speeding Up Automation" later in this chapter for hints.

Once you've created an object variable that points to an Access Application object, Access is running as an Automation server and the properties and methods of the Application object can be referenced, as in this example:

' Open a database

accappNwind.OpenCurrentDatabase "C:\Office\Samples\Northwind.Mdb"

' Print a report by referring to its Application parent

accappNwind.DoCmd.OpenReport "Catalog"

When a new Automation instance of Access is created, the instance is by default not visible. (The Visible property of the application is False; in actual fact, the instance is minimized not invisible.) If you need to display the instance to the user, you can set the Visible property to True. If not, the instance remains minimized until closed.

A programmatic instance of Access terminates when the procedure that created it goes out of scope, or when you set the object variable to Nothing:

Set accappNwind = Nothing

However, there are caveats to the previous paragraph. A server instance of Access may not close if there is a process pending. In this context, a process is anything that the server deems unfinished business. Also, if an instance of Access is opened invisible (minimized) and then set to Nothing, the instance closes correctly. However, if the user interacts with the instance (maximizes it and opens a form, for example) before it is set to Nothing, the instance does not close, because the user interaction has started an open process that must be completed before the server can close.

Both of these situations can be remedied by good coding. You can explicitly close a remote application instance from the calling code and force it to terminate with the Quit method. Alternately, closing the current database releases any pending processes and allow the instance to close on cue, as shown in Listing 11.2.

Listing 11.2 Two Ways to Close an Automation Instance of Access

' These two commands consistently close the server

accappNwind.CloseCurrentDatabase

Set accappNwind = Nothing

' As do these two

accappNwind.Quit

Set accappNwind = Nothing


Of the two syntax options, Quit is the more reliable and consistent with the Automation model.

Listings 11.3 and 11.4 clarify the previous points by providing two Automation examples.

Listing 11.3 AES_VBA.Mdb Creating an Access Server Instance with Automation

' This code block demonstrates how an open process will not terminate

Dim accappNwind As Access.Application

Set accappNwind = CreateObject("Access.Application.8")

accappNwind.OpenCurrentDatabase "C:\Office\Samples\Northwind.Mdb"

' At this point, Visible is False

' Display the server and a form in it

accappNwind.Visible = True

accappNwind.DoCmd.OpenForm "Main Switchboard"

' At this point, if the user clicks a button on the Northwind

' switchboard, the server instance opens a pending process

' Closing the variable does NOT close the server because a process

' is pending; you need to use CloseCurrentDatabase or Quit first

Set accappNwind = Nothing

Listing 11.4 AES_VBA.Mdb Creating an Access Automation Server Instance to Print a Report

' This code block shows an example of Automation to print

Dim accappNwind As Access.Application

Set accappNwind = CreateObject("Access.Application.8")

accappNwind.OpenCurrentDatabase "C:\Office\Samples\Northwind.Mdb"

' At this point, Visible is False

' You do not need to display the server to run a process in it

accappNwind.DoCmd.OpenReport "Summary of Sales by Quarter"

' Close the instance

accappNwind.Quit

Set accappNwind = Nothing

Figure 11.1 shows a conceptual representation of the Application object and its members from the standpoint of behaving as an Automation server.

Fig. 11.1

Automation of Access is facilitated through the exposed Application object.

We've touched on several properties and methods of an Application object in this section. The next two sections provide a complete run-down of all of the new properties and methods available.

Setting an Application Object's Properties

Properties of the current database's Application object or of an object variable referring to an Access instance can be read (and sometimes set) from VBA code. When referring to properties in an Automation application instance, you must prefix the property name with the object variable name, as in:

If accappNwind.Visible = True Then

When referring to properties of the current application, the keyword Application is optional. Thus, the following two lines are equivalent:

MenuBar = "lci_mbrSimple"

Application.MenuBar = "lci_mbrSimple"

The following list describes the primary properties of the Application object that are of interest when doing VBA coding or Automation. Read-only properties are denoted with RO and read/write with RW:

Listing 11.5 CodeContextObject Points To The Object
At The Top Of The Call Tree

' This code is in form frmOne

Private Sub cbfCustCheck()

Call Form_frmTwo.cbfCustFlag

End Sub

' This code is in form frmTwo

Public Sub cbfCustFlag()

Call ErrorMsg()

End Sub

' This code is in a standard module

Public Sub ErrorMsg()

' This statement displays "frmOne"

Debug.Print CodeContextObject.Name

End Sub


If the current object is a child of one of the six database object types (for example, a property dialog of a form in design view, or a menu for a form in browse view), CurrentObjectName and CurrentObjectType refer to the parent object.


If the current object is the Database window, the CurrentObjectName and CurrentObjectType properties refer to the object currently selected in the window, even if the object is not open. You should be careful to detect the state of the object before trying to manipulate it. Use code like the following:

If SysCmd(acSysCmdGetObjectState, CurrentObjectType _

, CurrentObjectName) = 0 Then

' Object is closed, do nothing

Else

' Do something here

End If


DAO code in a remote application runs much slower than in the current application, so you should generally only access the DBEngine property of the current database instead of a remotely automated database. Your code can use the OpenDatabase method of the current application to directly access an external database without relying on Automation.


The setting of the MenuBar and ShortcutMenuBar properties at the Application object level overrides the setting provided in the Startup options dialog for the database.

Also, forms and reports have a MenuBar property and a ShortcutMenuBar property, which override the Application object's settings for these properties while the form or report is displayed.

If Application.UserControl = True Then

' Display the status messages here

End If

Notice that functions and statements that you are probably already familiar with from prior versions of Access are now related to the Application object. For example, DBEngine is now a property of the Application object, even though you usually reference it directly instead of using Application.DBEngine. The value of this structure is that you can create an Application object variable for an Automation instance of Access and reference any application properties through that object variable.


To explore the Application object in more detail, view it in the Object Browser dialog by selecting it from the Classes list in the Access library.

Applying Methods to an Application Object

Just as property settings enable you define or retrieve the characteristics of an Application object instance, methods enable you to run functions that make the object perform tasks. Some of the tasks are important when using Access as an Automation server, and others are more useful within the current application.

As with properties, the object prefix Application is not required when making a reference to a method in the database containing the code line. For databases opened through Automation, the calling code should reference these methods through the Application object variable that points to the instance:

Set accappNwind = CreateObject("Access.Application.8")

accappNwind.OpenCurrentDatabase "C:\Office\Samples\Northwind.Mdb"

Here are the Application object's methods:

' Returns "File not found"

Debug.Print AccessError(53)

Err.Description

AccessError(Err.Number)

Application.AccessError(Err.Number)


The AccessError method returns Access, Jet and VBA error messages in Access 97, but does not return Jet error messages in Access 95.

' The method returns "CategoryID=6 Or CategoryID=7 Or CategoryID=8"

Me.Filter = BuildCriteria("CategoryID", dbLong, "6 or 7 or 8")

' This variation demonstrates BuildCriteria against a recordset

rst.FindFirst BuildCriteria("CategoryID", dbLong, "Between 6 And 8")

accappNwind.CloseCurrentDatabase

accappNwind.DBEngine.RepairDatabase "C:\Office\Samples\Northwind.Mdb"


When Access is running as an Automation server application, you might expect CurrentDb to point to the controlling application. However, both CurrentDb and CodeDb in this context return the server database.


The option values that can be retrieved with this method (or set with SetOption) are detailed in Access' on-line help system under the topic "Set Options from Visual Basic."

This method cannot read or set options displayed on the Modules tab of the Options dialog.


You can only use this method against an Automation instance of Access; you cannot create a new database through the current Application object. Close the currently open database in the server instance using the CloseCurrentDatabase method before issuing this command.


accappNwind.CloseCurrentDatabase

accappNwind.NewCurrentDatabase "C:\Data\Horses.Mdb"


Close the currently open database with the CloseCurrentDatabase method before issuing this command.


Return values sent back by a function called with the Run method are discarded.

Chapter 20, "Applying Development Shortcuts," discusses this method in greater detail.

The powerful methods of the Application object enable you to manipulate Automation instances of Access to perform almost any task.

Using Non-Access Objects

You've seen, in the previous topics, how Access provides an exposed object structure that can be addressed from program code. The other Office applications also expose their internal structures in a similar fashion and allow program code to propel them remotely.

Applications usually expose their components to Automation through the use of a file that contains Automation-specific descriptions of their objects (classes), properties, and methods. Such files usually have one of these extensions:

Table 11.1 lists the components of Office and Visual Basic that can be automated or shared and their Automation definition (type library) file names. Use the References option on the Tools menu in an Access module to create a reference to the type library for a component that you want to browse in the Object Browser or write Automation code against.

Table 11.1 Components of an Office-Centric Development Environment That Can be Automated

Product

Type Library

Entry Point

Access 8

MSACC8.OLB

Access.Application.8

Binder 8

MSBDR8.OLB

OfficeBinder.Binder.8

DAO 3.5

DAO350.DLL

DAO.DBEngine.35

Excel 8

EXCEL8.OLB

Excel.Application.8

Excel.Chart.8

Excel.Sheet.8

Graph 8

GRAPH8.OLB

MSGraph.Application.8

MAPI-OLE Messaging 1

MDISP32.TLB

MAPI.Session.1

Outlook 8

MSOUTL8.OLB

Outlook.Application.8

PowerPoint 8

MSPPT8.OLB

PowerPoint.Application.8

Project 4

PJ4EN32.OLB

MSProject.Application.4

MSProject.Project.4

Schedule+ 7

SP7EN32.OLB

SchedulePlus.Application.7

SQL OLE 6.5

SQLOLE65.TLB

SQLOLE.SQLServer.6.5

Word 8

MSWORD8.OLB

Word.Application.8

See Chapter 10, "Creating Expert Tables and Queries," for an example of Automation code written against Excel's object model.

In addition to the listed Automation servers, there are several support files that provide extensions to Office applications by attaching themselves to the host application's object model and behaving like intrinsic features. Table 11.2 lists such files.

Table 11.2 Office Extensions That Are Hosted

Product

Type Library

Provides

Forms 2

FM20.DLL

Form controls, properties, and methods

Office 8

MSO97.DLL

Assistant, command bars

VBA

VBA332.DLL

Collections, errors

VBA Editor

VBEEXT1.OLB

Project editor, references

The hosted extensions listed in Table 11.2 do not require explicit Automation to use; they become part of the host's object model. Thus, if your Access application contains a reference to MSO97.DLL, the Office objects are directly available to your program code, as demonstrated by the Office FileSearch object in Listing 11.6.

Listing 11.6 AES_VBA.Mdb Using the New FileSearch Object
Hosted Inside Access

Private Sub cmdSearch_Click()

With FileSearch

.NewSearch

.FileName = "Northwind.Mdb"

.FileType = msoFileTypeDatabases

.LookIn = "C:\"

.MatchTextExactly = True

.SearchSubFolders = True

If .Execute(SortBy:=msoSortByFileName _

, SortOrder:=msoSortOrderAscending) > 0 Then

Me!txtNWPath = .FoundFiles(1)

Else

Me!txtNWPath = Null

Beep

, SortOrder:=msoSortOrderAscending)

MsgBox "Could not find Northwind, enter the path yourself." _

, , "Sorry"

End If

End With

End Sub

Similarly, VBA editing services are provided to Excel by VBEEXT1.OLB. If your Excel application references this file, it can issue commands like the following based on objects in the referenced object library:

strName = Application.VBE.ActiveVBProject.Name


Access has its own forms engine and does not rely on the MSForms objects in FM20.DLL, which create forms in Excel, Outlook, PowerPoint, and Word. You do not use this DLL from Access.

Access also provides its own VBA editor, thus you also do not need to reference VBEEXT1.OLB, which provides the VBA editing services for the rest of Office.

Applying Conditional Compilation

By default, when you compile Access VBA code, every line in all of the application's modules (when using Debug, Compile All Modules or Debug, Compile and Save All Modules) or all of the application's open modules (with Debug, Compile Loaded Modules) is parsed and converted to pseudo code. If you have code that is relevant only in specific situations, you can tell Access to ignore compilation of that chunk of code in other situations. This default creates less compiled code (with an according performance increase) and also provides you the ability to give your application multiple personalities with the flick of a switch.

The switch in this case is called a Conditional Compilation Constant (abbreviated as CCC here), which is the global flag (or flags) that you pass to your code to facilitate the branching required for conditional compilation. Branching is achieved by testing the conditional constant with a special conditional If statement.

VBA provides this syntax for use with conditional compilation:

#If...Then

#ElseIf...Then

#Else

#End If

When one of these #If statements detects the appropriate setting of the named CCC, it branches accordingly, as in Listing 11.7:

Listing 11.7 AES_VBA.Mdb Code Can Be Conditionally Compiled Based Upon The Setting Of A Global Constant

#If lci_gcccDebug Then ' Debug mode

Application.SetOption "Break on All Errors", True

MsgBox "Debugging is on."

#Else

MsgBox "Debugging is off."

#End If

To create Conditional Compilation Constants, enter one or more of them in the Options dialog on the Advanced tab, as shown in Figure 11.2. If you are declaring more than one CCC flag, separate the values with colons.

Fig. 11.2

Conditional Compilation Constant values are entered in the Options dialog.

After you enter CCC flag values in the Options dialog, compile all of the module code in the application. Some code may be excluded from the compiled code block based on the entered flags. The Options dialog preserves the value of the flags between sessions of the current database, but not between different databases.


The CCC argument entered into the Options dialog cannot make use of intrinsic constants or expressions, you can only set the CCC value to a positive or negative integer, like this:

lci_gcccDebug = 0 ' This is valid in the Options dialog

lci_gcccDebug = False ' This is not valid in the dialog

The #If statement treats a CCC variable value of 0 as False and any non-zero value as True.

Only the special #If statements listed previously can reference a declared CCC, you cannot reference a conditional flag from any other VBA statement (not even in the Debug window). The inverse is also true: a conditional #If statement can only reference conditional constants and literal values in its statements, as in the examples in Listing 11.8.

Listing 11.8 Conditional Compilation Syntax Examples

' These are valid in code

#If lci_gcccDebug = True Then... ' Comparison to constant

#If lci_gcccDebug = "DebugOn" Then... ' Comparison to literal

' These are not valid

#If lci_gcccDebug = DebugGet() Then... ' Calls user-defined function

MsgBox CStr(lci_gcccDebug) ' CCC in non-conditional statement

Note in the previous code that a CCC value is being compared to a string value on one line. While CCC values cannot be non-numeric in the Options dialog, they can be assigned non-numeric values when declared in module code. CCC flags are declared in program code with the #Const declaration. Such declarations should be declared at the module level and are private to the module. A conditional constant so-defined has scope only within its module, so the following command is not allowed:

Public #Const lci_gcccDebug = False

Thus, you can only configure conditional statements at the module level from code-the only way to create application-wide CCC values is from the Options dialog. This means that you must set application-wide conditional constants in the dialog and then recompile the application manually.


Your application can have multiple Conditional Compilation Constants in each module and can apply global CCC values from the Options dialog in addition to the presence of CCC values in modules.


Beginning with Access 97, you can also change CCC values from code and recompile for them to take effect, as in this example:

Application.SetOption "Conditional Compilation Arguments" _

, "lci_gcccDebug = 1"

RunCommand acCmdCompileAndSaveAllModules

Here are some suggestions on how to make use of this feature in your code:

Sub EndOfMonth()

' When gcccAdmin is False, none of the following code is compiled

#If gcccAdmin Then

' All administrative procedure code goes in here

#End If

End Sub

There are still instances when you need more flexibility in handling conditional operations than is provided by conditional compilation. In situations where you need to set a conditional flag from a table, the Windows registry, an initialization file, or user interaction, global variables in standard If and Case statements provide flexibility still not available in the new conditional statements.

Exploring New Features Elsewhere in this Book

There are several powerful new VBA features that are described in detail elsewhere in this book as they are used in development techniques. They are:

Refer to the specific chapters noted for in-context demonstrations of these new features.

Developing With Visual Basic for Applications

If many of your days (and, as is very often the case, your nights) are spent writing VBA code, you may find that you've developed habits, "rules-of-thumb", shortcuts, conventions, and other style elements. Perhaps you've never formalized them, and even change them periodically for variety. Nevertheless, you've probably discovered that consistency can make your life easier.

For example, in both EB and VBA, you can use a variable in an assignment statement without ever declaring it with Dim (if the Option Explicit switch is not in the parent module). Access creates a Variant variable for you on the fly, using its best-guess to select the data type, as in this example:

varWork = 10

MsgBox VarType(varWork) ' Shows 2, which is an Integer

Such code is dangerous because Access is selecting the type for you. If you had really intended for this new variable to be a Long Integer and simply forgot to declare it, Access has possibly set your code up for failure (an overflow or type mismatch) later on by making it an Integer.

By contrast, in earlier and in the current versions of Access, placing an Option Explicit statement in each module forces the compiler to alert you to undefined variables. Many coders adopt the use of this statement as a standard policy after their first negative experience with a type mismatch between non-declared variables.


You can now set the Require Variable Declaration option in the Options dialog to force the automatic addition of an Option Explicit line to each new module.

Adopting a policy that says, "Do not use any undeclared variables" is an example of creating your personal coding style. The remainder of this chapter discusses issues that frequently arise in the course of VBA development. An understanding of the issues and techniques provides you with a solid foundation to define your coding style, by creating a set of standardized approaches to VBA.

Structuring Basic Code Procedures

dCOMMENTS


Years ago when we did dBASE development, one of our clients required us to aggressively comment our code. I've always been one to put a lot of code comments in a routine, but this client was extreme even by my standards-every code "block" (as they defined it, a group of related commands like an IF...ENDIF chunk) was preceded with a section comment, and each line of code required an in-line comment. This yielded programs like this:


* Check for the number 5

IF LEN(sInput) = 5 && Entered the number 5

DO Fiver && Run Fiver.Prg to handle the number 5

ELSE && Didn't enter the number 5

DO NoFiver && Run NoFiver.Prg to handle other numbers


ENDIF && End of block

Their intentions were actually good. dBASE was the kind of program where you could solve a particular problem a variety of ways, and different coders created radically different solutions to the same problem. For our client, maintaining code developed by outsiders was virtually impossible without good documentation and code comments. However, I think they went a little overboard.


By the time we stopped working with them, I'd been corrupted-I was adding parenthetical comments to my meeting notes, shopping lists, and letters to friends. Their commenting style had even trickled into my personal habits!

To me, the word coding is such an appropriate designation for what many developers do, because creating procedures involves uniquely mixing characters, numbers, and algorithms into a block of text that virtually nobody beside yourself can read. Reading another person's routines can be much like cracking a secret code.

There is a positive reason for this situation-the Basic language is flexible enough and Access is powerful enough to allow different developers with different styles to solve a stated problem in different ways. Flexibility yields diversity.

Chapter 5, "Creating Naming Conventions," provided you with some insights into the thought processes involved in designing a development style. Alternately, you may simply choose to use the style du-jour in your code, expressing today's mood and not fitting some larger plan.

If you have either of the following three needs, however, I would suggest you consider creating a consistent VBA coding style:

What are some of the elements of a VBA coding style? At the highest level, a primary style consideration is how to name procedures, variables, and the objects they work with, as exemplified by the conventions in Chapter 6, "Leszynski Naming Conventions for Access," and Appendix C, "Leszynski Naming Conventions for Microsoft Solution Developers."

Another very high-level style decision is selecting how you structure and locate code routines within an application. See Chapter 7, "Understanding Application Architecture," for a discussion of this topic.

At a more detailed level, there are several style decisions that you must make for each procedure and line of code that you write, as detailed in the following topics.

Indenting Code

Access' default indentation level is 4 characters. While this makes code look extremely clean and readable, I find that the text moves across the screen too quickly in complex applications. Consider this example:

Sub Demo()

Dim varname...

For Each...

Select Case...

With object...

.property =...

If...

Do...

You can see that editing a procedure like this in a module window that is not maximized to fill an SVGA screen quickly crowds critical code segments off the right edge of the screen.

At the other extreme, some developers code with no indentation. Let's take the same example and left-align it:

Sub Demo()

Dim varname...

For Each...

Select Case...

With object...

.property =...

If...

Do...

I've had to cleanup both kinds of code, and I would much prefer to deal with too much indentation rather than none at all, which produces code that is nearly impossible to audit or debug. My personal style is a 2 character indent, striking a compromise between both extremes shown here. Whether you adopt this indentation level for yourself or choose another, apply your choice consistently.

Indentation can also be used for alignment of information within code lines, and some coders do go slightly nuts when aligning code. For example, the comments in the following Case statement are all aligned:

Select Case Err.Number

Case 52, 53, 64, 75, 76 ' Path/file error or not found

lci_FileExists = cintErrNoFile

Case 57, 68, 71 ' No device or disk

lci_FileExists = cintErrNoDev

Case 70 ' No permission

lci_FileExists = cintErrNoPerm

End Select

Most people don't find code like this much more readable, and some even find it annoying. I tend to use special alignment selectively, favoring it when it improves readability. For example, I don't generally align comments unless I'm writing code for printed publication, but I do like to arrange Dim statements for readability in all my code:

Const cstrProc As String = "lci_AppBoAECheck"

Dim blnBreak As Boolean

Dim strMsg As String

Wrapping Code Lines

Wrapping long lines of code is a whole new ball game with the introduction of a line continuation character into VBA. Long lines of code are now much easier to manage. Formerly, code lines would disappear off the right edge of the screen, a situation that could only be avoided if they were painstakingly concatenated, as in this example:

strMsg = "An error has occurred, please contact Technical Support."

strMsg = strMsg & " Send e-mail to TechSupp or call extension 12345"

strMsg = strMsg & " for immediate aid."

Life is much better now. Although concatenating strings still involves matching quote pairs before continuing to the next line, long lines of code are easier to build and read with a continuation mark:

strMsg = "An error has occurred, please contact Technical Support." _

& " Send e-mail to TechSupp or call extension 12345" _

& " for immediate aid."

Notice three conscious style decisions I made when wrapping the lines of code:

I made these style decisions after trying various strategies and deciding which one worked best for my team and my clients. You can employ the same methodology to create your own policy.


You should also consider how to locate commas in long delimited lists. You can choose to locate them at the end of the line or the beginning, as in these examples:

' The comma trails the first line


DoCmd.OpenForm "Customers", , , "CompanyName Like '*market*'", _

acReadOnly, acDialog

' The comma wraps to the second line

DoCmd.OpenForm "Customers", , , "CompanyName Like '*market*'" _

, acReadOnly, acDialog

As with the use of leading spaces in the prior example, my team prefers the second alternative shown. Placing the comma at the beginning of the continued line is an immediate visual clue that the line is a continuation, even in the absence of any indentation.

On the road that leads to a consistent style, SQL statements introduce another fork. Depending on how much SQL you create, you may decide to standardize your approach or to ignore the issue completely. Consider the statement shown in Listing 11.9.

Listing 11.10 A Long SQL Statement Syntax Line Without Indentation

strSQL = "SELECT zstlogErr.ErrWhen, zstlogErr.ErrProcName," _

& " zstlogErr.ErrUserName, zstlogErr.ErrNumber," _

& " zstlogErr.ErrDescription FROM zstlogErr WHERE" _

& "(zstlogErr.ErrWhen >= #1/1/96#) AND" _

& "(zstlogErr.ErrNumber = 3146) ORDER BY zstlogErr.ErrWhen;"

The previous statement is what your English teacher used to call a run-on sentence, one that never seems to end. Worse yet, the statement shown is a very short SQL string. SQL statements in complex applications can go for fifty or a hundred lines.

Contrast the previous example with the code in Listing 11.11, which is at the extreme opposite end of the spectrum with respect to indentation.

Listing 11.11 A Long SQL Statement Syntax Line With Indentation

strSQL = "SELECT zstlogErr.ErrWhen," _

& " zstlogErr.ErrProcName," _

& " zstlogErr.ErrUserName," _

& " zstlogErr.ErrNumber," _

& " zstlogErr.ErrDescription" _

& " FROM zstlogErr" _

& " WHERE (zstlogErr.ErrWhen >= #1/1/96#)" _

& " AND" _

& " (zstlogErr.ErrNumber = 3146)" _

& " ORDER BY zstlogErr.ErrWhen;"

If I'm trying to find an error or insert a change in a long SQL statement, especially one that I didn't personally write, the second style of formatting can save several minutes of confusion.


Don't worry about a performance penalty for the indenting scheme shown-Jet optimizes the extra spaces out. Also, if you use code like this to create a new QueryDef object in a database, when you go to design view in the query you'll find that Jet has removed your indentation and spaces in favor of its own wrapping model. Your painstakingly precise alignment is not saved in the QueryDef.


Using named procedure arguments provides a third option for standardizing the wrapping of code, because their use frequently produces multi-line statements. Wrapping long commands with named arguments enables you to sort the arguments by name, which can improve readability and make reviewing or debugging a statement much easier. Consider the following statement, that reorders the arguments to OpenForm alphabetically for readability and uses the indentation and line continuation styles described previously:

DoCmd.OpenForm "frmCust" _

, DataMode:=acReadOnly _

, WhereCondition:="CompanyName Like '*market*'" _

, WindowMode:=acDialog

None of the style decisions involved in wrapping long code lines could be considered mission critical or make your code run any faster or slower. Thus, standardizing an approach to this task is more important for team development than when coding in a solo environment.

Sprinkling Your Code with Comments

Audit Trail Ad-Nauseum


One time at a client site, I noted that their IT group placed revision comments at the top of each of their code routines. The comments looked something like this:

' FSB 1/10/94 Created procedure

' FSB 1/14/94 Finished procedure

' ALC 4/12/94 Converted dynasets to snapshots for performance


' FSB 10/1/94 Added new error trapping model

I didn't disagree with the idea in principle, but I did notice that some of the comment blocks got very long, which required you to press PgDn several times from the top of a procedure just to see the first line of code. As I was reviewing and debugging their code, I found that these comments got in the way and did not add value. Even worse, I found some procedures that were totally empty except for comments, with the last one reading something like this:

' ALC 2/14/96 Procedure abandoned, code removed

This seemed to indicate that they placed a higher value on their comments than they did on their code!

In the chapter on the Leszynski Naming Conventions (Chapter 6), I included suggestions for procedure-level code comments. Basically, you should consider standardizing the type of information you put at the top of each procedure and the information's layout. Consistent code comments are very useful when reading your own or another person's code.

Here are some examples of commonly-used procedure heading comments:

Here is an example of a reasonable amount of comment information, taken from the top of one of our routines:

Public Sub lci_ErrLog(rlngErr As Long, rstrProc As String _

, rstrDesc As String)

' Purpose: Write an error log record to zstlogError

' Arguments: rlngErr:=Error number

' rstrProc:=Module and procedure name

' rstrDesc:=Err.Description or similar string to log

' Authors: Created 11/10/95 SWL Edited 3/2/96 WOW

' Example: lci_ErrLog(Err.Description, cstrProc, Err.Description)

' Called By: lci_ErrMsgStd

' Calls: lci_IsBlank

If you are inclined to keep a revision history in your procedures (see the previous sidebar), consider placing the comments at the bottom of procedures just before the End Sub or End Function statement, rather than at the top. The information is still available when needed but is less obtrusive.

For in-line code comments, your options are to create comments as independent lines or to add comments to the ends of code lines. Both are useful, and some developers prefer to mix and match them. Other teams use only full-line comments, in order to make reviewing code or stripping comments easier.

In either case, I generally find that the location of the comment is less important than what it says. There is a fine balance between too many comments and too few, and the judgment of what is too much or too little is in the eye of the individual reader of the code.


When I'm reading others' code, I tend to expect a comment every five or ten lines as an average, depending on the complexity of the routine. If there are too few comments, I find it impossible to browse the code by simply reading the comments, which I will do when looking for a specific feature or simply trying to understand the general flow of the routine.

Listin 11.12 provides an example of in-line code comments that help to clarify the values in an If block for readers who are not the original coder. The If, ElseIf, and Else values are all explained:

Listing 11.12 Using Comments to Clarify Each If, ElseIf, and Else Statement

If olngHelpID = -1 Then ' No Help button

MsgBox strMsg, vbOKOnly + vbCritical, lci_gcstrAppTitle

ElseIf olngHelpID = 0 Then ' Use the default ID

MsgBox strMsg, vbOKOnly + vbCritical, lci_gcstrAppTitle _

, lci_gcstrAppHelp, lci_gcintAppErrHelpID

ElseIf olngHelpID > 0 Then ' Use the argument ID

MsgBox strMsg, vbOKOnly + vbCritical, lci_gcstrAppTitle _

, lci_gcstrAppHelp, olngHelpID

Else ' Error, bad ID

MsgBox "Problem@Invalid value '" & CStr(olngHelpID) _

& "' passed to error handler '" & mcstrMod & "." & cstrProc _

& "'.", vbOKOnly + vbCritical, lci_gcstrAppTitle

End If

One commenting technique that has helped us a lot is to standardize how our developers note open issues in code. When we stop coding at a certain point, "comment-out" an On Error statement for testing purposes, or want to record an open issue in the code, we use two exclamation marks, like this:

lci_ErrLog_Err:

'!!TODO Add support for log to file if back-end not found

Beep

...

Before we ship an application, we search all of its code for our custom '!! marker and review the attached comments to see whether any of them indicate work that must be finished before releasing the application.


If you place comments in your code, they must be as accurate as the code itself, and they must be modified when the code changes in order to keep them accurate. Misleading comments are worse than none at all.

Another handy commenting technique is to place comment blocks at the top of the Declarations section of each module that describes the module, its purpose, and the procedures in the module. Such a record serves as a quick reference for the entire module, as shown in Listing 11.13.

Listing 11.13 AES_VBA.Mdb Module Comments Serve as a Quick Reference for
the Module

' lci_basLibErr, Version 1/31/96

' Standard error handling routines

' Copyright (c) 1995-96 Leszynski Company, Inc.

' May not be used without permission

' All routines here should start with 'lci_Err'

' Library dependencies:

' lci_basLibGenl.lci_IsBlank()

' lci_basLibInit.gstrCurrentUser

' Index:

' lci_ErrLog - Write error log record to zstlogError

' lci_ErrMsgFixup - Remove @ signs from an Err.Description string

' lci_ErrMsgStd - Standard error message display

Handling Run-Time Errors

One of the most intimidating facets of the development process is the way that no program ever seems able to be perfected. Some testing cycles seem to go on forever, as developers fix bugs and create bugs in the same keystrokes. There always seems to be one more thing keeping the software from being done.

When the solution finally does ship, it is presupposed that the users will find ways to make it misbehave. However, not all problems are bugs. There are several reasons why processes or code in an application can fail:

When an application fails for one of the listed reasons, Access, Jet, or VBA generates an internal error event and expresses this occurrence to your code. If you have not designated a mechanism for handling these error notifications, Access stops and displays the offending line of code (or alternately displays its own error alert if no code is running).

You tell Access how to deal with error conditions by placing On Error statements in your code. There are several derivatives of the On Error statement:

In general, good development practice requires that you use the On Error Goto labelname variation in most code routines. It must name a line label as a jump destination when an error occurs. Below the line label, you place your error management code (called an error handler).

The standard flow of error handling in a procedure is shown in Listing 11.14.

Listing 11.14 The Commonly-Used VBA Error Handling Structure

proctype procname()

On Error GoTo procname_Err

' The general code would be here

procname_Exit:

' The cleanup code would be here

Exit proctype

procname_Err:

' The error handler code would be here

Resume procname_Exit

End proctype

When an error occurs, control goes from the offending line (which does not complete execution) to the error handler (shown in the listing with the procname_Err label) and runs the statements there. To terminate your error handler code block, you have three choices:

Listing 11.15 Code Can Branch From an Error Handler to Various Locations in the Procedure

Select Case Err.Number

Case 52, 53, 64, 75, 76 ' Path/file error or not found

Resume ErrNoPath

Case 57, 68, 71 ' No device or disk

Resume ErrNoDisk

Case Else ' Unspecified error

Resume ErrOther

End Select

Listing 11.16 AES_VBA.Mdb A Customized Error Handler Can
Check For A Specific Error By Number

Public Sub lci_AppIconCheck()

' Purpose: Try to locate and set the application icon

On Error GoTo lci_AppIconCheck_Err

Const cintErrPrpNotFound As Integer = 3270

Dim dbsCurr As Database

Dim prpIcon As Property ' User-defined property

Dim strIcon As String ' Icon filename

' If the property does not exist, the next line jumps to _Err

strIcon = dbsCurr.Properties("AppIcon") ' Get the icon

' The rest of the code would be here

lci_AppIconCheck_Exit:

Exit Sub

lci_AppIconCheck_Err:

If Err.Number = cintErrPrpNotFound Then

Set prpIcon = dbsCurr.CreateProperty("AppIcon", dbText _

, "C:\Office\Access\Default.Ico")

dbsCurr.Properties.Append prpIcon

Resume ' And retry the offending line

Else

' The standard error handler would be here

End If

End Sub

In addition to On Error Goto labelname, you can issue the On Error GoTo 0 statement to turn off error handling in the current procedure. When an error occurs while this command is in effect, VBA goes backwards through the call tree looking for a procedure with an error handler and executes the first one it finds. If it finds none, Access itself responds to the error by providing an alert.

For example, Listing 11.17 shows nested routines, with only the parent routine enabling an error handler. The child routine instead uses On Error Goto 0, which tells it to find an available handler or use Access' handler. When the child routine fails, the routine in the parent is triggered to supplant it.

Listing 11.17 AES_VBA.Mdb A Demonstration Of On Error Goto 0
Passing Errors To The Next Available Handler

' The parent routine

Private Sub cmdGoto0_Click()

' Purpose: Demonstrate ramifications of On Error Goto 0

' Calls: cbfGoto0

' This routine has an error trap

On Error GoTo cmdGoto0_Click_Err

Const cstrProc As String = "cmdGoto0_Click"

Call cbfGoto0 ' Goto the subordinate routine

cmdGoto0_Click_Exit:

Exit Sub

cmdGoto0_Click_Err:

Call lci_ErrMsgStd(Me.Name & "." & cstrProc, Err.Number _

, Err.Description, True)

Resume cmdGoto0_Click_Exit

End Sub

' The child routine in the same form

Sub cbfGoto0()

' Purpose: Shows how an error here is passed up the call tree

' Called By: cmdGoto0_Click

On Error GoTo 0 ' Error goes to parent's handler or Access

DoCmd.OpenForm "frmBogusName" ' Not a real form, triggers an error

End Sub


The use of On Error Goto 0 can be terribly dangerous. When a routine fails but passes its error to a higher-level handler, all context is lost and the higher error handler essentially inherits the blame for the code that failed. This makes debugging very difficult.


On Error Goto 0 was formerly used to turn off error handling in order to enable debugging. The new Break on All Errors option setting provides a better mechanism for debugging, leaving On Error Goto 0 with limited usefulness.

The third error handling syntax is the On Error Resume Next statement, which enables you to momentarily discontinue error handling and simply ignore any resulting errors. The line that produced the error will not complete, but execution resumes at the line following the offender. This command is used when you consciously want to skip a statement or block of code and not be notified if it fails to run.

For example, it's a good coding policy to close open Jet objects (recordsets, databases, and so on) before leaving a procedure. However, if you are exiting a procedure due to an error, the objects you are trying to close may not ever have gotten created within the procedure; thus, you may generate another error while trying to bail out after an error. On Error Resume Next solves this situation by simply skipping statements that could cause such problems:

procname_Exit:

On Error Resume Next

rst.Close

dbs.Close

Exit Sub

Error handling code can be as simple as giving the user a message and giving up, as in this example:

procname_Err:

MsgBox "Error: "& cstr(Err.Number) & " " & Err.Description,,"Oops!"

Resume procname_Exit ' Abort the procedure

Alternately, some routines need rather complex error handling. Here are three examples of situations where you may want to provide advanced (smarter) error recovery than simply halting the procedure:


Some recordset operations legitimately should be discarded completely if one operation fails. However, others can be continued even in the presence of a partial failure. In such a case, your code should be smart enough to detect the error, determine that it is not fatal, and move past the problem record to the next record in the recordset and continue processing.

How your application displays error conditions to users is an area where your personal development style is reflected in your application. Your error messages may be as terse as simply displaying the Err.Description property of the Error object in a message box. Alternately, error displays can be as elaborate as drawing a custom form that shows error context information, a suggested resolution, and a telephone extension or e-mail address to use for immediate support.


If your users see several error messages more frequently than others, you can create strings of extra information to display for these specific errors to provide them with extra help. Keep the strings in a table with the error number as the key, then open an error alert form filtered to show only the specific message text for the error in process.

Alternately, create help topics for the most frequent errors and call the help topics from your error handler.

You can discern which errors occur most frequently by logging all errors to a database table as they occur. See Chapter 17, "Bulletproofing Your Application Interface," for more information on this technique.

Here are the most common problems related to error handling that I see in applications:


In a professional-grade expert solution, even the smallest code routine has an error handler.

Listing 11.18 Code That Runs in an Infinite Recursive Loop When it Fails

wsp.BeginTrans

' Recordset code would go here

wsp.CommitTrans

ErrSample_Exit:

Exit Sub

ErrSample _Err:

' Code would display error message here

wsp.Rollback ' This line will fail if no open transaction

Resume ErrSample_Exit


The solution to the problem just demonstrated is to set a flag (for example, blnInTrans = True)whenever you open a transaction, and check the flag in the error handler before rolling back the transaction, as in this example:

' The following replaces the single line: wsp.Rollback

If blnInTrans Then

wsp.Rollback

End If

str = dbsCurr.Properties("AppTitle") ' Get the title

#If mcccDebug = False Then ' Module-level debug mode

On Error GoTo procname_Err

#End If

To make error handling easier to code and more flexible in your application, you can create and reuse one or more standard error handling routines. For example, you can create a routine to handle file I/O errors, and a different one for recordset errors. You can call these routines as library functions from your error handler or paste the code into the handlers as required.

You can also create a standard routine for error messages and logging. The code in Listing 11.19 shows our standard library routines for displaying error messages to the user and capturing the error condition in a table. Our standard routine is complex, but even if you choose not to use it you benefit from understanding how our approach works.

Figure 11.3 shows an alert generated by the routine in the listing.

Fig. 11.3

This custom error alert is drawn by a library display routine.

Listing 11.19 AES_VBA.Mdb A Central Library Routine For Error
Message Display

Public Sub lci_ErrMsgStd(rstrProc As String, rlngErr As Long _

, rstrDesc As String, rblnLog As Boolean _

, Optional ovarResol As Variant, Optional olngHelpID As Long = -1)

' Purpose: Standard error message display

' Arguments: rstrProc :=Module and procedure name

' rlngErr :=Err number

' rstrDesc :=Error description

' rblnLog :=True/False=add to error log table

' ovarResol :=Resolution string, if Missing uses global

' olngHelpID :=Help context ID, -1=none (default)

' , 0=app default, >0=ID to call

' Example: Call lci_ErrMsgStd(Me.Name & "." & cstrProc, Err.Number

, Err.Description , True, "Compact the database.", 128)

' Calls: lci_ErrLog

' Pseudo Code:

' 1. Build the Problem message section from the argument values

' 2. Add Jet/ODBC information from the Errors collection

' 3. Add a standard resolution string and app title string

' 4. Show the message with the appropriate message box buttons

' 5. Log the error to a table if requested

On Error GoTo lci_ErrorMsgStd_Err

Const cstrProc As String = "lci_ErrMsgStd"

Dim errJet As Error

Dim strDAOMsg As String

Dim strMsg As String

strMsg = "Problem@" & lci_gcstrAppErrIntro & vbCrLf & vbCrLf

strMsg = strMsg & "Message: " & lci_ErrMsgFixup(rstrDesc) _

& " [" & CStr(rlngErr) & "]" & vbCrLf & vbCrLf

strMsg = strMsg & "Routine: " & rstrProc

' If Jet errors were found, add them to the message

If DBEngine.Errors.Count > 1 Then

If DBEngine.Errors(DBEngine.Errors.Count-1).Number = rlngErr Then

For Each errJet In DBEngine.Errors

With errJet

strDAOMsg = strDAOMsg & " " _

& lci_ErrMsgFixup(.Description) _

& " [" & CStr(.Number) & " in " & .Source & "]"

End With

Next

strMsg = strMsg & vbCrLf & vbCrLf & "Details:" & strDAOMsg

End If

End If

If IsMissing(ovarResol) Then

strMsg = strMsg & "@Suggestion: " & lci_gcstrAppErrResol

Else

strMsg = strMsg & "@Suggestion: " & ovarResol

End If

If olngHelpID > -1 Then ' Will have Help button, add help text

strMsg = strMsg & " " & lci_gcstrAppErrHelp

End If

Beep

If olngHelpID = -1 Then ' No Help button

MsgBox strMsg, vbOKOnly + vbCritical, lci_gcstrAppTitle

ElseIf olngHelpID = 0 Then ' Use the default ID

MsgBox strMsg, vbOKOnly + vbCritical, lci_gcstrAppTitle _

, lci_gcstrAppHelp, lci_gcintAppErrHelpID

ElseIf olngHelpID > 0 Then ' Use the argument ID

MsgBox strMsg, vbOKOnly + vbCritical, lci_gcstrAppTitle _

, lci_gcstrAppHelp, olngHelpID

Else ' Error, bad ID

MsgBox "Problem@Invalid value '" & CStr(olngHelpID) _

& "' passed to error handler '" & mcstrMod & "." _

& cstrProc & "'.", _

vbOKOnly + vbCritical, lci_gcstrAppTitle

GoTo lci_ErrorMsgStd_Exit

End If

If rblnLog Then

Call lci_ErrLog(rlngErr, rstrProc, strMsg)

End If

lci_ErrorMsgStd_Exit:

On Error Resume Next

Exit Sub

lci_ErrorMsgStd_Err:

' In a failure, this routine should not call itself

Beep

strMsg = "Problem@This application has encountered a problem '" _

& Err.Description & " [" & CStr(Err.Number) _

& "]' in the error display handler '" & mcstrMod _

& "." & cstrProc & "'." & "@" & lci_gcstrAppErrResol

MsgBox strMsg, vbOKOnly + vbCritical, lci_gcstrAppTitle

Resume lci_ErrorMsgStd_Exit

End Sub

Here are the highlights of the routine in the listing:

Fig. 11.4

Using the formatting character (@) produces this customized MsgBox() alert.


In Access 95, the MsgBox() function inserted the word Solution in bold as a header line before the text in the third substring (after the second @). This feature has been removed from Access 97, you can no longer create a second bolded header in the message box.

There are more subtleties to handling run-time errors than were listed in this topic, but they all involve variations on the ideas I've presented here. Handling errors properly is a development chore that must be taken seriously and a talent that is cultivated through hands-on experience.

Optimizing Your Basic Code Routines

I'll admit, quite candidly at this point, that some of the techniques for creating expert solutions create slower code, not faster code. For example, a Basic loop that calls out to a library function in each iteration of the loop runs slower than one that calls a chunk of code embedded directly inside the loop. Therefore, as you create modular code according to my model you are possibly slowing your applications.

A counterbalance to this point is that, as your coding expertise increases, you can offset the speed losses in one area with speed gains in another by writing better code. Additionally, as the VBA compiler improves with age, the compilation process becomes more sophisticated at optimizing your code, and thus its architecture becomes less important. All things considered, the performance of your applications is more strongly affected by database operations than VBA code execution speed. Thus the place to optimize your application first is in data access.

When you do get around to reviewing your code for VBA performance bottlenecks, keep in mind the primary VBA performance negative in Access 97 applications: uncompiled code. VBA decompiles the code in your application when any of these events occur:

Code in an uncompiled state must be recompiled as it is run. An uncompiled application is a certain breeding ground for performance complaints.

Your code can check the compilation state of the application using the new IsCompiled property, as shown in Listing 11.20:

Listing 11.20 AES_VBA.Mdb Handling An Uncompiled Application
With IsCompiled

If Application.IsCompiled = False Then

Beep

If MsgBox("This application is decompiled and my run slowly." _

& " Would you like to recompile the application now?" _

, vbYesNo, lci_gcstrAppTitle) = vbYes Then

' Recompile the application. Note that this requires opening

' a module, which may be dangerous in a production application

DoCmd.OpenModule "lci_basLibInit"

Application.RunCommand acCmdCompileAndSaveAllModules

DoCmd.Close acModule, "lci_basLibInit", acSaveNo

End If

End If

Access 97 uses a new "compile on demand" model which tests a module's compilation state as it's loaded into memory (when a procedure that resides in it is first called). If the module is not compiled, Access compiles it first (but does not save the compiled state). This takes time. Before you ship, and whenever you make a change to an application already deployed, compile the entire application. Open any module (form or standard) and select Debug, Compile and Save All Modules.


Report and form code is now saved in a compiled state if you compile it before you save the object. Thus, form and report code either run faster than in Access 2 (if compiled), or slower (if not compiled), but rarely at the same speed as in the version of Access that most of us use for performance metrics.

The next two topics provide additional performance considerations.

Improving VBA Performance

In addition to the standard admonitions discussed elsewhere in this chapter (including remember to compile your code before shipping and use conditional compilation to selectively exclude code), there are several less potent techniques that can add incremental performance benefits to your applications. Applied together, they may provide a noticeable speed improvement:


There are quite a few new intrinsic constants that you can use in your code, such as days (vbWednesday, et. al.), keyboard characters (from vbKeyA to vbKeyZ), and the long-desired carriage-return/linefeed combination (vbCrLf). Browse the Access, DAO, and VBA libraries in the Object Browser to see the available constants.


In the past, conventional wisdom said that economizing code with such techniques made the code run faster. With VBA's new compilation model, this is no longer true. There is no performance gain from making your (raw) source code more sparse..


To disable screen repaints, set the Painting property of a form to False, or set the Application object's Echo property off.


There are literally dozens more small VBA optimization techniques, but the performance benefits of each is marginal when contrasted with the benefits from optimization of other elements of an application, such as form loading, Automation, and data access.

Speeding Up Automation

An Automation session sends information back and forth between the server and controller through connections. Connections are managed automatically (you cannot see them), but due to the way Automation works, they are recreated for each message sent back and forth.

However, you can trick Automation into keeping a connection open by using the new With statement, as in Listing 11.21.

Listing 11.21 AES_VBA.Mdb Optimizing Automation Code Using
With

Dim accappNwind As Access.Application

Set accappNwind = CreateObject("Access.Application.8")

accappNwind.OpenCurrentDatabase "C:\Office\Samples\Northwind.Mdb"

With accappNwind

.Visible = False

With .DoCmd

.OpenReport "Summary of Sales by Quarter"

.OpenReport "Summary of Sales by Year"

End With

.Quit

End With

Set accappNwind = Nothing


Using With to keep a connection open increases the speed of your code only if early binding is used (the object variable is declared by type). In a late-bound variable (declared as Object), the With statement shows no performance benefit and may actually run slower. (See the section "Putting Access to Work Via Automation" earlier in this chapter to review the definitions of these terms.)

Your Automation work also performs better if you apply these techniques:

Obviously, you must also give your Automation-enabled application a fighting chance to perform by providing it with a machine with 16M of RAM or greater.

From Here...

In creating this chapter, I had to identify the concepts that would provide you with the most food for thought and overall benefit as you develop Access applications. The scope of VBA, however, is quite large and provides many more opportunities for exploration than were visited here. Many other chapters in this book demonstrate VBA's features while in the process of teaching their own solution:


1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.