Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

General Application Development Best Practices

This section covers best development practices common to all application development areas. Most of the other chapters in this book recommend further best practices related specifically to the subject of that chapter.

Code Commenting

Good code commenting is one of the most important practices in Excel application development. Your code comments should provide a clear and complete description of how your code is organized, how each object and procedure should be used and what you are trying to accomplish with your code. Comments also provide a means of tracking changes to your code over time, a subject we cover later in this chapter.

Code comments are important to both you and to other developers who may need to work on your code. The utility of code comments to other developers should be self-evident. What you might not realize until the cruel fist of experience has pounded it into you is that your comments are very important to you as well. It is very common for a developer to write an initial version of an application and then be asked to revise it substantially after a long period of time has passed. You would be surprised at how foreign even your own code looks to you after it has been out of sight and out of mind for a long period of time. Code comments help solve this problem.

Comments should be applied at all three major levels of your application's code: the module level, the procedure level and to individual sections or lines of code. We discuss the types of commenting appropriate to each of these levels below.

Module-Level Comments

If you have used the module naming conventions described previously in this chapter, then anyone examining your code will have a rough idea of the purpose of the code contained within each module. You should supplement this with a brief comment at the top of each module that provides a more detailed description of the purpose of the module.

NOTE

For the purposes of code commenting, when we use the term module, we mean it to include standard modules, class modules and the code modules behind userforms.

A good module-level comment should be located at the very top of the module and look something like the example shown in Listing 3-2.

Listing 3-2. A Sample Module-Level Comment

'
' Description:    A brief description of the purpose of the
'                 code in this module.
'
Option Explicit

Procedure-Level Comments

Procedure-level comments are typically the most detailed comments in your application. In a procedure-level comment block, you describe the purpose of the procedure, usage notes, a detailed list of arguments and their purposes and a description of expected return values in the case of functions.

Procedure-level comments can also serve a rudimentary change-tracking purpose by providing a place to add dates and descriptions of changes made to the procedure. A good procedure-level comment such as the one shown in Listing 3-3 would be placed directly above the first line of the procedure. The procedure-level comment in Listing 3-3 is designed for a function. The only difference between a comment block for a function and a comment block for a subroutine is the subroutine comment block does not contain a Returns section, obviously because subroutines do not return a value.

Listing 3-3. A Sample Procedure-Level Comment

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Comments:     Locates the chart to be operated on or asks
'               the user to select a chart if multiple charts
'               are located.
'
' Arguments:    chtChart    Returned by this function. An
'                          object reference to the chart to
'                          be operated on, or Nothing on user
'                          cancel.
'
' Returns:      Boolean     True on success, False on error
'                           or user cancel.
'
' Date         Developer     Action
' -------------------------------------------------------------
' 07/04/02     Rob Bovey     Created
' 10/14/03     Rob Bovey     Error trap for charts with no
series
' 11/18/03     Rob Bovey     Error trap for no active workbook
'

Internal Comments

Internal comments are comments that appear within the body of the code itself. These comments should be used to describe the purpose of any code where the purpose is not self-evident. Internal comments should describe the intent of the code rather than the operation of the code. The distinction between intent and operation is not always clear, so Listing 3-4 and Listing 3-5 show two examples of the same code, one with a bad comment and the other with a good comment.

Listing 3-4. Example of a Bad Internal Code Comment

' Loop the asInputFiles array.
For lIndex = LBound(asInputFiles) To UBound(asInputFiles)
'...
Next lIndex

The comment in Listing 3-4 is monumentally unhelpful. First of all, it describes only the line of code directly below it, giving you no clue about the purpose of the loop structure as a whole. Second, the comment is simply an exact written description of that line of code. This information is easy enough to determine by just looking at the line of code. If you removed the comment shown in Listing 3-4, you would not lose any information at all.

Listing 3-5. Example of a Good Internal Code Comment

' Import the specified list of input files into the working area
' of our data sheet.
For lIndex = LBound(asInputFiles) To UBound(asInputFiles)
'...
Next lIndex

In Listing 3-5, we have a comment that adds value to the code. Not only does it describe the intent, rather than the operation of the code, it also explains the entire loop structure. After reading this comment, you know what you're looking at as you delve into the code within the loop.

