The Visual Basic for Applications (VBA) language is extremely rich and comprehensive. VBA is covered throughout this book as it applies to different topics, but this chapter focuses on some advanced application development techniques. Some of the techniques are tips and tricks of the trade; others are advanced aspects of the language that weren't covered in Chapter 8, “VBA 101: The Basics of VBA.” An understanding of the concepts in this chapter is essential to learning about the more advanced topics covered in the rest of this book.
Using the tips and tricks of the trade (many of which are highlighted in this chapter) effectively can save you hours of time. 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, get help on VBA functions and commands, and split the Code window so that two procedures can be viewed simultaneously.
If you're a developer who's new to VBA, you might be confused by the coding environment. The Code window has two combo boxes, shown in Figure 9.1. The combo box on the left lists objects. For a form or report, the list includes all its objects; for a module, which has no objects, only (General)
appears.
The combo box on the right lists all the event procedures associated with a particular object. Figure 9.2 shows all the event procedures associated with a command button. Notice that the Click
event is the only one that appears in bold because it's the only event procedure that has been coded.
As you become more advanced with VBA, you can create libraries of VBA functions and subroutines. When you're viewing a call to a particular subroutine or function, you usually 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
Say 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. This immediately moves you to the EvaluateAge
procedure. Ctrl+Shift+F2 takes you back to the routine you came from (in this case, cmdOkay_Click
). This procedure works for both functions and subroutines.
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 change. 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 box, shown in Figure 9.3, choose Edit | Find or use Ctrl+F.
Type the text you want to find in the Find What text box. Notice that you can search in the Current Procedure, Current Module, Current Database, or Selected Text. The option Find Whole Word Only doesn't find the text if it's part of another piece of text. For example, if you check Find Whole Word Only, then search for “Count,” VBA doesn't find “Counter.” Other options include toggles for case sensitivity and pattern matching.
You use the Replace dialog box to search for text and replace it with another piece of text. (See Figure 9.4.) It offers all the features of the Find dialog box, but also allows you to enter Replace With text. In addition, you can select Replace or Replace All. Replace asks for confirmation before each replacement, but Replace All replaces text without this prompt. I recommend you take the time to confirm each replacement because it's all too easy to miscalculate the pervasive effects of a global search-and-replace.
A very useful but little-known feature of VBA is getting context-sensitive help while coding. With your cursor placed anywhere in a VBA command or function, press the F1 key to get context-sensitive help on that command or function. Most of the help topics let you 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 using the With...End With
construct. If you click on Example
, a specific example of the construct appears that you can copy and place into a module. (See Figure 9.6.) This feature is a great way to learn about the various parts of the VBA language.
The VBA Code window can be split so that you can look at two routines in the same module at the same time. This option is useful if you're trying to solve a problem involving two procedures or event routines in a large module. An example of a split Code window is shown in Figure 9.7. To split your Code window, choose Window | Split.
Notice the splitter. By placing your mouse cursor on the gray splitter button just above the Code window's vertical scrollbar and clicking and dragging, you can size each half of the window. The window can be split into only two parts. After it has been split, you can use the Object and Procedure drop-down lists to navigate to the procedure of your choice.
Another way to view multiple routines at the same time is to work in Full Module view (briefly discussed in Chapter 8). You use it to view all the code in a module as though you were in a normal text editor. To activate Full Module view so that it appears automatically each time you open a Code window, choose Tools | Options. Click on the Module tab, then place an × in the Full Module View checkbox under Window Settings. 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 does. Full Module View allows you to view multiple contiguous code routines, but splitting the Code window lets you view two nonconsecutive routines in the same module.
The Access 97 coding environment allows you to create placemarkers—called bookmarks—so that you can easily return to key locations in your modules. To add a bookmark, right-click on the line of code where the bookmark will be placed and choose Toggle | Bookmark or either Bookmarks | Bookmark from the Edit menu. You can add as many bookmarks as you like.
To navigate between bookmarks, choose Edit | Bookmarks | Next Bookmark or Edit | Bookmarks | Previous Bookmark. A bookmark is a toggle; to remove one, you simply choose Toggle | Bookmark from the shortcut menu or Bookmarks | Bookmark from the Edit menu. If you want to clear all bookmarks, choose Edit | Bookmarks | Clear All Bookmarks. All bookmarks are automatically removed when you close the database.
It's easy to test procedures from the Module window in Access 97—simply place your cursor in the routine you want to execute, then press the F5 key or click the Go/Continue button on the toolbar. The procedure you're in will execute as though you had called it from code or from the Immediate pane of the Debug window.
The Access environment is rich with objects that have built-in properties and methods. By using VBA code, you can modify the properties and execute the methods. One of the objects available in Access is the DoCmd object, used 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 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 that can be found in Help, but some don't. They are AddMenu
, MsgBox
, RunApp
, RunCode
, SendKeys
, SetValue
, StopAllMacros
, and StopMacro
. The SendKeys
method is the only one that has any significance to you as a VBA programmer. The remaining macro actions either have no application to VBA code or can be performed more efficiently by using VBA functions and commands. The VBA language includes a MsgBox
function, for example, that's far more robust than its macro action counterpart.
Many of the DoCmd methods have optional parameters, such as the OpenForm
method shown in Figure 9.8. If you don't supply an argument, its default value is assumed. You must use commas as placemarkers to designate the position of missing arguments, as shown here:
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 is the name of the form, a required parameter. The second and third parameters have been omitted, meaning that you're 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, which has been designated as the records in which the OrderAmount is greater than 1,000. The remaining four parameters haven't been referred to, so default values are used for these parameters.
Named parameters, covered later in this chapter, can greatly simplify the preceding syntax. With named parameters, the arguments don't need to be placed in a particular order, nor do you need to worry about counting commas. The preceding syntax can be changed to the following:
DoCmd.OpenForm FormName:="frmOrders", _ WhereCondition:=[OrderAmount]>1000
For more about named parameters, see the “Advanced Function Techniques” section of this chapter.
A user-defined type, known as a struct or record, allows you to create a variable containing 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's excellent for information that needs to be temporarily tracked at runtime. Because it's in memory, it can be quickly and efficiently read from and written to.
You declare a user-defined type by using a Type
statement that must be placed in the module's Declarations section. Types can be declared as Public
or Private
, but types can't be placed in Form or Report modules.
A Type variable is an instance of the type in memory; it must be declared before you can use the type. To declare a Type variable, 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 determine its scope. The same rules for 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 module's General section, it's visible to all routines in that module. If you place it in a subroutine or function, it's local to that particular routine:
Dim typTimeCardData As TimeCardInfo
After a Type variable has been declared, you can store data into each of its elements. The following code stores information from the frmTimeCardHours form into a Type variable called typTimeCardData
, declared in basDataHandling:
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 code for this chapter can be found in the CHAP9EX.MDB database on the book's CD-ROM. 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.
To retrieve information from your Type variable, simply refer to its name, followed by a period, 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
A constant is a meaningful name given to a meaningless number or string. Constants can be used only for values that don't change at runtime. A tax rate or commission rate, for example, might be constant throughout your application. There are three types of constants in Access:
Symbolic
Intrinsic
System-defined
Symbolic constants, created by using the Const
keyword, 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'll make the change in only 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 supplied by Microsoft Access, Visual Basic, and Data Access Objects (DAO). You can also use constants provided by any object libraries you're using in your application.
There are only three system-defined constants, available to all applications on your computer: True
, False
, and Null
.
As mentioned, a Symbolic constant is declared by using the Const
keyword. A constant can be declared in a subroutine or function or in the General section of a Form or Report module. Unlike in previous versions of Access, constants can be strong-typed in Access 97. The naming convention for constants is to use a suitable scoping prefix, the letter c to indicate you're 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 a module's Declarations section, creates a Private constant called mccurTaxRate
and sets it equal to .0875
. Here's how the constant is used in code:
Function TotalAmount(curSaleAmount As Currency) TotalAmount = curSaleAmount * mccurTaxRate End Function
This routine multiplies the curSaleAmount
, received 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 example is that the code is more readable than TotalAmount = curSaleAmount * .0875
would be.
Just like regular variables, user-defined constants have scope. In the preceding example, you created a Private constant. The following statement, when placed in a module's Declarations section, 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 better understand the benefits of a Public constant, say you have many functions and subroutines all making reference to the constant pccurTaxRate
. Imagine what would happen if the tax rate were to change. If you hadn't used a constant, you would need to search your entire application, replacing the old tax rate with the new tax rate. However, 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 can't be modified at runtime. If you try to modify the value of a constant, you get this VBA compiler error:
Variable Required—can't assign to this expression
Figure 9.9 illustrates this message box. You can see that an attempt was made to modify the value of the constant called pccurTaxRate
, which resulted in a compile error.
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, then modify the variable if necessary. If you choose, you can write the new value back to the table.
Microsoft Access declares a number of intrinsic constants that can be used in Code, Form, and Report modules. Because they're reserved by Microsoft Access, you can't modify their values or reuse their names; however, 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 isn't likely to change the constant's name. All intrinsic constants appear in the Object Browser; to activate it, simply click the Object Browser tool on the Visual Basic toolbar. To view the constants that are part of the VBA language, select VBA from the Object Browser's Project/Library drop-down list. Click Constants in the Classes list box, and a list of those constants are displayed in the “Members of 'Constants'” list box. (See Figure 9.10.)
In the list shown in Figure 9.10, all the 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 from the Project/Library drop-down list and Constants from the Classes list box. To view the Data Access Object constants, select DAO from the Project/Library drop-down list. The constants are categorized by their function into various classes (for example, DAOCollatingOrderConstants
, DAOCursorDriverConstants
). Select the appropriate class from the Classes list box, and its members appear in the “Members of…” list box.
Another way to view constants is within the context of the parameter you're working with in the Code window. Right-click after the name of a parameter and select List Constants to display the constants associated with the parameter. This feature is covered in detail in Chapter 8.
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, but all the elements must be of the same data type. Arrays help make coding more efficient. It's easy to loop through each element of an array, performing some process on each element. Arrays have a lower bound, which is zero by default, and an 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.
When declaring a fixed array, you give VBA the upper bound and the type of data it will contain. The following code creates an array that holds six string variables:
Dim astrNames(5) As String
Fixed means that this array's size can't 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) = "Alexis" 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, which is (5)
. Because the array is made up of strings, the last two elements of the array contain zero-length strings. If the array were composed 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) = "Alexis" 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.
Often, you don't know how many elements your array needs. In this case, you should consider declaring a dynamic array, which can be resized at runtime. Using this type of array can 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. However, if you aren't sure how many elements your array will contain, preallocating a huge amount of memory can be quite inefficient.
To create a dynamic array, you declare it without assigning an upper bound. You do this by omitting the number between the parentheses when declaring the array, as shown in this 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
However, there's a potential problem 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) = "Alexis" 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 contain values. This problem can be avoided by using 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) = "Alexis" 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 cause problems if you're 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 of choosing the right type of array for each situation. Fixed arrays are the way to go when the number of elements doesn't vary widely, but dynamic arrays should be used when the number varies widely and you're sure you have enough memory to resize even the largest possible arrays.
The advanced function techniques covered in this section allow you to get the most out of the procedures you build. First, you learn the difference between passing your parameters by reference and passing them by value, and see that the default method of passing parameters isn't necessarily the most prudent method.
The second part of this section shows you how to work with optional parameters, which help you build flexibility into your functions. They let you 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 can build much more robust and easy-to-use functions.
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, in which a variable can't be changed by any routine other than the one it was declared in. 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, can't 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 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're aware of what you're doing and why you're doing it, there's no problem with passing a parameter by reference.
I use a special technique to help readers of my code see whether I'm passing parameters by reference or by value. When passing parameters by reference, I refer to the parameters by using the same name in both the calling routine and the actual procedure that I'm calling. On the other hand, when passing parameters by value, I refer to the parameters by using different names in the calling routine and in the procedure that's being called.
Access 97 allows you to use optional parameters. In other words, it isn't necessary to know how many parameters will be passed. The ReturnInit
function in the following code receives the second two parameters as optional, 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, 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, but here are some other possible choices:
Insert default values when parameters are missing
Somehow accommodate for the missing parameters in your code
The following two examples illustrate how to carry out these two alternatives:
Example 9.1. Inserting default values when parameters are missing.
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"
. Now take a look at Listing 9.2, which illustrates another method of handling missing parameters.
Example 9.2. Accommodating for missing parameters in your code.
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.
In all the examples you've seen so far, the parameters to a procedure have been supplied positionally. Named parameters allow you to supply parameters without regard for their position, which is particularly useful with procedures that receive optional parameters. Take a look at this example:
strName = ReturnInit("Bill",,"Gates")
Because the second parameter isn't 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're dealing with several optional parameters. The following example greatly simplifies the process of passing the parameters and also better documents what's 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 shown in the following example:
strName = ReturnInit3(strLName:= "Gates",strFName:="Bill")
This call to the ReturnInit3
function yields the same results as 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 thorough knowledge of the function being called, this method of specifying parameters has one important disadvantage: If the author of the function modifies a parameter's name, all routines that use the named parameter when calling the function will fail.
With property procedures, you can 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 give you more flexibility in creating your applications; you can create reusable objects that expose properties to other objects.
Custom properties are Public by default and are placed in Class, Form, or Report modules, making them visible to other modules in the current database. They aren't visible to other databases.
The Property Let
routine defines a property procedure that assigns a value to a user-defined object's property. 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's written to it. With 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 was passed to it. You might be thinking this code looks just like a subroutine, and you're somewhat correct. It's a special type of subroutine that executes automatically in response to the change in a custom property's value. The following line of code causes the code in the Property Let
to execute:
Me.TextEnabled = False
The value False
is received as a parameter to the Property Let
routine, so 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 Let
sets the value of a custom property, but Property Get
defines a property procedure that retrieves a value from a user-defined object's property. 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's 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 form's TextBoxValues property. The Property Get
routine automatically executes whenever the code tries to retrieve the value of the property. This routine can be executed by retrieving the property from anywhere in the database.
Access 97 offers two types of modules: standard modules and class modules. Access 95 introduced class modules for forms and reports, but Access 97 is the first version of Access to offer the ability to create class modules from the Database window. To create a class module, simply choose Insert | Class Module.
A class module is similar to a code module. The subroutines and functions in the class module become the methods of the class. The Property Let
and Property Get
routines become the properties of the class, and the class module's name becomes the name of the custom object. A class module is a great way to encapsulate related functions into a portable, self-contained object.
Say that you regularly open databases and recordsets and traverse those recordsets by using code. You have decided that you want to simplify performing all the functions needed for these tasks. By building class modules, you can make accessing the table data much simpler. The class called clsRecordsets
has eight methods:
OpenDB:
Opens the database
OpenRS:
Opens the recordset
GoTop:
Moves to the top of the recordset
GoPrev:
Moves to the previous record
GoNext:
Moves to the next record
GoBott:
Moves to the bottom of the recordset
GetFieldValue:
Gets the value from the first field of the current record
CleanUp:
Closes the database and recordset objects
Listing 9.3 shows you what the methods of the clsRecordsets
class look like:
Example 9.3. The methods for clsRecordsets
.
Public Function OpenDB() 'Point module level workspace variable at first workspace Set mWS = DBEngine.Workspaces(0) 'Point module level workspace variable to the database with the 'name specified in the DBName property Set mDB = mWS.OpenDatabase(mDBName) End Function Public Function OpenRS() 'Point recordet variable to the recordset name 'contained in the RSName property Set mRS = mDB.OpenRecordset(Name:=mRSName) End Function Public Function GoTop() 'Move to the first record in the recordset mRS.MoveFirst End Function Public Function GoBott() 'Move to the last record in the recordset mRS.MoveLast End Function Public Function GoPrev() 'Check for beginning of file 'If not at beginning of file, move to the 'previous record. If still not at beginning 'of file, return true. Otherwise, move next and 'return false If Not mRS.BOF Then mRS.MovePrevious If mRS.BOF Then mRS.MoveNext GoPrev = False Else GoPrev = True End If Else GoPrev = False End If End Function Public Function GoNext() 'Check for end of file 'If not at end of file, move to the 'next record. If still not at end 'of file, return true. Otherwise, move previous and 'return false If Not mRS.EOF Then mRS.MoveNext If mRS.EOF Then mRS.MovePrevious GoNext = False Else GoNext = True End If Else GoNext = False End If End Function Public Function GetFieldValue() 'Return the value from the first field of the 'current record GetFieldValue = mRS.Fields(0).Value End Function Public Function CleanUp() 'Close recordset and database objects mRS.Close mDB.Close End Function
The eight methods are explained by the comments in each method. Several methods require the values of properties contained in the class. These properties are DBName and RSName; their declarations are as follows:
Public Property Let DBName(ByVal strDBName As String) mDBName = strDBName End Property Public Property Let RSName(ByVal strRSName As String) mRSName = strRSName End Property
Notice that each Property Let
receives a string as a parameter and sets a module-level variable equal to that string. The idea is that only the properties are exposed to the outside world. The module-level variables used by the class methods are protected by the Property Let
procedures. The Property Let
procedures can easily be enhanced to validate the parameters or to manipulate their values before they're placed in the module-level variables. The following module-level variables are declared in the module's General Declarations section; they're all used by the methods of the class:
Private mWS As Workspace Private mDB As Database Private mRS As Recordset Private mDBName As String Private mRSName As String
The mWS
, mDB
, and mRS
variables are set in the class methods, and the mDBName
and mRSName
variables are set by using the Property Let
routines.
The class is used to contain all the functionality needed to access and manipulate recordset data. Once you have created the class, it's easy for a routine in a standard module to call the class methods and to set the class properties. The TraverseRecordSet
method in basRecordsets illustrates how to use the clsRecordsets
class. It looks like this:
Sub TraverseRecordSet() clsRecordsets.DBName = CurrentDb.Name clsRecordsets.RSName = "tblClients" clsRecordsets.OpenDB clsRecordsets.OpenRS Debug.Print clsRecordsets.GetFieldValue Do While clsRecordsets.GoNext() Debug.Print clsRecordsets.GetFieldValue Loop clsRecordsets.CleanUp End Sub
The routine begins by setting the DBName property of the clsRecordsets
class to the name of the current database. This has the indirect effect of setting the mDBName
variable in the class to the name of the current database. It then sets the RSName property of the class to tblClients
, which has the indirect effect of setting the mRSName
variable in the class to tblClients
. Next, the OpenDB
method of the class is issued, opening the database specified in the DBName property. The OpenRS
method of the class opens the recordset found in the RSName property. The GoNext
method of the class is called recursively until it returns a False
because no further records can be found. After the GoNext
method is called, the GetFieldValue
method is called to get the value from the first field in the record. Finally, the CleanUp
method is called to close the recordset and database object.
This example illustrates how you can create custom methods that help you through the complexities of the task at hand. Whenever you want to take advantage of the functionality supplied by the class, you simply set the class properties and call the class methods.
Empty
and Null
are values that can exist only for Variant variables. They're 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
. You can make this differentiation only with Variant variables.
Variant variables are initialized to the value of Empty
. Often, you need to know whether a value has been stored in 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's important to be able to test for Empty
in a runtime environment. This can be done by using the IsEmpty
function, which determines whether a variable has the Empty
value. The following example tests a String variable for the Empty
value:
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 has the Empty
value:
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 in it, including zero, Null
, or a zero-length string. It can become Empty
again only by storing the keyword Empty
in the variable.
Null
is a special value that indicates unknown or missing data. Null
is not the same as Empty
, nor is one Null
value equal to another one. Variant variables can contain the special value called Null
.
Often, you need to know whether specific fields or controls have ever been initialized. Uninitialized fields and controls have a default value of Null
. By testing for Null
, you can make sure fields and controls contain values.
If you want to make sure that all fields and controls in your application have data, you need to test for Nulls
. This can be done by 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 in it. It's important to know not only how variables and Null
values interact, but also how to test for Null
within a field in your database. A field contains a Null
if data hasn't yet been entered in the field and the field has no default value. In queries, you can test for the criteria "Is Null"
to find all the records in which a particular field contains a Null value. When dealing with recordsets (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 (found in the frmProjects form in CHAP9EX.MDB) loops through every control on the current form. If the control is a text box, the routine checks to see whether the value in the text box is Null
. If it is, the BackColor property of the text box is set to Aqua
; otherwise, it's set to White
.
You should know about some idiosyncrasies of Null
:
Expressions involving Null
always result in Null
. (See the next example.)
A function that's passed a Null
usually returns a Null
.
Null
values propagate through built-in functions that return variants.
The following example shows how Null
values are propagated:
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 Null
values. Notice that the result of the calculation is Null
for those records because the Null
propagated within those records.
Notice the difference if the value in 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 has four records. The PurchaseOrderNumber for the first record contains a Null
; for the third record, it contains an Empty
. Notice the different effects of the two values, as shown in Figure 9.12.
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, the effect of Null
used in a calculation is a resulting Null
. In 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 Null
values result from the concatenation.
It's very common to create a generic routine that receives any value, tests to see whether it's 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 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, which also receives two parameters. The first parameter is the variable being tested for Null
; the second is the value you want the function to return if the first parameter is determined to be Null
. The routine combines the Immediate If (IIf
) function and the IsNull
function to evaluate the first parameter and return the appropriate value.
Microsoft Access gives you a few alternatives for compilation. Understanding them can help you decide whether compilation speed or trapping compilation errors is more important to you.
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 up the compilation process, it can also leave you wondering whether you have a hidden timebomb lurking somewhere in 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), errors aren't identified until the offending code is accessed.
To disable the Compile On Demand feature, choose Tools | Options. Click the Module tab and remove the check from Compile On Demand. You might notice some degradation in performance each time your code compiles, but this is time well spent.
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, but the code in 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.
The Compile All Modules and Compile and Save All Modules features are found under the Debug menu. These menu items, when selected, compile every module in the database, regardless of whether it's open.
The Compile and Save All Modules selection takes an additional step and saves all code in the database in its fully compiled state. This procedure not only ensures that the modules compile successfully, it makes sure they're saved in their compiled state so that they don't need to be compiled again when the application is run.
Before Access 95, Access programmers had little opportunity to customize the look and behavior of the interactive development environment (IDE). Fortunately, Access 95 and Access 97 offer major improvements in this area. To view the environment options, choose Tools | Options, then click the Module tab. Figure 9.14 shows the Module tab of the Options dialog box; its different aspects are discussed in detail in the rest of this section.
In Access 97, you can customize code colors, font, size, and tab width within the coding environment. You can also 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 in the Code window. Finally, you can specify how many characters your text is indented each time the Tab key is pressed.
The coding options available to you include Auto Indent, Auto Syntax Check, Require Variable Declaration, Compile On Demand (discussed in the previous section), Auto List Members, Auto Quick Info, and Auto Data Tips. 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. It's not uncommon to type a line of code and notice a typo in a previous line of code, so you want to rectify the error before you forget. You move off the incomplete line of code you're typing, only to get 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 instead of later.
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're then forced to declare all variables before they're used. Many innocent typos are identified by the compiler at compile time rather than by your users at runtime.
The Auto List Members option determines whether the List Properties/Methods and List Constants features are automatically invoked as you type code in the Code window. They help you in your coding endeavors by presenting a valid list of properties, methods, and constants. For more about these features, see Chapter 8.
The Auto Quick Info feature determines whether the syntax of a procedure or method is automatically displayed. If this option is selected, the syntax information is displayed as soon as you type a procedure or method name followed by a space, period, or opening parenthesis.
The Auto Data Tips feature is used when you're debugging. It displays the current value of a selected value when you place your mouse pointer over the variable in Break mode. This feature is discussed in Chapter 16, “Debugging: Your Key to Successful Development.”
The Time and Billing application will put into practice all that you have learned throughout this chapter. The following examples cover the use of Null
, the DoCmd object, intrinsic constants, and type structure.
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 Null
values, the DoCmd object, and intrinsic constants.
Private Sub cmdProjects_Click() On Error GoTo Err_Projects_Click If IsNull(Me![txtClientID]) Then MsgBox "Enter client information before viewing projects form." Else DoCmd.RunCommand Command:=acSaveRecord DoCmd.OpenForm "frmProjects", , , "[ClientID]=" & [txtClientID] 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”), then uses the IsNull
function to test whether a ClientID has been entered. The IsNull
function returns a True
if the value in the txtClientID control is Null
. If it is, an error message is displayed. If the txtClientID control contains a non-Null
value, two methods are performed on the DoCmd object.
The first method performed on the DoCmd object is the RunCommand
method. This method receives the constant associated with the name of the menu command you want to execute. The use of intrinsic constants makes this code more readable, and the RunCommand
method makes it much easier now to call menu commands from code.
The second method performed on the DoCmd object is OpenForm
, which opens the frmProjects form. Two optional parameters, View
and FilterName
, are omitted. The fourth parameter, the WhereCondition
, is set to the ClientID that's displayed on the Client form.
Many parts of the Time and Billing application require the company information stored in the tblCompanyInfo table. It would be inefficient to read the data from this table each time the application needs 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 a module's Declarations section. 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.
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 has shown you tricks and tips you can use to help you effectively navigate the VBA environment and delved into more advanced aspects of the VBA language, such as the DoCmd object, user-defined types, constants, and arrays. You have seen the important difference between passing parameters by reference and passing them by value, and learned about other advanced function techniques, such as optional and named parameters. Other important topics covered in this chapter include Property Let
and Property Get
, classes modules, collections, Empty
versus Null
, and compilation options. Understanding these valuable aspects of the VBA language will help you get the most out of the code you write.
3.136.97.64