Previous Page TOC Index Next Page Home


9

Advanced Visual Basic for Applications Techniques

The Visual Basic for Applications (VBA) language is extremely rich and comprehensive. The VBA language is covered throughout this book as it applies to various topics. This chapter shows you additional techniques to assist you in your application development. Some of the techniques are tips and tricks of the trade and others are advanced aspects of the language that were not covered in Chapter 8, "VBA 101: The Basics of VBA." An understanding of the concepts included in this chapter is essential for the more advanced topics that are covered in the remainder of this book.

Navigation Tips and Tricks

Effectively utilizing the tips and tricks of the trade can save you hours of time. This chapter highlights many of the tips and tricks that can help you get your work done more efficiently and effectively. These tricks help you to navigate around the coding environment as well as quickly and easily modify your code. They include the ability to easily zoom to a user-defined procedure, search and replace within modules, obtain help on VBA functions and commands, and split the Code window so that two procedures can be viewed simultaneously.

Mysteries of the Coding Environment Solved

Developers who are new to VBA are often confused by the coding environment. The Code window contains two combo boxes. These boxes are shown in Figure 9.1. The combo box on the left lists objects. In the case of a form or report, the list includes all the objects on that form or report. In the case of a module, which has no objects, only General appears.


Figure 9.1. The Code window with the Object combo box.

The combo box on the right contains all the events associated with a particular object. Figure 9.2 shows all the events associated with a command button. Notice that the Click event is the only event that appears in bold, because it is the only event that has been coded.


Figure 9.2. The Code window with the Procedure combo box.

Zoom: Shift+F2

As you become more advanced with VBA, you will create libraries of VBA functions and subroutines. As you are viewing a call to a particular subroutine or function, you often want to view the code behind that function. Fortunately, VBA gives you a quick and easy way to navigate from procedure to procedure. Assume that the following code appears in your application:

Private Sub cmdOkay_Click()
    Dim iAgeInTen As Integer
    If IsNull(Me!txtName) Or IsNull(Me!txtAge) Then
        MsgBox "You must fill in name and age"
        Exit Sub
    Else
        MsgBox "Your Name Is: " & Me!txtName & " and Your Age Is: " & Me!txtAge
        Call EvaluateAge(Val(Me!txtAge))
        iAgeInTen = AgePlus10(Fix(Val(Me!txtAge)))
        MsgBox "In 10 Years You Will Be " & iAgeInTen
    End If
End Sub

Let's say that you want to quickly jump to the procedure called EvaluateAge so that you can take a better look at it. All you need to do is place your cursor anywhere within the call to EvaluateAge and then press Shift+F2. You are immediately moved to the EvaluateAge procedure. Ctrl+Shift+F2 takes you back to the routine where you came from (in this case, cmdOkay_Click). This procedure works for both functions and subroutines.

Find and Replace

Often, you name a variable only to decide later that you want to change the name. VBA comes with an excellent find-and-replace feature to help you with this process. You can simply search for data, or you can search for a value and replace it with some other value. To invoke the Find dialog, select Edit|Find or use Ctrl+F. The Find dialog appears in Figure 9.3.


Figure 9.3. The Find dialog is set up to search for typCompanyInfo within the current module.

Type the text you want to find in the Find What text box. Notice that you can search within the Current Procedure, Current Module, Current Database, or Selected Text. Find Whole Word Only does not find the text if it is part of another piece of text. For example, if you select the Find Whole Word Only option and then search for "Count," VBA does not find "Counter." Other options include toggles for case sensitivity and pattern matching.

The Find dialog allows you to search for text, and the Replace dialog allows you to search for text and replace it with another piece of text. The Replace dialog is shown in Figure 9.4.


Figure 9.4. The Replace dialog is set to find typCompanyInfo and replace it with typCompanyInformation within the current module.

The Replace dialog offers all the features of the Find dialog but also allows you to enter Replace With text. In addition, it lets you select Replace or Replace All. Replace asks for confirmation before each replacement, whereas Replace All replaces text without prompting for confirmation. I recommend that you take the time to confirm each replacement. It is easy to miscalculate the pervasive effects of a global search and replace.

Help

A very useful but little-known feature of VBA is the capability to get context-sensitive help while coding. With your cursor placed anywhere within any VBA command or function, press the F1 key. You are given context-sensitive help on that command or function. Most of the help topics also give you the opportunity to view practical examples of the function or command within code. Figure 9.5 shows help on the With...End With construct. Notice that the Help window includes the syntax for the command, a detailed description of each parameter included in the command, and remarks about using the command. At the top of the window you can see hypertext links to related topics ("See Also") as well as a link to an example of the use of the With...End With construct. If you click on Example, a specific example of the construct appears (see Figure 9.6). You can copy the example and place it into a module. This feature is a great way to learn about the various parts of the VBA language.


Figure 9.5. Help on With...End With.


Figure 9.6. An Example of With...End With.

Splitting the Code Window

The VBA Code window can be split so that you can look at two routines within the same module at the same time. This option is useful if you are trying to solve a problem that involves two procedures or event routines in a large module. An example of a split Code window appears in Figure 9.7.


Figure 9.7. A split Code window allows viewing of two routines.

Notice the splitter. By placing your mouse cursor on the gray splitter button just above the vertical scrollbar of the Code window and clicking and dragging, you can size each half of the window as desired. The window can be split only into two halves. After it has been split, you can use the Object and Procedure drop-downs to navigate to the procedure of your choice.


Only routines within the same module can be viewed in a particular Code window, but multiple Code windows can be open at the same time. Each time you open an Access, Form, or Report module, you are placed in a different window. Each module window can then be sized, moved, and split as desired.

Full Module View

Another way to view multiple routines at a time is to work in Full Module View. Full Module View was briefly discussed in Chapter 8. It enables you to view all the code within a module as if you were in a normal text editor. To activate Full Module View, select Tools|Options. Click on the Module tab and then place an x in the Full Module View check-box under Code View. This global setting affects all the modules in all your databases but can be changed at any time.