As with most rules, there are exceptions to the internal comment guidelines specified above. The most important exception concerns comments used to clarify control structures. If...Then statements and Do... Loops can make code difficult to understand as they become wider, because you can no longer see the entire control structure in a single code window. At that point, it becomes difficult to remember what the applicable control expression was. For example, when evaluating a lengthy procedure we have often found ourselves looking at something like the code snippet shown in Listing 3-6.

Listing 3-6. Inscrutable Control Structures

End If
lNumInputFiles = lNumInputFiles - 1
Loop
End If

In Listing 3-6, what are the logical tests being made by the two If...Then statements, and what expression controls the Do...While loop? After these structures have been filled with a substantial amount of code, you simply cannot tell without scrolling back and forth within the procedure, because the entire block is no longer visible within a single code window. You can alleviate this problem very easily by using the end-of-control-block commenting style shown in Listing 3-7.

Listing 3-7. Understandable Control Structures

End If  ' If bContentsValid Then
lNumInputFiles = lNumInputFiles - 1
Loop        ' Do While lNumInputFiles > 0
End If          ' If bInputFilesFound Then

The comments in Listing 3-7, although they just restate the code at the top of each control structure, make it completely obvious what you are looking at. These types of comments should be used anywhere you have a control structure within your code that is too large to fit completely into one code window.

Avoiding the Worst Code-Commenting Mistake

It might seem obvious, but the most frequent and damaging mistake related to code commenting is not keeping the comments updated as you modify the code. We have frequently seen projects that appeared at first glance to implement good code-commenting practices, but upon closer examination discovered the comments were created for some ancient version of the project and now bore almost no relationship to the current code.

When attempting to understand a project, bad comments are worse than no comments at all. Bad comments are actively misleading. Always keep your comments current. Old comments can either be deleted or retained as a series of change-tracking records. We recommend removing obsolete in-line comments or they will quickly clutter your code, making it difficult to understand simply due to the number of lines of inapplicable comments that accumulate. Use procedure-level comments as a change-tracking mechanism where necessary.

Code Readability

Code readability is a function of how your code is physically arranged. Good visual layout of code enables you to infer a significant amount of information about the logical structure of the program. This is a key point. Code layout makes not one bit of difference to the computer. Its sole purpose is to assist humans in understanding the code. Like naming conventions, the consistent use of good code layout conventions makes your code self-documenting. The primary tool of code layout is white space. White space includes space characters, tabs and blank lines. The following paragraphs discuss the most important ways to use white space to produce a well-designed code layout.

Group related code elements together and separate unrelated code elements with blank lines. Sections of code separated by blank lines within a procedure can be thought of as serving a similar function to paragraphs within the chapters of a book. They help you determine what things belong together. Listing 3-8 shows an example of how blank lines can improve code readability. Even without the code comments, it would be obvious which lines of code are related.

Listing 3-8. Using Blank Lines to Group Related Sections of Code

' Reset Application properties.
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.StatusBar = False
Application.Caption = Empty
Application.EnableCancelKey = xlInterrupt
Application.Cursor = xlDefault
' Delete all custom CommandBars
For Each cbrBar In Application.CommandBars
If Not cbrBar.BuiltIn Then
cbrBar.Delete
Else
cbrBar.Enabled = True
End If
Next cbrBar
' Reset the Worksheet Menu bar.
With Application.CommandBars(1)
.Reset
.Enabled = True
.Visible = True
End With

Within a related section of code, alignment is used to indicate which lines of code belong together. Indentation is used to show the logical structure of the code. Listing 3-9 shows a single section from Listing 3-8 where alignment and indentation have been used to good effect. You can look at this section of code and understand immediately which elements go together as well as deduce the logical flow of the code's execution.

Listing 3-9. Proper Use of Alignment and Indentation

' Delete all custom CommandBars
For Each cbrBar In Application.CommandBars
If Not cbrBar.BuiltIn Then
cbrBar.Delete
Else
cbrBar.Enabled = True
End If
Next cbrBar

Line continuation can be used to make complex expressions and long declarations more readable. Keep in mind that breaking code into continued lines solely for the purpose of making the entire line visible without scrolling is not necessarily a good practice and can often make code more confusing. Listing 3-10 shows examples of judicious use of line continuation.

Listing 3-10. Judicious Use of Line Continuation

