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

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

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

Contents

• Controlling Program Flow

• Documenting Your Code

• Error Debugging

• Error Handling

• Summary

• Exercises

• Additional Resources

Controlling Program Flow

So far, you have learned how to create one or more modules to contain your code as well as how to create procedures within those modules. Now you will see how to write commands within procedures that control the flow of the program. The flow of a program can be controlled through decision-making, loops, and in other ways. We’ll start by looking at writing decision-making code.

Decision Making

VBA has various statements you can use to make decisions in your code and then take an appropriate action depending on the result. The following gives you several decision-making examples to illustrate this concept.

If...Then

If...Then statements can be used to make decisions and perform certain actions depending on whether the conditions are met.

The syntax for an If...Then statement is:

If CONDITION Then
' code if the condition is met
End If

The syntax for an If...Then...Else statement is:

If CONDITION Then
' code if the condition is met
Else
' code if the condition is not met
End If

The syntax for an If...Then...ElseIf statement is:

If CONDITION Then
' code if the condition is met
ElseIf CONDITION Then
' code if the ElseIf condition is met
End If

ElseIf and Else can be used together as part of the same If...Then statement, or they can be used separately, as illustrated in the previous example. If...Then statements can also be nested within each other, as shown in the following example:

If intCounter < 0 Then
  ' reset intCounter to 0
  intCounter = 0
ElseIf intCounter > 0 and intCounter < 50 Then
  If intCounter = 50 Then
    Msgbox "The maximum number of sessions has been reached."
  Else
    Msgbox "There are still sessions remaining."
  End If
  intCounter = intCounter + 1
End If

Try It Out: Creating TestIfStatement Procedure

Now, it’s your turn to create a new procedure and makes uses of If... Then statements.

1. In the modBusinessLogic standard module, add the following TestIfStatement procedure:

   Sub TestIfStatement()
   ' declare variable to store sales tax value
   Dim curSalesTax As Currency
   ' call function to calculate sales tax
   curSalesTax = CalculateSalesTax(500, 0.05)
   
   ' evaluate sales tax and write proper message
   ' to debug window
   If curSalesTax <= 10 Then
     Debug.Print "You are lucky - the amount of tax is nominal."
   ElseIf curSalesTax > 10 And curSalesTax <= 50 Then
     Debug.Print "The amount of sales tax could have bought you a nice meal."
   Else
     Debug.Print "You bought a really nice item for that tax amount."
   End If
   End Sub
   

2. From the Immediate Window, run the new TestIfStatement procedure. The result is displayed in Figure 2-27, in the Immediate Window.

   Figure 2-27. Results displayed in the Immediate Window

   

    

How It Works

The TestIfStatement procedure calls the CalculateSalesTax function that you created previously and evaluates the result in an If...Then statement to determine which message to display in the Immediate Window. The procedure begins by declaring a variable to store the calculated sales tax value.

Sub TestIfStatement()
' declare variable to store sales tax value
Dim curSalesTax As Currency

The curSalesTax variable is assigned to the result of the CalculateSalesTax function. In other words, the CalculateSalesTax procedure is called with the hard-coded values, and the resulting value is placed in the curSalesTax variable.

'call function to calculate sales tax
curSalesTax = CalculateSalesTax(500, 0.05)

An If... statement is then used to evaluate the curSalesTax value and write a particular message to the Immediate Window, depending on how the expression is evaluated.

' evaluate sales tax and write proper message
' to debug window
If curSalesTax <= 10 Then
  Debug.Print "You are lucky - the amount of tax is nominal."
ElseIf curSalesTax > 10 And curSalesTax <= 50 Then
  Debug.Print "The amount of sales tax could have bought you a nice meal."
Else
  Debug.Print "You bought a really nice item for that tax amount."
End If
End Sub

In the current example, because a value of 500 is specified for the sales amount parameter of the CalculateSalesTax function and .05 is specified for the tax rate parameter, the resulting tax amount is 25. Thus, when you run the procedure, the Immediate Window displays the corresponding message.

IIf

The IIf function can be used to return one of two possible values depending on whether the condition being tested is true or false. The syntax for the IIf function is:

IIf(expr, truepart, falsepart)

Here is an example:

strResult = IIf(intWeight > 25, "Heavy", "Light")

If the intWeight value is greater than 25, then the IIf function will return the value “Heavy” and assign it to the strResult variable. Otherwise, the strResult variable will be assigned to the value “Light”.

Conditional If

Conditional If...Then statements enable you to selectively compile and execute certain blocks of code. Conditional If statements can be used in various scenarios, such as when you want certain blocks of code to execute during testing but not in the release version, or when you’re distributing your application in different regions and want certain code to apply in some regions but not others. The following is an example of the general syntax:

#If conLanguage = "English" Then
' The code specific to the English version of the software goes here.
#ElseIf conLanguage = "Spanish" Then
' The code specific to the Spanish version of the software goes here.
#Else
' The code specific to the remaining versions of the software goes here.
#End If

Select...Case

Another way to implement decision making in your VBA code is to use a Select...Case statement. Select...Case statements can be used to easily evaluate the same variable multiple times and then take a particular action depending on the evaluation.

The syntax for a Select...Case statement is:

Select Case VARIABLE
Case VALUE1
' code to run if VARIABLE equals Value1
Case VALUE2
' code to run if VARIABLE equals Value2
Case Else
' code to run for remaining cases
End Select

Try It Out: Create the TestCaseStatement Procedure

Let’s create a new procedure that makes use of a Select...Case statement to illustrate this in further detail.

1. Add the following TestCaseStatement procedure to the modBusinessLogic standard module.

   Sub TestCaseStatement(strCountry As String)
   ' evaluate the value of strCountry and display applicable result in debug window
   Select Case strCountry
   Case "Italy"
     Debug.Print "The buildings dating back to 400 BC are incredible."
   Case "China"
     Debug.Print "Great bargains for shoppers."
   Case "Russia"
     Debug.Print "A beautiful country with a growing democracy."
   Case "Germany"
     Debug.Print "Fantastic food - you will not starve there."
   Case Else
     Debug.Print "You should travel more when you have the chance."
   End Select
   End Sub
   

2. Run the TestCaseStatement procedure from the Immediate Window and specify “Italy” as the parameter, as shown in Figure 2-28. Click Enter to run the procedure. The resulting value is then displayed in the Immediate Window.

   Figure 2-28. Immediate Window with parameter and results

   

    

How It Works

The TestCaseStatement procedure accepts a string variable called strCountry as a parameter.

Sub TestCaseStatement(strCountry As String)

The strCountry variable is evaluated in the Select...Case statement to determine which one of a variety of messages should be displayed.

' evaluate the value of strCountry and display applicable result in debug window
Select Case strCountry
Case "Italy"
  Debug.Print "The buildings dating back to 400 BC are incredible."
Case "China"
  Debug.Print "Great bargains for shoppers."
Case "Russia"
  Debug.Print "A beautiful country with a growing democracy."
Case "Germany"
  Debug.Print "Fantastic food - you will not starve there."
Case Else
  Debug.Print "You should travel more when you have the chance."
End Select

Because you ran the procedure using “Italy” as the value for Country, the message for Italy was displayed in the Immediate Window. Try running the procedure with different values for Country and see how the results differ.

Loops

Various types of loops can be used to iterate through a particular action until a particular scenario occurs. For example, loops can be used to run particular code a specified number of times. Now you will learn about a few different ways to declare loops using VBA code.

For...Next and For Each...Next

You can use For...Next loops to run the same code a particular number of times. For Each...Next loops can be used to run the same code for each object in a particular collection, such as for each form in the Forms collection. The basic syntax for a For...Next loop is shown here.

For counter = start To end
' statements go here
Next [counter]

The basic syntax for a For Each...Next loop is shown here.

For Each element In Collection
' statements go here
Next [element]

Try It Out: Creating a For...Next Loop