Full Module View has a slightly different effect than splitting a code window. Whereas Full Module View allows you to view multiple contiguous code routines, splitting the code window allows you to view two nonconsecutive routines within the same module.

The DoCmd Object: Performing Macro Actions

The Access environment is rich with objects. These objects contain built-in properties and methods. The properties can be modified and the methods can be executed using VBA code. One of the objects available in Access is the DoCmd object. This object enables you to execute macro actions in Visual Basic procedures. The macro actions are executed as methods of the DoCmd object. The syntax looks like this:

DoCmd.ActionName [arguments]

Here's a practical example:

DoCmd.OpenReport strReportName, acPreview

The OpenReport method is a method of the DoCmd object. It runs a report. The first two parameters that the OpenReport method receives are the name of the report you want to run and the view in which you want the report to appear (Preview, Normal, or Design). The name of the report and the view are both arguments of the OpenReport method.

Most macro actions have corresponding DoCmd methods. These methods can be found in Help (see Figure 9.8). Certain macro actions do not have corresponding DoCmd methods. They are AddMenu, MsgBox, RunApp, RunCode, SendKeys, SetValue, StopAllMacros, and StopMacro. AddMenu and SendKeys are the only two that have any significance to you as a VBA programmer. The remainder of the macro actions either have no application to VBA code or can be executed in better ways using VBA functions and commands. An example is the MsgBox action. The VBA language includes a MsgBox function that is far more robust than its macro action counterpart.


Figure 9.8. A listing of DoCmd methods in Help.

Many of the methods of the DoCmd object have optional parameters. If you do not supply an argument, its default value is assumed. You must use commas as placemarkers to designate the position of missing arguments. Here's an example:

DoCmd.OpenForm "frmOrders", , ,"[OrderAmount} > 1000"

The OpenForm method of the DoCmd object receives seven parameters. The last six parameters are optional. In the example, two parameters are explicitly specified. The first parameter is the name of the form, a required parameter. The second and third parameters have been omitted, meaning that you are accepting their default values. The commas, used as placemarkers for the second and third parameters, are necessary because one of the parameters following them is explicitly designated. The fourth parameter is the Where condition for the form. This has been designated as the records where the OrderAmount is greater than 1,000. The remaining four parameters have not been referenced, so default values for these parameters are used.

What Are User-Defined Types and Why Would You Use Them?

A user-defined type, known as a struct or record, allows you to create a variable that contains several pieces of information. User-defined types are often used to hold information from one or more records in memory. Because each element of a user-defined type can be instructed to hold a particular type of data, each element in the type can be defined to correspond to the type of data stored in a specific field of a table. A user-defined type might look like this:

Public Type TimeCardInfo
   TimeCardDetailID As Long
   TimeCardID As Long
   DateWorked As Date
   ProjectID As Long
   WorkDescription As String * 255
   BillableHours As Double
   BillingRate As Currency
   WorkCodeID As Long
End Type

Notice that the type of data stored in each element has been explicitly declared. The element containing the string WorkDescription has been declared with a length of 255. User-defined types make code cleaner by storing related data as a unit. A user-defined type exists only in memory and is therefore temporary. It is excellent for information that needs to be temporarily tracked at runtime. Because it is in memory, it can be quickly and efficiently read from and written to.

Declaring a User-Defined Type

You declare a user-defined type using a Type statement. This statement must be placed in the Declarations Section of a module. The type can be declared as Public or Private. Types cannot be placed in Form or Report modules.

Creating a Type Variable

A Type variable is an instance of the type in memory. A Type variable must be declared before you can use the type. To declare a Type variable, you create a Local, Private, Module-Level, or Public variable based on the type. Depending on where you place this declaration and how you declare it (Dim, Private, or Public), you will determine its scope. All the same rules that apply to any other kind of variable apply to Type variables. The Dim statement in the code that follows creates a variable called typTimeCardData. If you place this Dim statement in the General section of a module, it is visible to all routines within that module. If you place it in a subroutine or function, it is local to that particular routine:

Dim typTimeCardData As TimeCardInfo

Storing Information from a Record in a Form into a Type

After a Type variable has been declared, you can store data into each of its elements. The following code stores information from the form called frmTimeCardHours into a Type variable called typTimeCardData:

Private Sub cmdWriteToType_Click()
   Dim typTimeCardData As TimeCardInfo
   typTimeCardData.TimeCardDetailID = Me!TimeCardDetailID
   typTimeCardData.TimeCardID = Me!TimeCardID
   typTimeCardData.DateWorked = Me!DateWorked
   typTimeCardData.ProjectID = Me!ProjectID
   typTimeCardData.WorkDescription = Me!WorkDescription
   typTimeCardData.BillableHours = Me!BillableHours
   typTimeCardData.BillingRate = Me!BillingRate
   typTimeCardData.WorkCodeID = Me!WorkCodeID
End Sub

The advantage of this code is that rather than creating eight variables to store these eight pieces of related information, it creates one variable with eight elements. This method keeps things nice and neat.

Retrieving Information from the Elements of a Type

To retrieve information from your Type variable, simply refer to the name of the Type variable, followed by a period and then the name of the element. The following code displays a message box containing all the time-card hour information:

Private Sub cmdDisplayFromType_Click()
   MsgBox "Timecard Detail ID Is " & typTimeCardData.TimeCardDetailID & Chr(13) & _
         "Timecard ID Is " & typTimeCardData.TimeCardID & Chr(13) & _
         "Date Worked Is " & typTimeCardData.DateWorked & Chr(13) & _
         "Project ID Is " & typTimeCardData.ProjectID & Chr(13) & _
         "Work Description Is " & Trim(typTimeCardData.WorkDescription) & Chr(13) & _
         "Billable Hours Is " & typTimeCardData.BillableHours & Chr(13) & _
         "Billing Rate Is " & typTimeCardData.BillingRate & Chr(13) & _
         "Workcode ID Is " & typTimeCardData.WorkCodeID
End Sub

Working with Constants

A constant is a meaningful name given to a meaningless number or string. Constants can be used only for values that do not change at runtime. For example, a tax rate or commission rate might be constant throughout your application. Three types of constants exist in Access:

Symbolic constants are constants that you create using the Const keyword. They are used to improve the readability of your code and make code maintenance easier. Rather than referring to the number .0875 every time you want to refer to the tax rate, you can refer to the constant mccurTaxRate. If the tax rate changes and you need to modify the value in your code, you need to make the change only in one place. Furthermore, unlike the number .0875, the name mccurTaxRate is self-documenting.

Intrinsic constants are built into Microsoft Access; they are part of the language itself. As an Access programmer, you can use constants provided by Microsoft Access, Visual Basic, and Data Access Objects (DAO). You can also use constants provided by any object libraries you are using in your application.

System-defined constants are available to all applications on your computer. Only three system-defined constants exist: True, False, and Null.

Defining Your Own Constants

As mentioned, a symbolic constant is declared using the Const keyword. A constant can be declared in a subroutine or function. It can also be declared in the General section of a Form or Report module. Unlike in previous versions of Access, constants can be strong-typed in Access 95. The naming convention for constants consists of a suitable scoping prefix, the letter c to indicate that you are working with a constant rather than a variable, and then the appropriate tag for the data type. The declaration and use of a Private constant would look like this:

Private Const mccurTaxRate As Currency = .0875

This code, when placed in the Declarations section of a module, creates a Private constant called mccurTaxRate and sets it equal to .0875. An example of the constant used in code looks like this:

Function TotalAmount(curSaleAmount As Currency)
   TotalAmount = curSaleAmount * mccurTaxRate
End Function

This routine multiplies the curSaleAmount, received in as a parameter, by the constant mccurTaxRate. It returns the result of the calculation by setting the function name equal to the product of the two values. The advantage of the constant in this scenario is that the code is more readable than simply typing TotalAmount = curSaleAmount * .0875.

Scoping Symbolic Constants

Just as regular variables have scope, user-defined constants have scope. In the preceding example, you created a Private constant. The following statement, when placed in the Declarations section of a module, creates a Public constant:

Public Const pccurTaxRate = 0.0875 As Currency

Because this constant is declared as Public, it can be accessed from any subroutine or function (including event routines) in your entire application. To truly understand the benefits of a Public constant, imagine a scenario in which numerous functions and subroutines are all referencing the constant named pccurTaxRate. Imagine what would happen if the tax rate were to change. Without the use of a constant, you would need to search your entire application, replacing the old tax rate with the new tax rate. Because your Public constant is declared in one place, you can easily go in and modify the one line of code where this constant is declared.


By definition, the value of constants cannot be modified at runtime. If you attempt to modify the value of a constant, you receive the VBA compiler error Variable Required—can't assign to this expression.

Figure 9.9 illustrates this point. You can see that an attempt was made to modify the value of the constant called pccurTaxRate. A compile-time error occurred.

If you need to change the value at runtime, you should consider storing the value in a table rather than declaring it as a constant. You can read the value into a variable when the application loads and then modify the variable if required. If you choose, you can write the new value back to the table.


Figure 9.9. An attempt to modify the value of a constant.

Working with Intrinsic Constants

Microsoft Access declares a number of intrinsic constants. These constants can be used in Code, Form, and Report modules. They are reserved by Microsoft Access in that you cannot modify their values or reuse their names. They can be used at any time without being declared.

You should use intrinsic constants whenever possible in your code. Besides making your code more readable, they make your code more portable to future releases of Microsoft Access. Microsoft might change the value associated with a constant, but it is not likely to change the name of a constant. All intrinsic constants appear in the Object Browser. To view the constants that are part of the VBA language, select VBA - Visual Basic for Applications from the Databases/Libraries drop-down of the Object Browser. Click Constants in the Modules/Libraries list box. All the constants that are part of the VBA language are listed in the Properties/Methods list box (see Figure 9.10). Notice that all constant names begin with vb. All VBA constants are prefixed with vb, all Data Access Object constants are prefixed with db, and all constants that are part of the Access language are prefixed with ac. To view the Access language constants, select Access - Microsoft Access for Windows 95 from the Databases/Libraries drop-down. To view the Data Access Object constants, select DAO - Microsoft DAO 3.0 Object Library from the Databases/Libraries drop-down.


Figure 9.10. Using the Object Browser to view intrinsic constants.

Working with Arrays

An array is a series of variables referred to by the same name. Each element of the array is differentiated by a unique index number. Arrays help to make coding efficient. It is easy to loop through each element of an array, performing some process on each element. Arrays have a lower and upper bound, and all array elements must be contiguous.

The scope of an array can be Public, Module, or Local. As with other variables, this depends on where the array is declared and whether the Public, Private, or Dim keyword is used. The lower bound of an array is zero by default. All elements of an array must have the same data type.

Declaring and Working with Fixed Arrays

When declaring a fixed array, you give VBA the upper bound and the type of data that it will contain. The following code creates an array that holds six string variables:

Dim astrNames(5) As String

This array is fixed in that its size cannot be altered at runtime. The following code gives an example of how you can loop through the array:

Sub FixedArray()
    Dim astrNames(5) As String
    Dim intCounter As Integer
    astrNames(0) = "Dan"
    astrNames(1) = "Reggie"
    astrNames(2) = "Chelsea"
    astrNames(3) = "Joshua"
    For intCounter = 0 To UBound(astrNames)
        Debug.Print astrNames(intCounter)
    Next intCounter
End Sub