' Complex expressions are easier to understand
' when properly continued
If (uData.lMaxLocationLevel > 1) Or _
uData.bHasClientSubsets Or _
(uData.uDemandType = bcDemandTypeCalculate) Then
End If
' Line continuations make long API declarations easier to read.
Declare Function SHGetSpecialFolderPath Lib "Shell32.dll" _
(ByVal hwndOwner As Long, _
ByRef szBuffer As String, _
ByVal lFolder As Long, _
ByVal bCreate As Long) As Long

VBA Programming Best Practices

General VBA Best Practices

Use of Module Directives

Option Explicit
Always use the Option Explicit statement in every module. The importance of this practice cannot be overstated. Without Option Explicit, any typographical error you make results in VBA automatically creating a new Variant variable. This type of error is very insidious because it may not even cause an immediate runtime error. However, it will certainly cause your application to eventually return incorrect results. This type of bug may very well pass without notice until your application is distributed, and it will be difficult to debug under any circumstances.

The Option Explicit statement forces you to explicitly declare all the variables you use. Option Explicit causes VBA to throw a compile-time error (initiated by selecting Debug > Compile from the VBE menu) whenever an unrecognized identifier name is encountered. This makes it very easy to discover and correct typographical errors. You can ensure that Option Explicit is automatically placed at the top of every module you create by choosing Tools > Options > Editor from the VBE menu and checking the Require Variable Declaration check box. This setting is strongly recommended.

Option Private Module
The Option Private Module statement makes all procedures within the module where it is used unavailable from the Excel user-interface or from other Excel projects. Use this statement to hide procedures that should not be called from outside your application.

TIP

The Application.Run method can circumvent the Option Private Module statement and run private procedures in modules where this statement has been used.

Option Base 1
The Option Base 1 statement causes all array variables whose lower bound has not been specified to have a lower bound of 1. Do not use the Option Base 1 statement. Instead, always specify both the upper and lower bounds of every array variable you use. A procedure created in a module that uses Option Base 1 may malfunction if copied to a module in which this statement isn't used. This behavior inhibits one of the most important procedure design goals, that of reusability.

Option Compare Text
The Option Compare Text statement forces all string comparisons within the module where it is used to be text based rather than binary. In a text-based string comparison, upper- and lowercase versions of the same character are treated as identical, whereas in a binary comparison they are different. The Option Compare Text statement should be avoided for the same reason Option Base 1 should be avoided. It makes procedures behave differently when placed in modules with the statement versus modules without it. Text-based comparisons are also much more computationally expensive than binary comparisons, so Option Compare Text slows down all string comparison operations in the module where it's located. Most Excel and VBA string comparison functions provide an argument you can use to specify binary or text-based comparison. It's much better to use these arguments to provide text-based comparisons only where you need them.

There are some rare cases where Option Compare Text is required. The most frequent case occurs when you need to do non-case-sensitive string comparisons with the VBA Like operator. The only way to get the Like operator to perform in a non-case-sensitive manner is to use the Option Compare Text statement. In this case, you should isolate the procedures that require this statement in a separate code module so other procedures that don't require this option aren't adversely affected. Be sure to document why you have done this in a module-level comment.

Variables and Constants

Avoid Reusing Variables

Each variable declared in your program should serve one purpose only. Using the same variable for multiple purposes saves you only one variable declaration line but introduces massive potential for confusion within your program. If you are trying to determine how a procedure works and you have figured out what a certain variable does in a certain place, you will naturally assume the variable serves the same purpose the next time you see it. If this is not the case, the code logic will become very difficult to understand.

Avoid the Variant Data Type

Avoid the use of the Variant data type whenever possible. Unfortunately, VBA is not a strongly typed programming language. Therefore, you can simply declare variables without specifying their data type and VBA will create these variables as Variants. The main reasons not to use Variants are as follows:

Variants are very inefficient
This is because internally, a Variant is a very complex structure designed to hold any data type in the VBA programming language. Variant values cannot be accessed and modified directly as can fundamental data types such as Long and Double. Instead, VBA must use a series of complex Windows API calls behind the scenes whenever it needs to perform any operation on a Variant.

Data stored in a variant can behave unexpectedly
Because Variants are designed to hold any type of data, the data type that goes into a Variant is not necessarily the data type that will come out of it. When accessing the data in a Variant, VBA will attempt to coerce the data into whatever data type it thinks makes the most sense in the context of the operation. If you must use Variants, always explicitly cast them to the data type you want when using their values.

Beware of Evil Type Coercion

Evil Type Coercion (ETC) is another symptom that results from VBA not being a strongly typed programming language. ETC occurs when VBA automatically converts one data type to another completely unrelated data type. The most frequent examples are Strings that hold numbers being converted to Integers and Booleans being converted to their String equivalents. Don't mix variables of different data types in your code without using the explicit casting functions (CStr, CLng, CDbl and so on) to tell VBA exactly how you want those variables to be treated.

Avoid the As New Declaration Syntax

Never declare object variables using the As New syntax. For example, the following form of an object variable declaration should never be used:

Dim rsData As New ADODB.Recordset

If VBA encounters a line of code that uses this variable and the variable has not been initialized, VBA will automatically create a new instance of the variable. This is never the behavior you want. Good programming practice implies that the programmer should maintain complete control over the creation of all the objects used in the program. If VBA encounters an uninitialized object variable in your code, it is almost certainly the result of a bug, and you want to be notified about it immediately. Therefore, the proper way to declare and initialize the object variable shown above is as follows:

Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset

Using this style of declaration and initialization, if the object variable is destroyed somewhere in your procedure and you inadvertently reference it again after that point, VBA will immediately throw the runtime error "Object variable or With block variable not set," notifying you of the problem.

Always Fully Qualify Object Names

Always use fully qualified object names in variable declarations and code with their class name prefix. The reason for this is because many object libraries share the same object names. If you just declare a variable with an object name alone and there are multiple object libraries with that object name being referenced by your application, VBA will create a variable from the first library in the Tools > References list where it finds the object name you used. This is often not what you want.

UserForm controls present the most common situation where problems result from object variable declarations that aren't fully qualified. For example, if you wanted to declare an object variable to reference a TextBox control on your userform, you might be inclined to do the following:

Dim txtBox As TextBox
Set txtBox = Me.TextBox1

Unfortunately, as soon as VBA attempts to execute the second line of code, a "Type mismatch" error would be generated. This is because the Excel object library contains a TextBox object and the Excel object library comes before the MSForms object library in the Tools > References list. The correct way to write this code is shown here:

Dim txtBox As MSForms.TextBox
Set txtBox = Me.TextBox1

Never Hard Code Array Bounds

When you are looping the contents of an array variable, never hard-code the array bounds in loop. Use the LBound and UBound functions instead, as shown in Listing 3-11.

Listing 3-11. The Correct Way to Loop an Array

Dim lIndex As Long
Dim alListItems(1 To 10) As Long
' Load the array here.
For lIndex = LBound(alListItems) To UBound(alListItems)
' Do something with each value.
Next lIndex

The reason for this is because array bounds frequently change over the course of creating and maintaining an application. If you hard-code the array bounds 1 and 10 in the loop shown above, you will have to remember to update the loop any time the bounds of the alListItems array change. Failure to do so is a frequent source of errors. By using LBound and Ubound, you make the loop self-adjusting.

Always Specify the Loop Counter After a Next Statement

Listing 3-11 demonstrates another good coding practice. You should always specify the loop counter variable after a Next statement. Even though this is not strictly required by VBA, doing so makes your code much easier to understand, especially if the distance between the For and Next statements is long.

Make Use of Constants

Constants are very useful programming elements. They serve the following purposes in your code, among others:

Constants eliminate "magic numbers," replacing them with recognizable names. For example, in the following line of code, what does the number 50 mean?

If lIndex < 50 Then

There is no way of knowing unless you wrote the code and you still remember what 50 represents. If instead you saw the following, you would have a very good idea of what the If...Then test was looking for:

Const lMAX_NUM_INPUT_FILES As Long = 50
' More code here.
If lIndex < lMAX_NUM_INPUT_FILES Then

If you need to know the value of a constant at design time, you can just right-click over the constant name in the VBE and choose Definition from the shortcut menu. You will be brought directly to the line where the constant is defined. In break mode at runtime it's even easier. Just hover your mouse over the constant and a tooltip window containing its value will appear.

Constants improve coding efficiency and avoid errors by eliminating duplicate data. In the preceding example, assume you reference the maximum number of input files in several places throughout your program. At some point you may need to upgrade your program to handle more files. If you have hard-coded the maximum number of input files everywhere you've needed to use it, you will have to locate all of these places and change the number in each one. If you've used a constant, all you need to do is modify the value of the single constant declaration and the new value will automatically be used wherever the constant has been used in your code. This situation is a very frequent source of errors that can be eliminated by simply using constants instead of hard-coded numbers.

Variable Scope

Public variables are dangerous. They can be modified anywhere in your application without warning, making their values unpredictable. They also work against one of the most important programming precepts: encapsulation. Always create variables with the minimum scope possible. Begin by creating all of your variables with local (procedure-level) scope and only widen the scope of a variable when it is absolutely necessary.

As with most of our other rules, there are a few cases where the use of public variables is useful and/or necessary.

When data must be passed deep into the stack before it is used. For example, if procedure A reads some data, then passes that data to procedure B, which passes it to procedure C, which passes it to procedure D where the data is finally used, a good case can be made that the data should be passed directly from procedure A to procedure D by way of a public variable.

Certain inherently public classes, such as an application-level event handling class, require a public object variable so they never go out of scope while your application is running.

Early Binding vs. Late Binding

The distinction between early binding and late binding is widely misunderstood and often confused with how an object is created. The only thing that affects whether an object is early bound or late bound is how the object variable holding the reference to the object was declared. Variables declared as a specific object data type are always early bound. Variables declared with the Object or Variant data type are always late bound. Listing 3-12 shows an example of a late bound reference, and Listing 3-13 shows an example of an early bound reference.

Listing 3-12. A Late Bound Reference to an ADO Connection Object

Dim objConnection As Object
' It doesn't matter how you create the object, it's still
' late bound due to the As Object variable declaration.
Set objConnection = New ADODB.Connection
Set objConnection = CreateObject("ADODB.Connection")

Listing 3-13. An Early Bound Reference to an ADO Connection Object

Dim cnConnection As ADODB.Connection
' It doesn't matter how you create the object, it's still early
' bound due to the data type used in the variable declaration.
Set cnConnection = New ADODB.Connection
Set cnConnection = CreateObject("ADODB.Connection")

Note that to use early binding with objects that are outside the Excel object model you must set a reference to the appropriate object library using the Tools > References menu in the Visual Basic Editor. For example, to create early bound variables referencing ADO objects, you must set a reference to the Microsoft ActiveX Data Objects 2.x Library, where x is the version of ADO you intend to use.

You should use early bound object variables wherever possible. Early bound object variables provide the following advantages over late bound variables:

Improved performance
When you use an object variable whose data type is known to VBA at compile time, VBA can look up the memory locations of all property and method calls you use with this object and store them with your code. At runtime, when VBA encounters one of these early bound property or method calls, it simply executes the code located at the stored location. (This is a bit of an oversimplification. What VBA actually stores is a numeric offset to the code to be executed from a known starting point in memory, which is the beginning of a structure called the object's VTable.)

When you use a late bound object variable, VBA has no way of knowing in advance what type of object the variable will contain. Therefore, it cannot optimize any property or method calls at compile time. This means that each time VBA encounters a late bound property or method call at runtime, it must query the variable to determine what kind of object it holds, look up the name of the property or method being executed to determine where in memory it is located and then execute the code located at that memory address. This process is significantly slower than an early bound call.

Strict type checking
In the late bound example in Listing 3-12, if you accidentally set your object variable to reference an ADO Command object instead of a Connection object, VBA would not complain. You would only discover you had a problem downstream in your code when you tried to use a method or property not supported by the Command object. With early binding, VBA will immediately detect that you are trying to assign the wrong type of object reference to your object variable and notify you with a "Type mismatch" error. Incorrect property and method calls can be detected even earlier, before the code is ever run. VBA will attempt to look up the name of the property or method being called from within the appropriate object library at compile time and throw an error if the name cannot be located.

IntelliSense availability
Early bound object variables make for much easier programming as well. Because VBA knows exactly what type of object a variable represents, it can parse the appropriate object library and provide a drop-down list of all available properties and methods for the object as soon as you type a dot operator after the variable's name.

As you might expect, in some cases you need to use late binding rather than early binding. The two most common reasons for using late binding rather than early binding are as follows:

When a newer version of an application's object library has broken compatibility with an earlier version.

This is an all too common situation. If you set a reference to the later version of the application's object library in your application and then attempt to run it on a computer that has the earlier version, you will get an immediate compile time error "Can't find project or library," and the reference on the target machine will be prefixed with MISSING. The most insidious thing about this error is that the line of code flagged as being the source of the error will often have nothing to do with the object library actually causing the problem.

If you need to use objects from an application that exhibits this problem and you want to support users with any version of the application, you need to use late binding for all variables referencing objects from the application. If you are creating new objects, you also need to use the CreateObject function with the version-independent ProgID of the object you want to create, rather than the = New ObjectName syntax.

When you want to use an application that you cannot be sure will exist on the user's computer and that you cannot install yourself.

In this case, you need to use late binding to avoid the compile time error that would immediately result from attempting to run an application that referenced an object library that did not exist on the user's computer. Your application can then check for the existence of the object library in question and exit gracefully if that library is not installed on the user's computer.

TIP

Even if you will eventually use late binding in your code, early binding offers such a great increase in productivity while coding that you should write and test the application using early binding. Convert your code to late binding only for the final round of testing and distribution.

Defensive Coding

Defensive coding refers to various programming practices designed to help you prevent errors rather than having to correct them after they occur.

Write Your Application in the Earliest Version of Excel That You Expect It to Run In

Although the Microsoft Excel team has done a better job than most of maintaining backward compatibility with earlier versions of Excel, there are many subtle differences between the versions. If you are very familiar with a later version of Excel, you can easily write an application that will not run on an earlier version because some feature you used did not exist in that version.

The solution to this problem is to always develop your applications in the earliest version of Excel that you expect them to run in. This may force you to maintain multiple versions of Excel on one computer, or better yet, separate computers for each version of Excel. Either way, this is an essential practice. If you develop an application in Excel 2000, give it to a user in Excel 97 and find out it doesn't run, you will need to debug and remove any code that doesn't work in Excel 97. You will save much time and stress by simply developing the application using Excel 97 to begin with.

Explicitly Use ByRef or ByVal

If a procedure takes arguments, there are two ways to declare those arguments: ByRef or ByVal.

ByRef
This convention means you are passing the memory address of the variable rather than the value of the variable. If the called procedure modifies a ByRef argument, the modification will be visible in the calling procedure.

ByVal
This convention means you are passing a value to the procedure. A procedure can make changes to a ByVal argument, but these changes will not be visible to the calling procedure. In fact, a procedure can use ByVal arguments exactly as if they were locally declared variables.

Always explicitly declare your procedure arguments as ByRef or ByVal. If you do not specify this, all arguments are created ByRef by default. You should declare procedure arguments ByVal unless you have a specific need for the calling procedure to see changes made to the arguments. Declaring arguments ByVal will prevent changes made to those arguments from being propagated back to the calling procedure.

The only exceptions are when you are passing large strings (very large strings), which are far more efficiently passed ByRef, or when your procedure argument is of a type, such as an array, that cannot be passed ByVal. Be aware that declaring procedure arguments ByVal does leave you more exposed to Evil Type Coercion. A ByRef procedure argument must be passed the same data type as it is declared to accept; otherwise a compile time error will result. By contrast, VBA will attempt to coerce a value passed to a ByVal procedure argument into a compatible data type.

Explicitly Call the Default Property of an Object

With the possible exception of the Item property of a Collection object, it's never a good idea to implicitly invoke the default property of an object just by using the object's name in an expression. Listing 3-14 shows the right way and the wrong way of accessing the default property of an object using an MSForms.TextBox control for demonstration purposes (the Text property is the default property of an MSForms.TextBox control).

Listing 3-14. Default Properties

' The right way.
txtUsername.Text = "My Name"
' The wrong way
txtUsername = "My Name"

By avoiding the implicit use of default properties, you make your code much more readable and protect yourself from errors if the default behavior of the object changes in some future version of Excel or VBA.

Validate Arguments Before Using Them in Procedures

If your procedure accepts input arguments that must have certain properties in order to be validfor example, if they must be within a specific range of valuesverify that the values passed to those arguments are valid before attempting to use them in your procedure. The idea is to catch erroneous input as soon as possible so that you can generate a meaningful error message and simplify your debugging.Chapter 16 VBA Debugging discusses test harnesses in detail.

Use Guard Counters to Protect Against Infinite Loops

Program your loops to automatically handle infinite loop conditions. One of the most common mistakes made when using Do...While or While...Wend loops is to create a situation where the loop control condition is never satisfied. This causes the loop to run forever (or until you can force your code to break by pressing Ctrl+Break if you are lucky, or by using the Windows Task Manager to shut down your application if you are not). Always add a counter that automatically bails out when the number of loops executed is known to be more than the highest number that should ever occur in practice. Listing 3-15 shows a Do...While loop with an infinite loop guard structure.

Listing 3-15. Guard Against Infinite Loops

Dim bContinueLoop As Boolean
Dim lCount As Long
bContinueLoop = True
lCount = 1
Do
' The code that goes here should set the
' bContinueLoop variable to False once the
' loop has achieved its purpose.
' This infinite loop guard exits the loop
' unconditionally after 10000 iterations.
lCount = lCount + 1
If lCount > 10000 Then Exit Do
Loop While bContinueLoop

The only purpose of the lCount variable within the loop is to force the loop to exit if the code within the loop fails to set the control variable to exit within 10,000 iterations. (The appropriate number would depend on the particular situation.) This type of construct adds very little overhead to your loop; if performance is a significant concern, however, use the infinite loop guard until you are sure all the code within the loop is functioning properly, and then delete it or comment it out.

Use Debug > Compile Early and Often

Never let your code stray more than a few changes away from being able to run a flawless Debug > Compile. Failing to adhere to this practice will lead to long, inefficient debugging sessions.

Use CodeNames to Reference Sheet Objects

Always reference worksheets and chart sheets in your application by their CodeName. Depending on sheet tab names to identify sheets is risky because you or your users may change these tab names, breaking any code that uses them.

Validate the Data Types of Selections

If you write a procedure designed to operate on a specific type of object the user has selected, always check the object type of the selection using either the TypeName function or the If TypeOf...Is construct. For example, if you need to operate on a range selected by the user, ensure that the selection really is a Range object before continuing, as shown in Listing 3-16.

Listing 3-16. Verify That the Selection Is the Correct Object Type

' Code designed to operate on a range.
If TypeOf Selection Is Excel.Range Then
' OK, it's a Range object.
' Continue code execution.
Else
' Error, it's not a Range object.
MsgBox "Please select a range.", vbCritical, "Error!"
End If

Change Control

Change control, also known as version control, at the most basic level involves two practices: maintaining a set of prior versions of your application that you can use to recover from various programming or technical errors and documenting changes made to your application over time.

Saving Versions

When most professional programmers talk about version control, they mean the use of dedicated version control software, such as Microsoft Visual Source Safe. However, this type of software is expensive, has a steep learning curve and doesn't integrate well with applications built in Excel. This is because Excel doesn't store its modules natively as separate text files. The version control method we will suggest here is quick, simple, requires no special software and delivers the most crucial benefits of a traditional version control system.

The most important objective of a version control system is to enable you to recover an earlier version of your project if you have encountered some significant problem with the version you are currently working on. If a significant code modification has gone terribly wrong or you suddenly find yourself with a corrupt file, you will be in a very difficult position if you do not have a recent backup to help you recover.

A simple version control system that can save you from these problems would be implemented in a fashion similar to the following. First create a folder named Backup as a subfolder to the folder in which your project is stored. Each time you prepare to make a significant addition or modification to your project, or at minimum once a day, use a file-compression utility such as WinZip to zip all the files in your project folder into a file with the following name format: Backup_YYYYMMDDHH.zip, where Y stands for year, M stands for month, D stands for day and H stands for hour. This naming format will give your backup file a unique name that will sort in correct sequential order when viewed in Windows Explorer. Move this file into your Backup folder and continue working.

If you encounter a problem, you can recover your project from the most recent backup. You will obviously lose some work, but if you save backup versions diligently you can minimize the loss. Each time you are sure you have a fully tested build of your project, you can delete most of the intermediate files from your Backup folder. It is advisable to retain at least weekly backups throughout the life of a project.

Documenting Changes with Comments

When you are maintaining code, if you make a significant change to the logic of a procedure you should also make a note with a brief description of the change, the date it was made and your name in the procedure-level comment block (see