Let’s jump right in by creating our own For...Next loop to see how this works.

1. Place this code for the TestLoop procedure in the modBusinessLogic standard module.

   Sub TestLoop()
   
   ' declare variable to store Counter
   Dim intCounter As Integer
   
   ' increment intCounter from 1 to 5 and
   ' display output in debug window
   For intCounter = 1 To 5
     Debug.Print intCounter
   Next intCounter
   End Sub
   

2. Run the TestLoop procedure from the Immediate Window. The results of running the procedure are shown in Figure 2-29.

   Figure 2-29. Immediate Window with procedure name and results

   

    

How It Works

The TestLoop procedure begins by declaring an intCounter variable to store the number of times the loop has been iterated.

Sub TestLoop()
' declare variable to store Counter
Dim intCounter As Integer

The For...Next statement comes next, with code specifying that the loop should run with intCounter starting at 1 and repeat multiple times until intCounter reaches 5. The Next statement increments the intCounter value by one. When intCounter reaches 5, the Debug.Print statement will execute for the last time and then the loop is exited.

' increment intCounter from 1 to 5 and
' display output in debug window
For intCounter = 1 To 5
  Debug.Print intCounter
Next intCounter

Do...Loop

The Do...Loop statement can be used instead of For...Next to accomplish the same purpose. The two types of Do...Loops are Do...While and Do...Until. Do...While may never run any statements if the condition is not initially true, while Do...Until will always run at least once.

The generic syntax for Do...Loop is shown here.

Do [{While | Until} condition]
' statements go here
Loop

Or, you can use this syntax:

Do
' statements go here
Loop [{While | Until} condition]

The following code uses a Do...While statement to accomplish the same result as the For...Next loop described previously.

Do While intCounter <= 5
  Debug.Print intCounter intCounter = intCounter + 1
Loop

An example of a Do Until loop is shown here.

Do Until intCounter = 6
  Debug.Print intCounter intCounter = intCounter + 1
Loop

Caution
If the condition in a Do Until statement is never met, then the loop is known as an infinite loop, which will execute indefinitely.

While...Wend

The While...Wend statement executes repeatedly while a certain condition is met. When the condition is no longer met, the loop terminates. Here is an example:

intCounter = 1
While intCounter <= 5
  Debug.Print intCounter intCounter = intCounter + 1
Wend

Documenting Your Code

It is a good idea to document your code very well so that you and others will be able to understand the purpose of each procedure and why it was written in a certain way. You can document your code in several ways. The ideal way to document your code is to use a general comment section at the beginning of each procedure and to use in-line comments to describe each segment of code. Here is an example of such a general comment section.

'*****************************************************************
'*
'* Procedure Name: TestProcedure
'*
'* Purpose: The purpose of this procedure is to illustrate why
'* documenting your code is so important.
'*
'* Parameters: None.
'*
'* Date Created: September 4, 2006
'* By: Denise M. Gosnell
'*
'* Modification Log:
'* Date Explanation of Modification Modified By
'* ------------ ---------------------------------- ------------------------
'*
'******************************************************************

The preceding documentation style may be overkill if the procedure itself is only a few lines of code. Some type of general comment section should be used with all procedures, even if it is not as detailed as the one in the preceding code.

You have seen in-line comments in all the code examples throughout this chapter. Here is another example:

' display a message to the user
Msgbox "Demonstration of In-Line commenting"

It is always better to write too many comments than too few. As you gain more experience writing VBA code, you will see how difficult it can be to follow what is happening in a particular procedure. This is especially true if you go back to the code at a later date and no longer remember the details about why you wrote it a particular way, or if you are reviewing code that someone else wrote. Code comments make this process much easier.

Error Debugging

Now that I have covered several basic ways to write VBA code and to control the flow of the program, let’s look at the types of errors you may encounter, how to debug them, and how to write error handlers to handle them.

Types of Errors

The types of errors include: syntax errors, compile errors, runtime errors, and logic errors, each of which will be discussed in turn.

Syntax Errors

Syntax errors are errors that occur because the code you wrote does not comply with the required syntax. Figure 2-30 illustrates a syntax error that occurred because the As part of the Dim statement is missing.

Figure 2-30. Incomplete Dim statement and resultant error

 

Compile Errors

Compile errors are errors discovered on compilation that may have a correct code syntax, but for some reason will not compile. Figure 2-31 illustrates a compiler error that has occurred because a CalculateTax function cannot be located. In this particular example, the reason for the compile error is that the function is incorrectly listed as CalculateTax instead of CalculateSalesTax.

Figure 2-31. Misspelled function name and resultant error

 

Runtime Errors

Runtime errors occur at runtime when the code executes. Runtime errors can include errors that occur because of something that it not allowed or supposed to happen. For example, a line of code that tries to assign a variable of one data type to an incompatible value could result in a runtime error. The example in Figure 2-32 runs the CalculateSalesTax function and passes string values as parameters, instead of the required currency and double values. The result is a runtime error, which in this example is a Type Mismatch error.

Figure 2-32. Mismatched data type to value and resultant error

 

Logic Errors

Logic errors are flaws in the logic of your code. Logic errors can be the hardest to find because they are syntactically correct but can only be discovered by testing your code and ensuring it produces the desired and appropriate result.

Debugging Your Code

You certainly need the capability to debug and correct your VBA code as you encounter errors. As you may recall from Chapter 1, the Visual Basic Editor provides several ways to help you debug and correct your code. Now we’ll look at some of these methods in more detail.

Using Breakpoints to Step through Code

Breakpoints can be set on one or more lines of code. When a line of code that has an associated breakpoint is reached, code execution stops and you can then choose to Step Into, Step Over, or Step Out of the code. Selecting the Step Into option from the Debug menu will run the current line of code at the breakpoint. The Step Over option will skip the current line of code at the breakpoint and move on to the next line of code.

Try It Out: Setting and Using Breakpoints

It’s your turn to set a breakpoint and use it to step through code.

1. Navigate to the TestLoop procedure you created previously in the modBusinessLogic standard module.

2. Set a breakpoint on the Debug.Print and the Next intCounter lines of code, as shown in Figure 2-33. You can set a breakpoint by pointing and clicking the cursor just to the left of the line of code where you want to add a breakpoint.

   Figure 2-33. Breakpoints set in code

   

    

3. Next, open the Immediate Window and run the TestLoop procedure. The code will stop execution at the first breakpoint, as shown in Figure 2-34.

   Figure 2-34. Code stops running at the first breakpoint

   

    

4. While at the breakpoint, use the Immediate Window to inquire about the current value of the intCounter variable. To do so, type ? intCounter and press ENTER, as shown in Figure 2-35.

   Figure 2-35. Immediate Window with inquiry and results

   

    

5. The current value of intCounter is displayed in the Immediate Window. You should press F5 or select Run ➪ Continue to keep executing the lines of code. Execution will stop on each breakpoint.

How It Works

The breakpoint enables you to closely monitor and determine the values of variables and take other actions that will allow you to test and review your code most effectively. This example allows you to see how the value of intCounter increments as the loop repeats.

Using the Immediate Window

As you are well aware at this point, you can use the Immediate Window to call procedures and to display the results of a line of code. You can also use the Immediate Window to assign new values to variables. You have already learned that the Debug.Print statement writes output directly to the Immediate Window. The Debug.Print statement can be used for your testing purposes.

Using the Locals Window

The Locals Window can be used to see a list of all the objects and variables that are used in the current procedure. The values in the list are updated every time you suspend execution with a breakpoint or step through code.

Try It Out: Using the Locals Window

Now, let’s walk through an example of using the Locals Window.

1. Display the Locals Window by selecting View ➪ Locals Window.

2. Run the TestLoop procedure again from the Immediate Window.

3. The code execution will again stop when the first breakpoint is reached. Notice that the values in the Locals Window are updated with the current value of intCounter.

4. Press F5 or select Run ➪ Continue to keep executing the lines of code. You should see that the values of intCounter in the Locals window change as you walk through the code, as shown in Figure 2-36.

   Figure 2-36. Locals window displays the changing values as the code runs

   

    

How It Works

The Locals Window allows you to closely monitor and determine values of variables. This example allows you to see how the value of intCounter increments as the loop repeats.

Using the Watch Window

The Watch Window can be used to see a list of objects and variables in the current procedure. The Watch Window can be used to monitor the value of variables as breakpoints are encountered, just as the Immediate and Locals Windows can. The Watch Window also provides additional features, such as allowing you to add a Watch that will cause the code execution to break when a certain event or value occurs. This enables you to have the system break for you automatically when the certain event occurs without having to set a predetermined breakpoint.

Try It Out: Adding a Watch

Let’s look at an example of adding a watch using the Watch Window.

1. From the Debug menu in the Visual Basic Editor, choose Add Watch, as shown in Figure 2-37.

   Figure 2-37. Add Watch command on the Debug menu

   

    

2. In the Add Watch dialog box, specify intCounter as the Expression, TestLoop as the procedure, and modBusinessLogic as the Module.

3. Click the OK button in the Add Watch dialog box to open the Watch Window.

4. Run the TestLoop procedure again from the Immediate Window. You should see the values in the Watch Window change when the first breakpoint is encountered.

How It Works

The Watch Window allows you to closely monitor and determine values of variables and also to cause code to automatically enter a break mode when certain events happen. The example shown in Figures 2-38, 2-39, and 2-40 illustrates how the value of intCounter increments as the loop repeats, just as the prior examples did.

Figures 2-38. Setting up Add Watch for the variable intCounter

 

Figures 2-39. Value of intCounter before entering the For loop

 

Figures 2-40. Value of intCounter during the first For loop

 

Using the Call Stack

A call stack can be used to trace through the process of nested procedures. You can use the call stack window when VBA is in break mode by selecting View ➪ Call Stack. However, you have to create your own call stack if you are not in Debug Mode. You have various ways to create your own call stack, such as to write various messages to the Immediate Window as you enter certain procedures to see the order in which they were called.

Error Handling

Now that you are familiar with various types of errors that can occur in your VBA code and how to debug them, it’s time to look at how to write error-handling code in your procedures so that your application performs well for users.

Default Error Messages

You have all used applications that did not have very good error handling and found yourself getting booted out of the application without warning or faced with unfriendly messages like the ones you saw earlier in this chapter. You do not want such problems to happen when users interact with your application. Errors will always occur, but if you design error handlers correctly, at least they can be handled reasonably well.

Handling Errors with an On Error Statement

Errors can be handled in VBA procedures using the On Error statement, as I show you next.

On Error Statement

The On Error statement can be placed on your procedure to specify a section in the procedure to which the execution should jump when an error occurs. Here is an example:

Sub Test()
On Error GoTo HandleError
' normal code for the procedure goes hereExit Sub

HandleError:
' code for handling the error goes here
Exit Sub

End Sub

Tip
To make sure that your own error handler does not kick in when you are debugging your code, select Tools ➪ Options ➪ General and enable the Break On All Errors option. This will allow you to see the root of the error and not your own error handler.

Resume Statement

The Resume and Resume Next statements can be used with the On Error statement. The Resume statement will cause code execution to resume at the line that caused the error, and the Resume Next statement will cause code execution to resume at the line following the one that caused the error. Resume statements are commonly added within the error-handler routines, such as the one shown here.

Sub Test()

On Error GoTo HandleError
' normal code for the procedure goes hereExit Sub

HandleError:
' code for handling the error goes here

Resume Next
Exit Sub

End Sub

Try It Out: Creating an Error Handler

It is your turn to try your hand at creating an error handler.

1. Place the following code for a new procedure called TestError in the modBusinessLogic standard module:

   Sub TestError(intValue1 As Integer, intValue2 As Integer)
   
   On Error GoTo HandleError
   
     ' declare variable to store result
     Dim intResult As Integer
   
     ' calculate result by dividing first value by second value
     intResult = intValue1 / intValue2
   Exit Sub
   
   HandleError:
     MsgBox "An error has occurred in your application: " & Err.Description
     Exit Sub
   End Sub
   

2. Run the TestError procedure from the Immediate Window, as shown in Figure 2-41.

   Figure 2.41. Immediate Window with procedure ready to run

   

    

3. The following error message (shown in Figure 2-42) is displayed.

   Figure 2-42. Error message from the Error Handler

   

    

How It Works

The TestError procedure accepts two variables as parameters.

Sub TestError(intValue1 As Integer, intValue2 As Integer)

A variable is declared that will be used to store the result of an upcoming mathematical calculation.

'declare variable to store result
Dim intResult As Integer

The intValue1 value is then divided by the intValue2 value.

'calculate result by dividing first value by second value
intResult = intValue1 / intValue2

Because the intValue2 value you specified when calling the procedure was set to 0, a “divide by 0 error” occurs when the preceding line of code executes. Thus, the execution of code then jumps to the error handler HandleError at the bottom of the procedure.

HandleError:
  MsgBox "An error has occurred in your application: " & Err.Description
  Exit Sub
End Sub

A message box is then displayed to indicate that an error has occurred and to provide a description of the error.

The Err Object

The Err object contains information about runtime errors that occur when your code executes. Error handlers can use it to provide useful information to users or to the system to help determine what action should be taken. For example, in the error-handling example you just looked at, the Description property of the Err object (Err.Description) was used to include a description of the error to the user.

The Err object has a global scope. You need not declare it. Some examples of the properties available for the Err object include Number, Description, HelpContext, HelpFile, and Source. Number and Description are the most commonly used properties of the Err object.

Raising an Error

The Err object has a Raise method that allows runtime errors to be raised when necessary. Here is the syntax:

Err.Raise number, source, description, helpfile, helpcontext

The number argument is required for raising an error, but the other arguments are optional. Here’s an example to illustrate how you can raise an error in your code.

Sub TestErrRaise(intValue1 As Integer, intValue2 As Integer)
  On Error GoTo HandleError

  ' declare variable to store result
  Dim intResult As Integer
  If intValue2 <> 0 Then
    ' calculate result by dividing first value by second value
    intResult = intValue1 / intValue2
  ElseIf intValue2 = 0 Then
    ' raise a custom divide by 0 error
    Err.Raise vbObjectError + 513, "TestErrRaise", _
        "The second value cannot be 0."
  End If
Exit Sub
HandleError:
  MsgBox "An error has occurred in your application: " & Err.Description
  Exit Sub
End Sub

When the error is raised, the code then jumps to the error handler just as if the error had occurred in the traditional fashion.

Using the Errors Collection

The Errors collection stores the most recent ActiveX Data Objects (ADO) database errors that have occurred. Chapter 5 covers ADO in detail. For now, just be aware that the Errors collection can be used to loop through each error that was generated by one database operation. Only a few instances require that you implement such an error-handling feature.

Creating a Generic Error Handler

One way of handling errors is to create a generic error handler that gets called from every sub procedure or function.

Try It Out: Creating a Generic Error Handler

Create a generic error handler now.

1. Add the GeneralErrorHandler procedure that follows to the modBusinessLogic standard module:

   Public Sub GeneralErrorHandler(lngErrNumber As Long, strErrDesc As String,
   strModuleSource As String, strProcedureSource As String)
   Dim strMessage As String
   ' build the error message string from the parameters passed in
   strMessage = "An error has occurred in the application."
   strMessage = strMessage & vbCrLf & "Error Number: “ & lngErrNumber
   strMessage = strMessage & vbCrLf & "Error Description: " & strErrDesc
   strMessage = strMessage & vbCrLf & "Module Source: " & strModuleSource
   strMessage = strMessage & vbCrLf & "Procedure Source: " & strProcedureSource
   ' display the message to the user
   MsgBox strMessage, vbCritical
   End Sub
   