This code starts by storing values into the first four elements of a six-element array. It then loops through each element of the array, printing the contents. Notice that the For...Next loop starts at zero and goes until the upper bound of the array (5). Because the array is an array of strings, the last two elements of the array contain zero-length strings. If the array were an array of integers, the last two elements would contain zeros.

Another way to traverse the array is to use the For Each...Next construct. Your code would look like this:

Sub ArrayWith()
    Dim astrNames(5) As String
    Dim intCounter As Integer
    Dim vntAny As Variant
    astrNames(0) = "Dan"
    astrNames(1) = "Reggie"
    astrNames(2) = "Chelsea"
    astrNames(3) = "Joshua"
    For Each vntAny In astrNames
        Debug.Print vntAny
    Next vntAny
End Sub

This code declares a variant variable called vntAny. Instead of using a loop with Ubound as the upper delimiter to traverse the array, the example uses the For Each...Next construct to traverse the array.

Declaring and Working with Dynamic Arrays

Often, you do not know how many elements your array needs to contain. In this case, you should consider declaring a dynamic array. Dynamic arrays can be resized at runtime. This could make your code more efficient because VBA preallocates memory for all elements of a fixed array, regardless of whether data is stored in each of the elements. If you are not sure whether your array will contain very few elements or a large number of elements, preallocating a huge amount of memory can be extremely inefficient.

To create a dynamic array, you declare the array without assigning an upper bound. You do this by omitting the number between the parentheses when declaring the array. Here's an example:

Sub DynamicArray()
    Dim astrNames() As String
    Dim intCounter As Integer
    Dim vntAny As Variant
    ReDim astrNames(1)
    astrNames(0) = "Dan"
    astrNames(1) = "Reggie"
    For Each vntAny In astrNames
        Debug.Print vntAny
    Next vntAny
End Sub

A potential problem occurs when you try to resize the array:

Sub ResizeDynamic()
    Dim astrNames() As String
    Dim intCounter As Integer
    Dim vntAny As Variant
    ReDim astrNames(1)
    astrNames(0) = "Dan"
    astrNames(1) = "Reggie"
    ReDim astrNames(3)
    astrNames(2) = "Chelsea"
    astrNames(3) = "Joshua"
    For Each vntAny In astrNames
        Debug.Print vntAny
    Next vntAny
End Sub

You might expect that all four elements will contain data. Instead, the ReDim statement reinitializes all the elements, and only elements two and three will contain values. This problem can be avoided with the Preserve keyword. The following code behaves quite differently:

Sub ResizePreserve()
    Dim astrNames() As String
    Dim intCounter As Integer
    Dim vntAny As Variant
    ReDim astrNames(1)
    astrNames(0) = "Dan"
    astrNames(1) = "Reggie"
    ReDim Preserve astrNames(3)
    astrNames(2) = "Chelsea"
    astrNames(3) = "Joshua"
    For Each vntAny In astrNames
        Debug.Print vntAny
    Next vntAny
End Sub

In this example, all values already stored in the array are preserved. The Preserve keyword brings its own difficulties, though. It can temporarily require huge volumes of memory because during the ReDim process VBA creates a copy of the original array. All the values from the original array are copied to a new array. The original array is removed from memory when the process is complete. The Preserve keyword can present problems if you are dealing with very large arrays in a limited memory situation.


Each type of array complements the other's drawbacks. As a VBA developer, you have the flexibility to choose the right type of array for each situation: Fixed arrays are the way to go when the number of elements doesn't vary widely; dynamic arrays should be used when the number of elements varies widely and you're sure you have enough memory to resize even the largest possible arrays.

Advanced Function Techniques

The advanced function techniques covered in this section allow you to get the most out of the procedures you build. The section begins by discussing the difference between passing your parameters by reference and passing them by value. You see that the default method of passing parameters is not necessarily the most prudent method. Optional parameters allow you to build flexibility into your functions. The second part of this section shows you how to work with optional parameters. Optional parameters allow you to omit parameters, but named parameters allow you to add readability to your code. Named parameters also shelter you from having to worry about the order in which the parameters must appear. After reading this section, you will be able to build much more robust and easy-to-use functions.

Passing by Reference Versus Passing by Value

By default, parameters in Access are passed by reference. This means that a memory reference to the variable being passed is received by the function. This process is best illustrated by an example:

Sub PassByRef()
   Dim strFirstName As String
   Dim strLastName As String
   strFirstName = "Alison"
   strLastName = "Balter"
   Call FuncByRef(strFirstName, strLastName)
   Debug.Print strFirstName
   Debug.Print strLastName
End Sub
Sub FuncByRef(strFirstParm As String, strSecondParm As String)
   strFirstParm = "Bill"
   strSecondParm = "Gates"
End Sub

You might be surprised that the Debug.Print statements found in the subroutine PassByRef print "Bill" and "Gates". This is because strFirstParm is actually a reference to the same location in memory as strFirstName, and strSecondParm is a reference to the same location in memory as strLastName. This violates the concepts of black box processing, where a variable should not be able to be changed by any routine besides the one within which it was declared. The following code eliminates this problem:

Sub PassByVal()
   Dim strFirstName As String
   Dim strLastName As String
   strFirstName = "Alison"
   strLastName = "Balter"
   Call FuncByVal(strFirstName, strLastName)
   Debug.Print strFirstName
   Debug.Print strLastName
End Sub
Sub FuncByVal(ByVal strFirstParm As String, ByVal strSecondParm As String)
   strFirstParm = "Bill"
   strSecondParm = "Gates"
End Sub

This FuncByVal subroutine receives the parameters by value. This means that only the values in strFirstName and strLastName are passed to the FuncByVal routine. The strFirstName and strLastName variables, therefore, cannot be modified by the FuncByVal subroutine. The Debug.Print statements print "Alison" and "Balter".

Although I try to avoid passing parameters by reference, sometimes it makes good sense to do so. Take a look at the following example:

