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

Naming Conventions

What Is a Naming Convention and Why Is It Important

The term naming convention refers to the system you use to name the various parts of your application. Whenever you declare a variable or create a userform, you give it a name. You implicitly name objects even when you do not give them a name directly by accepting the default name provided when you create a userform, for example. One of the hallmarks of good programming practice is the consistent use of a clearly defined naming convention for all parts of your VBA application.

Let's look at an example that may help demonstrate why naming conventions matter. In the following line of code, what do you know about x?

x = wksDataSheet.Range("A1").Value

From its usage you can reasonably assume it is a variable. But what data type is it designed to hold? Is its scope public, module level, or private? What is its purpose in the program? As it stands, you cannot answer any of these questions without spending some time searching through the rest of the code. A good naming convention conveys the answers to these questions with a simple visual inspection of the variable name. Here's a revised example. (We cover the specifics in detail in the next section.)

glListCount = wksDataSheet.Range("A1").Value

Now you know the scope of the variable (g stands for global or public scope), what data type it was designed to hold (l stands for the Long data type) and have a rough idea of the purpose of the variable (it holds the number of items in a list).

A naming convention helps you to immediately recognize the type and purpose of the building blocks used in an application. This enables you to concentrate on what the code is doing rather than having to figure out how the code is structured. Naming conventions also help make your code self-documenting, reducing the number of comments required to make the purpose of your code clear.

We present an example of a well-structured naming convention in the following section. However, the most important thing about naming conventions is that you pick one and use it consistently. As long as everyone involved in a project understands the naming convention, it doesn't really matter exactly what prefixes you use or how your names are capitalized. When it comes to naming conventions, consistency rules, both across projects and over time.

A Sample Naming Convention

A good naming convention applies not just to variables, but to all the elements of your application. The sample naming convention we present here covers all the elements in a typical Excel application. We begin with a discussion of variables, constants and related elements, because these are the most common elements in any application. Table 3-1 shows the general format of the naming convention. The specific elements of the naming convention and their purposes are described afterward.

Type DESCRIPTIVE_NAME
<data type>DescriptiveName
End Type

Enum <project prefix>GeneralDescr
<project prefix>GeneralDescrSpecificName1
<project prefix>GeneralDescrSpecificName2
End Enum

The Scope Specifier (<scope>)

g
Public

m
Module level

(nothing)
Procedure level

The Array Specifier (<array>)

a
Array

(nothing)
Not an array

The Data Type Specifier (<data type>)

There are so many data types that it's difficult to provide a comprehensive list of prefixes to represent them. The built-in types are easy. The most frequently used built-in types get the shortest prefixes. Problems arise when naming object variables that refer to objects from various applications. Some programmers use the prefix obj for all object names. This is not acceptable. However, devising consistent, unique and reasonably short prefixes for every object type you will ever use is also probably too much to ask. Try to find reasonably meaningful one- to three-letter prefixes for the object variables you use most frequently and reserve the obj prefix for objects that appear infrequently in your code.

Make your code clear, and above all, be consistent. Keep data type prefixes to three characters or fewer. Longer prefixes, in combination with scope and array specifiers, make for unwieldy variable names. Table 3-2 shows some suggested prefixes for the most commonly used data types.

^[*] Used for ComboBox controls with a DropDownCombo Style setting.

^[**] Used for ComboBox controls with a DropDownList Style setting.

Using Descriptive Names

VBA gives you up to 255 characters for each of your variable names. Use a few of them. Don't try to save yourself a little effort by making your variable names very short. Doing so will make your code difficult to understand in the long run, both for you and for anyone else who has to work on it.

The Visual Basic IDE provides an auto-complete feature for identifiers (all the names used in your application). You typically need to type only the first few characters to get the name you want. Enter the first few characters of the name and press Ctrl+Spacebar to activate an auto-complete list of all names that begin with those characters. As you type additional characters, the list will continue to narrow down. In Figure 3-1, the Ctrl+Spacebar shortcut has been used to display a list of message string constants available to add to a message box.

Figure 3-1. Using the Ctrl+Spacebar Shortcut to Auto-Complete Long Names

[View full size image]

A Few Words About Enumerations

Enumerations are a special type of constant available in Excel 2000 and higher. They enable you to take a list of related values and package them up with similar, logical friendly names. VBA and the Excel object model make extensive use of enumerations. You can see these in the auto-complete list that VBA provides for the values of many properties. For example if you type:

Sheet1.PageSetup.PaperSize =

into a VBA module, you'll be prompted with a long list of XlPaperSize enumeration members that represent the paper sizes available to print on. Figure 3-2 shows this in action.