2. Add the procedure TestError2 to the modBusinessLogic standard module:

   Sub TestError2(intValue1 As Integer, intValue2 As Integer)
   On Error GoTo HandleError
     ' declare variable to store result
     Dim intResult As Integer
   
     ' calculate result by dividing first value by second value
     intResult = intValue1 / intValue2
   Exit Sub
   
   HandleError:
     GeneralErrorHandler Err.Number, Err.Description, "modBusinessLogic", _
       "TestError2"
     Exit Sub
   End Sub
   

3. Call the TestError2 procedure from the Immediate Window with the values shown in Figure 2-43.

   Figure 2-43. Immediate Window with procedure and parameters

   

    

4. The message box shown in Figure 2-44 is displayed.

   Figure 2-44. Error message from the Error Handler

   

    

How It Works

The TestError2 procedure operates nearly identically to the TestError procedure. TestError2 has two parameter variables.

Sub TestError2(intValue1 As Integer, intValue2 As Integer)

An On Error statement specifies that the HandleError section of code should be executed when an error occurs.

On Error GoTo HandleError

A variable is declared to store the result of the division, and then the values that were passed in as parameters to the procedure are used for the division. Because the values passed in cause a divide by 0 error, the code execution skips down to the HandleError section of code.

'declare variable to store result
Dim intResult As Integer
'calculate result by dividing first value by second value
intResult = intValue1 / intValue2

This time, the HandleError section includes a call to the GeneralErrorHandler procedure you just added. The error number, error description, module name, and procedure name are passed as parameters to the GeneralErrorHandler procedure.

HandleError:
  GeneralErrorHandler Err.Number, Err.Description, "modBusinessLogic", _
    "TestError2"

Now let’s turn to the GeneralErrorHandler procedure, since it runs next to handle the error. The GeneralErrorHandler procedure accepts the Error Number, Error Description, Module Source, and Procedure Source as parameters.

Public Sub GeneralErrorHandler(lngErrNumber As Long, strErrDesc As String,
strModuleSource As String, strProcedureSource As String)

A variable is declared to store the error message, and the error message is created based on the various values passed in to the procedure.

Dim strMessage As String
' build the error message string from the parameters passed in
strMessage = "An error has occurred in the application."
strMessage = strMessage & vbCrLf & "Error Number: " & lngErrNumber
strMessage = strMessage & vbCrLf & "Error Description: " & strErrDesc
strMessage = strMessage & vbCrLf & "Module Source: " & strModuleSource
strMessage = strMessage & vbCrLf & "Procedure Source: " & strProcedureSource

The error message is then displayed to the user:

'display the message to the user
MsgBox strMessage, vbCritical

This generic error handler can be called from every procedure you write, changing only those variables that specify the current module and procedure. Errors can also be logged to a table, written to a file, or e-mailed, but those options are beyond the scope of this book.

Summary

This chapter covered the basic VBA programming concepts. You learned that code can be placed in standard modules or class modules and that various coding statements can be placed in the modules for controlling the flow of execution, storing values, and so on. You also learned how to test and run your VBA code and to capture errors. These basic VBA coding techniques will serve as a foundation for all remaining chapters in the book. Now that you have a basic understanding of how to write VBA code statements, let’s move on to Chapter 3, where you will explore the world of object-oriented programming.

Exercises

1. What is a module, and what are the different types of modules?

2. What is the difference between a sub procedure and a function? When should you use each one?

3. Describe at least three ways you can control the flow of code execution. Also describe how variables fit into controlling the flow of code execution.

4. Implementing naming conventions and code documentation are both good habits to practice. What value do they provide?

5. Describe the types of errors that you might encounter in your application and how to resolve them so that the program will operate and will provide users with informative error messages.

Additional Resources

For more information, see the following resources:

• Access Developer Center

• Access 2007 Resource Center

• Microsoft Access Team Blog

• Microsoft Office Developer Center