Sub GoodPassByRef()
   Dim blnSuccess As Boolean
   Dim strName As String
   strName = "Microsoft"
   blnSuccess = GoodFunc(strName)
   Debug.Print blnSuccess
End Sub
Function GoodFunc(strName As String)
   If Len(strName) Then
      strName = UCase$(strName)
      GoodFunc = True
   Else
      GoodFunc = False
   End If
End Function

In essence, the GoodFunc function needs to return two values. Not only does the function need to return the uppercase version of the string that is passed to it, but it also needs to return a success code. Because a function can return only one value, you need to be able to modify the value of strName within the function. As long as you are aware of what you are doing and why you are doing it, there is no problem with passing a parameter by reference.


I use a special technique to help the readers of my code easily see whether I am passing parameters by reference or by value. When I pass parameters by reference, I refer to the parameters using the same name in both the calling routine and the actual procedure I am calling. On the other hand, when I pass parameters by value, I refer to the parameters using different names in the calling routine and in the procedure I am calling.

Optional Parameters: Building Flexibility into Functions

Access 95 allows you to utilize optional parameters. In other words, it is not necessary to know how many parameters will be passed. The function called ReturnInit in the following code receives the second two parameters as optional. It then evaluates whether the parameters are missing and responds accordingly.

Function ReturnInit(ByVal strFName As String, _
       Optional ByVal strMI, Optional ByVal strLName)
   If IsMissing(strMI) Then
      strMI = InputBox("Enter Middle Initial")
   End If
   If IsMissing(strLName) Then
      strLName = InputBox("Enter Last Name")
   End If
   ReturnInit = strLName & "," & strFName & " " & strMI
End Function

This function could be called as follows:

strName = ReturnInit("Bill",,"Gates")

As you can see, the second parameter is missing. Rather than causing a compiler error, as in earlier versions of Access, this code compiles and runs successfully. The IsMissing function, which is built into Access, determines whether a parameter has been passed. After identifying missing parameters, you must decide how to handle the situation in code. In the example, the function prompts for the missing information. Other possible choices are

Here are two examples that illustrate implementation of these two alternatives:

Function ReturnInit2(ByVal strFName As String, _
       Optional ByVal strMI, Optional ByVal strLName)
   If IsMissing(strMI) Then
      strMI = "B"
   End If
   If IsMissing(strLName) Then
      strLName = "Jones"
   End If
   ReturnInit2 = strLName & "," & strFName & " " & strMI
End Function

This example uses a default value of "B" for the middle initial and a default last name of "Jones".

Function ReturnInit3(ByVal strFName As String, _
       Optional ByVal strMI, Optional ByVal strLName)
   Dim strResult As String
   If IsMissing(strMI) And IsMissing(strLName) Then
      ReturnInit3 = strFName
   ElseIf IsMissing(strMI) Then
      ReturnInit3 = strLName & ", " & strFName
   ElseIf IsMissing(strLName) Then
      ReturnInit3 = strFName & " " & strMI
   Else
      ReturnInit3 = strLName & "," & strFName & " " & strMI
   End If
End Function

This example manipulates the return value depending on which parameters it receives. If neither optional parameter is passed, just the first name displays. If the first name and middle initial are passed, the return value contains the first name followed by the middle initial. If the first name and last name are passed, the return value contains the last name, a comma, and the first name. If all three parameters are passed, the function returns the last name, a comma, a space, and the first name.

Named Parameters: Eliminate the Need to Count Commas

In all the examples you have seen thus far, the parameters to a procedure have been supplied positionally. Named parameters enable you to supply parameters without regard for their position. This is particularly useful with procedures that receive optional parameters. Consider an example:

strName = ReturnInit("Bill",,"Gates")

Because the second parameter is not supplied, and the parameters are passed positionally, a comma must be used as a placemarker for the optional parameter. This requirement can become unwieldy when you are dealing with several optional parameters. The following example greatly simplifies the process of passing the parameters and also serves to better document what is happening:

strName = ReturnInit3(strFName:= "Bill",strLName:= "Gates")

When parameters are passed by name, it doesn't even matter in what order the parameters appear, as in the following example:

strName = ReturnInit3(strLName:= "Gates",strFName:="Bill")

This call to the ReturnItit3 function yields identical results to the call to the function in the previous example.


When using named parameters, each parameter name must be exactly the same as the name of the parameter in the function being called. Besides requiring intimate knowledge of the function being called, this method of specifying parameters brings one important disadvantage. If the author of the function modifies the name of a parameter, all routines that use the named parameter when calling the function will fail.

Property Let and Get: Working with Custom Properties

Property procedures enable you to create custom runtime properties of user-defined objects. After you have defined custom properties, you can use Property Let and Get to assign values to and retrieve values from custom properties. Custom properties provide you with flexibility in creating your applications. Using custom properties, you can create reusable objects that expose properties to other objects.

Custom properties are Public by default and are placed in Form or Report modules, making them visible to other modules in the current database. They are not visible to other databases.

Property Let

Property Let is used to define a property procedure that assigns a value to a property of a user-defined object. Using a Property Let is similar to assigning a value to a Public variable, but a Public variable can be written to from anywhere in the database, with little or no control over what is written to it. Using a Property Let routine, you can control exactly what happens when a value is assigned to the property. Here's an example:

Property Let TextEnabled(blnEnabled As Boolean)
   Dim ctl As Control
   For Each ctl In Controls
      If TypeOf ctl Is TextBox Then
         ctl.Enabled = blnEnabled
      End If
   Next ctl
End Property

This routine receives a Boolean parameter. It loops through each control in the controls collection, setting the Enabled property of each text box to True or False, depending on the value of the Boolean variable that it was passed. You might be thinking that this code looks just like a subroutine. You are actually somewhat correct. It is a special type of subroutine that executes automatically in response to the change in the value of a custom property. The following line of code causes the code within the Property Let to execute:

Me.TextEnabled = False

The value False is received as a parameter to the Property Let routine. All the text boxes become disabled. The TextEnabled property of the form can be called from any module in the database, causing the Property Let routine to execute.

Property Get

Property Let is used to set the value of a custom property, and Property Get is used to define a property procedure that retrieves a value from a property of a user-defined object. This example illustrates how Property Get is used:

Property Get TextBoxValues()
   Dim ctl As Control
   For Each ctl In Controls
      If TypeOf ctl Is TextBox Then
         TextBoxValues = TextBoxValues & ctl.Name & _
            " = " & ctl.Value & Chr(13)
      End If
   Next ctl
End Property

The Property Get routine loops through each control on the form. It retrieves the name and value of each text box, building a return value that is a concatenated string with the names and values of all the text boxes. The call to the Property Get routine looks like this:

MsgBox Me.TextBoxValues

When the MsgBox command executes, it retrieves the value of the TextBoxValues property of the form. The Property Get routine automatically executes whenever the code attempts to retrieve the value of the property. This routine can be executed by retrieving the property from anywhere within the database.

Working with Empty and Null

Empty and Null are values that can exist only for Variant variables. They are different from one another and different from zero or a zero-length string. At times, you need to know whether the value stored in a variable is zero, a zero-length string, Empty, or Null. It is possible to make this differentiation only with Variant variables.

Working with Empty

Variant variables are initialized to the value of Empty. It is often important to ascertain whether a value has been stored into a Variant variable. If a variant has never been assigned a value, its value is Empty. As mentioned, the Empty value is not the same as Zero, Null, or a zero-length string. It is important to be able to test for Empty in a runtime environment. The IsEmpty function is used to determine whether a variable is Empty. The following example tests a String variable to see whether it is Empty:

Sub StringVar()
    Dim sName As String
    Debug.Print IsEmpty(sName) 'Prints False
    Debug.Print sName = "" 'Prints True
End Sub

The Debug.Print statement prints False. This variable is equal to a zero-length string because the variable is initialized as a String variable. All String variables are initialized to a zero-length string. The next example tests a Variant variable to see whether it is Empty.

Sub EmptyVar()
    Dim vntName As Variant
    Debug.Print IsEmpty(vntName) 'Prints True
    vntName = ""
    Debug.Print IsEmpty(vntName) 'Prints False
    vntName = Empty
    Debug.Print IsEmpty(vntName) 'Prints True
End Sub

A Variant variable loses its Empty value when any value has been stored to it, including zero, Null, or a zero-length string. It can become Empty again only by storing the keyword Empty into the variable.

Working with Null

Null is a special value used to indicate unknown or missing data. Null is not the same as Empty. Furthermore, one Null value is not equal to another Null value. Variant variables can contain the special value called Null.

It is often important to ascertain whether specific fields or controls have never been initialized. Uninitialized fields and controls have a default value of Null. By testing for Null, you can ensure that fields and controls contain values.

If you want to make sure that all fields and controls in your application contain data, you need to test for nulls. This can be accomplished using the IsNull function:

Sub NullVar()
    Dim vntName As Variant
    Debug.Print IsEmpty(vntName) 'Prints True
    Debug.Print IsNull(vntName) 'Prints False
    vntName = Null
    Debug.Print IsNull(vntName) 'Prints True
End Sub

Notice that vntName is equal to Null only after the value of Null is explicitly stored to it. It is important not only to know how variables and Null values interact, but how you can test for Null within a field in your database. A field contains a Null if data has not yet been entered into the field and the field contains no default value. In queries, you can test for the criteria "Is Null" to find all the records where a particular field contains a Null value. When dealing with recordsets, which are covered in Chapter 15, "What Are Data Access Objects and Why Are They Important?," you can also use the IsNull function to test for a Null value in a field. Here's an example:

Sub LoopProjects()
   Dim db As DATABASE
   Dim rs As Recordset
   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblProjects", dbOpenDynaset)
   Do While Not rs.EOF
      Debug.Print rs![ProjectID], rs![ProjectName]
      If IsNull(rs!ProjectBeginDate) Then
        Debug.Print "Project Begin Date Contains No Value!!"
      End If
      rs.MoveNext
   Loop
End Sub

All the concepts of recordset handling are covered in Chapter 15. For now, you need to understand only that this code loops through each record in tblProjects. It uses the IsNull function to evaluate whether the ProjectBeginDate field contains a Null value. If the field does contain a Null, a warning message is printed to the Debug window.

Private Sub Form_Current()
   Dim ctl As Control
   For Each ctl In Controls
      If TypeOf ctl Is TextBox Then
         If IsNull(ctl.Value) Then
            ctl.BackColor = 16776960
         Else
            ctl.BackColor = 16777215
         End If
      End If
   Next ctl
End Sub

The code in this example loops through every control on the current form. If the control is a text box, the routine checks to see whether the value within the text box is Null. If the value is Null, the BackColor property of the text box is set to Aqua; otherwise, it is set to White.

You should know about some special idiosyncrasies of Null:

The following example illustrates the propagation of Nulls:

Sub PropNulls()
   Dim db As DATABASE
   Dim rs As Recordset
   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblProjects", dbOpenDynaset)
   Do While Not rs.EOF
      Debug.Print rs![ProjectID], rs![ProjectBeginDate] + 1
      rs.MoveNext
   Loop
End Sub

Figure 9.11 illustrates the effects of running this routine on a table in which the first and third records contain Nulls. Notice that the result of the calculation is Null for those records.


Figure 9.11. The result of running the PropNulls routine.

Notice the difference if the value contained within the field is Empty:

Sub EmptyVersusNull()
   Dim db As DATABASE
   Dim rs As Recordset
   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblProjects", dbOpenDynaset)
   Do While Not rs.EOF
      Debug.Print rs![ProjectID], rs![PurchaseOrderNumber] + "Hello"
      rs.MoveNext
   Loop
End Sub

In this example, the tblProjects table contains four records. The PurchaseOrderNumber for the first record contains a Null. The PurchaseOrderNumber for the third record contains an Empty. Notice the different effects of the two values, as shown in Figure 9.12.


Figure 9.12. The result of running the EmptyVersusNull routine.

Looking at Figure 9.12, you can see that "Null" printed for the first record, and "Hello" printed for the third record.


The EmptyVersusNull routine uses a numeric operator (+). As discussed in the text, the effect of Null used in a calculation is a resulting Null. With text strings you can use an ampersand (&) instead of a plus (+) to eliminate this problem. Figure 9.13 illustrates the same code with an ampersand to concatenate rather than add. You can see that no nulls result from the concatenation.


Figure 9.13. The result of changing plus (+) in the EmptyVersusNull routine to ampersand (&).

It is very common to create a generic routine that receives any value, tests to see whether it is Null, and returns a non-Null value. An example is the CvNulls function:

Function CvNulls(vntVar1 As Variant, vntVar2 As Variant) _
      As Variant
   CvNulls = IIf(IsNull(vntVar1), vntVar2, vntVar1)
End Function

This routine would be called as follows:

Sub TestForNull(vntSalary As Variant, vntCommission As Variant)
   Dim curTotal As Currency
   curTotal = CvNulls(vntSalary, 0) + CvNulls(vntCommission, 0)
   MsgBox curTotal
End Sub

The TestForNull routine receives two parameters: salary and commission. It adds the two values together to determine the total of salaries plus commissions. Ordinarily, if the value of either parameter is Null, the expression results in Null. This problem is eliminated by the CvNulls function. The CvNulls function receives two parameters. The first parameter is the variable that is being tested for Null. The second parameter is the value that you want the function to return if the first parameter is determined to be Null. The routine uses the combination of the immediate if (IIf) function and the IsNull function to evaluate the first parameter and return the appropriate value.

Understanding and Effectively Utilizing Compilation Options

Microsoft Access provides you with a few alternatives for compilation. Understanding these alternatives can help you to decide whether compilation speed or the trapping of compilation errors is more important to you.

Compile On Demand

By default, VBA compiles your code only when the code in the module changes or when a procedure in one module is called by another module. Although this default setting can dramatically speed the compilation process, it can leave you wondering if you have a hidden timebomb lurking somewhere within your application.

Here's a typical scenario: You open a form, make some simple changes, save the changes, and close the form. You repeat this process for a few additional forms. You also open a couple of modules to make some equally simple changes. During the testing process, you forget to test one or more of the forms and one or more of the modules. With the Compile On Demand option set to True (its default value), any errors will not be identified until the offending code is accessed.

To disable the Compile On Demand feature, select Tools|Options. Click the Module tab and remove the check from Compile On Demand. You might notice a degradation in performance each time your code compiles, but this is time well spent.

Compile Loaded Modules

Whether the Compile On Demand feature is on or off, the Compile Loaded Modules tool, found on the Visual Basic toolbar, compiles only loaded modules. This means that all open Access, Form, and Report modules are compiled. The code within any closed modules, forms, and reports is not compiled. If an error is found in any open module, the compilation process terminates, an error message is displayed, and your cursor is placed on the offending line of code.

Compile All Modules

The Compile All Modules feature is found under the Run menu. This menu item, when selected, compiles every module in the database, regardless of whether it is open. To save all code in the database in its fully compiled state, you must open the database exclusively, select the Compile All Modules feature, and then select File|Save All Modules. This procedure not only ensures that the modules compile successfully, it ensures that they are saved in their compiled state so that they do not need to be compiled again when the application is run.

Customizing the IDE

Prior to Access 95, Access programmers were given little opportunity to customize the look and feel of the interactive development environment (IDE). Fortunately, Access 95 provides major improvements in this area. To view the environment options, select Tools|Options and then click the Module tab. Figure 9.14 shows the Module tab of the Option dialog. The various aspects of the dialog are discussed in detail in the remainder of this section.


Figure 9.14. The Module tab of the Options dialog.

Code Color, Fonts, and Sizes

Access 95 allows you to customize code colors, font, size, and tab width within the coding environment. It lets you specify the foreground and background colors for the Code window text, selection text, syntax error text, comment text, keyword text, and more. You can select from any of the Windows fonts and sizes for the text within the Code window. Finally, you can identify how many characters your text is indented each time the Tab key is pressed.


Most programmers prefer a tab width of three over the default width of four. This slightly smaller tab width improves the readability of your code by consuming less screen real estate each time the Tab key is pressed.

Coding Options

The coding options available to you include Auto Indent, Auto Syntax Check, Break on All Errors, Require Variable Declaration, and Compile On Demand (discussed in the previous section). The Auto Indent feature invokes the automatic indenting of successive lines of code. This means that when you indent one line, all other lines are indented to the same position until you specify otherwise.

The Auto Syntax Check feature determines whether Access performs a syntax check each time you press Enter after typing a single line of code. Many developers find this option annoying. Many times you type a line of code and notice a typo in a previous line of code. You want to rectify the error before you forget. You move off the incomplete line of code that you are typing, only to receive an error message that your syntax is incorrect. Although I find this aspect of Auto Syntax Check annoying, I would still rather identify any syntax errors sooner rather than later.

The Break on All Errors feature is an important feature when you have added error handling to your code. If the feature is turned on, you get Access's error handling rather than your own whenever an error is encountered. I turn this feature on when I am debugging an application that I wrote. Nothing is more annoying than seeing your own error message when you are debugging your own code. The following routine provides an example:

Sub Test()
   On Error GoTo Test_Err
   Debug.Print 5 / 0
   MsgBox "I am Here"
   Exit Sub