Figure 3-2. The Excel Paper Size Enumeration Member List

Chapter 16 VBA Debugging. Notice the structure of these enumeration names. First, they all begin with a prefix identifying the application they are associated with, in this case xl, which obviously stands for Excel. Second, the first part of their name is a descriptive term that ties them together visually as belonging to the same enumerated type, in this case Paper. The last part of each enumeration name is a unique string describing the specific value. For example, xlPaper11x17 represents 11x17 paper and xlPaperA4 represents A4 paper. This system for naming enumerated constants is very common and is the one we use in this book.

Naming Convention Examples

Naming convention descriptions in the abstract are difficult to connect to real-world names, so we show some real-world examples of our naming convention in this section. All of these examples are taken directly from commercial-quality applications written by the authors.

Variables

gsErrMsg
A public variable with the data type String used to store an error message

mauSettings()
A module-level array of user-defined type that holds a list of settings

cbrMenu
A local variable with the data type CommandBar that holds a reference to a menu bar

Constants

gbDEBUG_MODE
A public constant of type Boolean that indicates whether the project is in debug mode

msCAPTION_FILE_OPEN
A module-level constant of data type String that holds the caption for a user-defined file open dialog (Application.GetOpenFilename in this instance)

lOFFSET_START
A local constant of data type Long holding the point at which we begin offsetting from some Range object

User-Defined Types

The following is a public user-defined type that is used to store the dimensions and location of an object. It consists of four variables of data type Double that store the top, left, width and height of the object and a variable of data type Boolean used to indicate whether the settings have been saved.

Public Type DIMENSION_SETTINGS
bSettingsSaved As Boolean
dValTop As Double
dValLeft As Double
dValHeight As Double
dValWidth As Double
End Type

The variables within a user-defined type definition are called member variables. These can be declared in any order. However, our naming convention suggests you sort them alphabetically by data type unless there is a strong reason to group them in some other fashion.

Enumerations

The following is a module-level enumeration used to describe various types of days. The sch prefix in the name of the enumeration stands for the application name. This enumeration happens to come from an application called Scheduler. DayType in the enumeration name indicates the purpose of the enumeration and each of the individual enumeration members has a unique suffix that describes what it means.

Private Enum schDayType
schDayTypeUnscheduled
schDayTypeProduction
schDayTypeDownTime
schDayTypeHoliday
End Enum

If you don't indicate what values you want to give your enumeration members, VBA automatically assigns a value of zero to the first member in the list and increments that value by one for each additional member. You can easily override this behavior and assign a different starting point from which VBA will begin incrementing. For example, to make the enumeration above begin with one instead of zero, you would do the following:

Private Enum schDayType
schDayTypeUnscheduled = 1
schDayTypeProduction
schDayTypeDownTime
schDayTypeHoliday
End Enum

VBA will continue to increment by one for each member after the last member for which you've specified a value. You can override automatic assignment of values to all of your enumeration members by simply specifying values for all of them.

Figure 3-3 shows one of the primary advantages of using enumerations. VBA provides you with an auto-complete list of potential values for any variable declared as a specific enumeration.

Figure 3-3. Even Custom Enumerations Get a VBA Auto-Complete Listing

Procedures

Subroutines and functions are grouped under the more general term procedure. Always give your procedures very descriptive names. Once again, you are allowed up to 255 characters for your procedure names, and procedure names appear in the Ctrl+Spacebar auto-complete list, so don't sacrifice a name that makes the purpose of a procedure obvious for one that's simply short.

It is not a common practice to do so, but we find that giving functions a prefix indicating the data type of their return value to be very helpful in understanding code. When calling a function, always place open and closed parenthesis after the function name to distinguish it from a variable or subroutine name, even if the function takes no arguments. Listing 3-1 shows a well-named Boolean function being used as the test for an If...Then statement.

Listing 3-1. An Example of Naming Conventions for Function Names

If bValidatePath("C:\Files") Then
' The If...Then block is executed
' if the specified path exists.
End If

Subroutines should be given a name that describes the task they perform. For example, a subroutine named ShutdownApplication leaves little doubt as to what it does. Functions should be given a name that describes the value they return. A function named sGetUnusedFilename() can reasonably be expected to return a filename.

The naming convention applied to procedure arguments is exactly the same as the naming convention for procedure-level variables. For example, the bValidatePath function shown in Listing 3-1 would be declared in the following manner:

Function bValidatePath(ByVal sPath As String) As Boolean

Modules, Classes and Userforms

