Writing Error-Free Code

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

When you write VBA code, or script in an HTML page, it is not a question of "if" the code will contain errors but "when" those errors will be introduced and how many there will be. No matter how careful you are or how much experience you have, errors will occur. There are some things you can do to make sure errors are kept to a minimum.

The best way to reduce errors and minimize the amount of debugging you'll need to do is to follow the guidelines discussed in Chapter 3, "Writing Solid Code." If you have written maintainable code, you have also written code that is going to be much easier to debug.

Make sure that you use the Option Explicit statement in every module. The Option Explicit statement forces you to declare all variables before you use them in code. This simple step eliminates undeclared variables, which cause some of the most common, and often difficult to detect, errors in code. When you are writing Microsoft Visual Basic Scripting Edition (VBScript) code, insert Option Explicit as the first line after the first <SCRIPT> tag on the HTML page.

Avoid using the Variant data type unless you are declaring a variable and you truly do not know what kind of data it may contain at run time. Variants are slow, they take up a lot of memory, and using them when not absolutely necessary can create hard-to-find bugs in your code.

Always declare variables as a group at the beginning of each procedure and always declare each variable on a separate line. This will prevent you from inadvertently declaring a Variant variable. For example, the following line creates two Variant variables and one String variable, which is not what the developer intended:

Dim strFirstName, strLastName, strCompanyName As String

When you are creating VBA object variables, explicitly reference the object the variable represents rather than declaring the variable by using a Variant or Object data type. For example:

Dim xlSheet   As Excel.Worksheet
Dim cboUserNames  As ComboBox

When you are creating procedures that accept arguments that must fall within a specified range of data, validate the data before using it in the procedure. If an argument uses a value that represents a built-in enumerated constant, you won't have to validate the argument if you declare its data type by using the name of the enumerated constant class. If a procedure uses optional arguments, make sure that you supply the default value for each argument and make sure that the data supplied falls within the desired range. For example, the following procedure shows how to validate the data in an argument. If the data is invalid, the Raise method of the Err object is used to pass error information back to the calling procedure.

Function ErrorExample3(strTextToCheck As String) As Boolean
   
   ' This procedure illustrates using the Err.Raise method
   ' along with the vbObjectError constant to define and
   ' raise a custom error and return that error to the
   ' calling procedure. The CUSTOM_ERROR constant is defined
   ' in the Declarations section of the module by using the
   ' vbObjectError constant.
   
   Dim strTemp                As String
   Const CUSTOM_ERR_DESC      As String = "The argument passed to " & "ErrorExample3" _
                              & " is invalid - a zero-length string is not" _
                              & " permitted."
   
   If Len(strTextToCheck) = 0 Then
      Err.Raise CUSTOM_ERROR, "ErrorExample3", CUSTOM_ERR_DESC
      ErrorExample3 = False
   Else
      ' Continue processing successful case here.
      ErrorExample3 = True
      strTextToCheck = "This procedure executed successfully!"
   End If
End Function

The ErrorExample3 procedure is available in the modErrorCode module in ErrorHandlers.dot in the ODETools\V9\Samples\OPG\Samples\CH08 subfolder on the Office 2000 Developer CD-ROM.

Note   When debugging, you can also use the Assert method of the Debug object to test the validity of the supplied data. For more information about using the Assert method, see "Using Assertions" later in this chapter.

Test each procedure as soon as it is written to make sure that it does what it is supposed to do and, if necessary, validates data submitted in arguments. If your application uses data supplied by the user, make sure you test for unexpected input values. For example, if you expect a user to enter a numeric value in a text box control, what happens if the user enters text in that control instead?

Make sure you know that your code actually works. When an error occurs in a procedure, you know that your code does not work given the input data that caused the error. But it does not follow that if an error does not occur, you are safe to assume that your code does work. While debugging your code, use assertions to test for conditions that should exist at specific points in your code. For example, you can use assertions to test the validity of inputs to procedures. For more information about assertions, see "Using Assertions" later in this chapter.

When you locate a problem in your code, make sure you understand its nature and extent (errors in code are not always what they appear to be) and then fix it immediately. Avoid flagging errors or questionable code with the intent to come back and fix them later. You may never get back to it and what needs to be done will never be clearer in your mind than when you are creating the procedure.

When you do make a change to your code, use a comment to document the change. Consider commenting out old code but leaving it in a procedure until you're certain the new code works correctly.

Be aware that changing code in one place can introduce additional bugs somewhere else. When debugging code, never make more than one change to a procedure without retesting it and all related procedures after each change.