Test_Err:
   MsgBox "Oops"
   Exit Sub
End Sub

If the Break on All Errors box is not checked, a message box saying Oops appears when this code is run. If the Break on All Errors box is checked, Access's divide-by-zero error message appears.

The Require Variable Declaration option is a must. If this option is turned on, all variables must be declared before they are used. This important feature, when set, places the Option Explicit line in the Declarations section of every module. You are then forced to declare all variables before they are used. Many innocent typos are identified by the compiler at compile time rather than by your users at runtime.


All the customization options that have been discussed apply to the entire Access environment. This means that, when set, they affect all your databases.

Practical Examples: Putting Advanced Techniques to Use in the Time and Billing Application

The time and billing application will implement all that you have learned throughout this chapter. The following examples cover the use of Null, the DoCmd Object, intrinsic constants, and type structure.

Examples of Null, the DoCmd Object, and Intrinsic Constants

The following event routine is used to view all the projects associated with the selected client. It illustrates the importance of the ability to work with Nulls, the DoCmd object, and intrinsic constants.

Private Sub Projects_Click()
On Error GoTo Err_Projects_Click
    If IsNull(Me![ClientID]) Then
        MsgBox "Enter client information before viewing projects form."
    Else
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        DoCmd.OpenForm "frmProjects", , , "[ClientID]=" & [ClientID]
    End If
Exit_Projects_Click:
    Exit Sub
Err_Projects_Click:
    MsgBox Err.Description
    Resume Exit_Projects_Click
End Sub

The routine first invokes error handling (discussed in Chapter 17, "Handling Those Dreaded Runtime Errors"). The routine then tests to ensure that a ClientID has been entered by using the IsNull function. The IsNull function returns a True if the value in the ClientID control is Null. If the value contained within the ClientID control is Null, an error message is displayed. If the ClientID control contains a non-Null value, two methods are performed on the DoCmd object.

The first method performed on the DoCmd object is the DoMenuItem method. This method receives several parameters: menu bar, menu name, command, subcommand, and version. In the event routine, the menu bar is the Form Menu bar, the menu name is the Records menu, the command is Save Record. There is no subcommand, and the version is version 7.0. The use of intrinsic constants makes this code more readable.

The second method performed on the DoCmd object is OpenForm. This method opens the form called frmProjects. Two optional parameters, View and FilterName, are omitted. The fourth parameter, the WhereCondition, is set to the ClientID that is displayed on the Client form.

An Example of the Use of a Type Structure

Many parts of the time and billing application require the company information that is stored in the table tblCompanyInfo. It would be inefficient to read the data from this table each time the application requires it. It would be much more efficient to read this data once, when the application loads, and store it in a type structure. Because it remains in memory at all times, you can efficiently retrieve it whenever needed. The type structure is defined and a Public Type variable based on the type structure is declared in the Declarations section of a module. It looks like this:

Type CompanyInfo
    SetUpID As Long
    CompanyName As String * 50
    Address As String * 255
    City As String * 50
    StateProvince As String * 20
    PostalCode As String * 20
    Country As String * 50
    PhoneNumber As String * 30
    FaxNumber As String * 30
    DefaultPaymentTerms As String * 255
    DefaultInvoiceDescription As String
End Type
Public typCompanyInfo As CompanyInfo

A subroutine is invoked when the client form (your startup form) is first loaded. This routine populates all the elements of the type structure. The routine looks like this:

Sub GetCompanyInfo()
    Dim db As DATABASE
    Dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblCompanyInfo", dbOpenSnapshot)
    typCompanyInfo.SetUpID = rs!SetUpID
    typCompanyInfo.CompanyName = rs!CompanyName
    typCompanyInfo.Address = rs!Address
    typCompanyInfo.City = rs!City
    typCompanyInfo.StateProvince = rs!StateProvince
    typCompanyInfo.PostalCode = rs!PostalCode
    typCompanyInfo.Country = rs!Country
    typCompanyInfo.PhoneNumber = rs!PhoneNumber
    typCompanyInfo.FaxNumber = rs!PhoneNumber
    rs.Close
    db.Close
End Sub

Don't be concerned with the recordset handling included in this routine. Instead, notice that the value from each field in the first (and only) record of the tblCompanyInfo table is being loaded into the elements of the Global Type variable. Here's an example of how the Type variable is used:

Sub PopulateControls()
    txtCompanyName.Value = Trim(typCompanyInfo.CompanyName)
    txtAddress.Value = Trim(typCompanyInfo.Address)
    txtCityStateZip.Value = Trim(typCompanyInfo.City) & ", " & _
        Trim(typCompanyInfo.StateProvince) & _
        "  " & Format(Trim(typCompanyInfo.PostalCode), "!&&&&&-&&&&")
    txtPhoneFax.Value = "PHONE: " & _
        Format(Trim(typCompanyInfo.PhoneNumber), "(&&&)&&&-&&&&") & _
        "       FAX: " & _
        Format(Trim(typCompanyInfo.FaxNumber), "(&&&)&&&-&&&&")
End Sub

This routine is called by the activate event of rptInvoice. It populates four different controls on the form with the company information retrieved from the elements of the Global Type variable.

Summary

As an Access developer, you spend much of your time writing VBA code. Knowing the tricks and tips of the trade and understanding the more advanced aspects of the language will save you much time and help you streamline your application code.

This chapter began by showing you tricks and tips that you can use to help you effectively navigate the VBA environment. It then delved into more advanced aspects of the VBA language such as the DoCmd object, user-defined types, constants, and arrays. The chapter illustrated the important difference between passing parameters by reference and passing them by value, and it covered other advanced function techniques such as optional and named parameters. Other important topics covered in this chapter include Property Let and Property Get, empty versus null, and compilation options. An understanding of all of these valuable aspects of the VBA language will help you get the most out of the code that you write.

Previous Page TOC Index Next Page Home