In our sample naming convention, the names of standard code modules should be prefixed with an uppercase M, class modules with an uppercase C and userforms with an upper case F. This has the advantage of neatly sorting these objects in the VBE Project window if you don't care for the folder view, as shown in Figure 3-4.

Figure 3-4. Class Modules, Userforms and Standard Modules Sorted in the Project Window

This convention also makes code that uses classes and userform objects much clearer. In the following code sample, for example, this naming convention makes it very clear that you are declaring an object variable of a certain class type and then creating a new instance of that class:

Dim clsMyClass As CMyClass
Set clsMyClass = New CMyClass

In each case, the name on the left is a class variable, and the object on the right is a class.

Worksheets and Chart Sheets

Because the CodeNames of worksheets and chart sheets in your project are treated by VBA as intrinsic object variables that reference those sheets, the CodeNames given to worksheets and chart sheets should follow variable naming conventions. Worksheet CodeNames are prefixed with wks to identify them in code as references to Worksheet objects. Similarly, chart sheets are prefixed with cht to identify them as references to Excel Chart objects.

For both types of sheets, the prefix should be followed by a descriptive term indicating the sheet's purpose in the application. Figure 3-4, for example, shows a wksCommandBars worksheet that contains a table defining the command bars created by the application. For sheets contained within an add-in or hidden in a workbook and not designed to be seen by the user, the sheet tab name should be identical to the CodeName. For sheets that are visible to the user, the sheet tab name should be a friendly name, and one that you should be prepared for the user to change. As discussed later, you should always rely on sheet CodeNames rather than sheet tab names within your VBA code.

The Visual Basic Project

Notice in Figure 3-4 that the Visual Basic Project has been given the same name as the workbook it's associated with. You should always give your VBProject a name that clearly identifies the application it belongs to. There's nothing worse than having a group of workbooks open in the VBE with all of them having the same default name VBAProject. If you plan on creating references between projects, you will be required to give them unique names.

Excel UI Naming Conventions

Excel user interface elements used in the creation of an application should also be named using a consistent and well-defined naming convention. We covered worksheets and chart sheets in a previous section. The three other major categories of Excel UI elements that can be named are shapes, embedded objects and defined names.

Shapes

The term Shapes refers to the generic collection that can contain the wide variety of objects you can place on top of a worksheet or chart sheet. Shapes can be broadly divided into three categories: controls, drawing objects and embedded objects. Shapes should be named similarly to object variables, which is to say they should be given a prefix that identifies what type of object they are followed by a descriptive name indicating what purpose they serve in the application.

Many controls that can be placed on userforms can be placed on worksheets as well. Worksheets can also host the old Forms toolbar controls, which are similar to the ActiveX MSForms controls but with their own unique advantages and disadvantages. Chapter 4 Worksheet Design discusses these in more detail. Controls placed on worksheets should be named using exactly the same conventions you would use for controls placed on userforms.

Worksheets can also host a wide variety of drawing objects (technically known as Shapes) that are not strictly controls, although you can assign macros to all of them. These fall into the same naming convention category as the wide variety of objects that you can use in VBA. It would be very difficult to devise unique prefixes for all of them, so use well-defined prefixes for the most common drawing objects and use a generic prefix for the rest. Here are some sample prefixes for three of the most commonly used drawing objects:

Embedded Objects

The term embedded object is used here to refer to Excel objects such as PivotTables, QueryTables and ChartObjects, as well as objects created by applications other than Excel. Worksheets can host a variety of embedded objects. Common examples of non-Excel embedded objects would include equations created with the Equation Editor and WordArt drawings. Sample prefixes for embedded objects are shown here:

Defined Names

Our naming convention for defined names is a bit different than for other program elements. In the case of defined names, the prefix should indicate the broad purpose of the defined name, as opposed to the data type it's expected to hold. This is because nontrivial Excel applications typically have many defined names that are much easier to work with if they are grouped together by purpose within the Define Name dialog. When a worksheet contains dozens or hundreds of defined names, there are significant efficiencies to be gained by having names with related functions grouped together by prefix in the defined name list.

The descriptive name portion of a defined name is used to specify exactly what purpose the name serves within its broader category. The following list shows some examples of purpose prefixes for defined names:

ExceptionsWhen Not to Apply the Naming Convention

You want to break the general rule and not apply your naming convention in two specific situations. The first is when you are dealing with elements related to Windows API calls. These elements have been named by Microsoft, and the names are well known within the programming community. The Windows API constants, user-defined types, procedure declarations and procedure arguments should appear in your code exactly as they appear in the Microsoft Platform SDK, which can be viewed on the MSDN Web site at: