A good naming convention applies not just to variables, but to all the ele- ments of your application. The sample naming convention we present here covers all the elements in a typical Excel application. We begin with a dis- cussion of variables, constants, and related elements, since these are the most common elements in any application. The general format of the nam- ing convention is shown in Table 3-1. The specific elements of the naming convention and their purposes are described afterwards.
Table 3-1 A Naming Convention for Variables, Constants, UDTs, and Enumerations
Element Naming Convention
Variables <scope><array><data type>DescriptiveName Constants <scope><data type>DESCRIPTIVE_NAME User-defined types Type DESCRIPTIVE_NAME
<data type>DescriptiveName End Type
Enumeration types 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 data types are easy. The most frequently used built-in data types get the shortest prefixes. Problems arise when naming object variables that refer to objects from various applica- tions. Some programmers use the prefix “obj” for all object names. This is
3. E XCEL AND VBA D EVELOPMENT B EST P RACTICES
Table 3-2 Suggested Naming Convention Prefixes
Prefix Data Type
Prefix Data Type Prefix Data Type
b Boolean cm ADODB.Command cbo MSForms.ComboBox*
byt Byte cn ADODB.Connection chk MSForms.CheckBox cur Currency rs ADODB.Recordset cmd MSForms.
CommandButton
dte Date ddn MSForms.ComboBox**
dec Decimal cht Excel.Chart fra MSForms.Frame d Double rng Excel.Range lbl MSForms.Label i Integer wkb Excel.Workbook lst MSForms.ListBox l Long wks Excel.Worksheet mpg MSForms.MultiPage
obj Object opt MSForms.OptionButton
sng Single cbr Office.CommandBar spn MSForms.SpinButton s String ctl Office. CommandBarControl txt MSForms.TextBox u User- Defined Type
not acceptable. However, devising consistent, unique, and reasonably short prefixes for every object type you will ever use is also 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 pre- fixes to three or fewer characters. 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.
Table 3-2 Suggested Naming Convention Prefixes
Prefix Data Type
Prefix Data Type Prefix Data Type
v Variant cls User-Defined Class Variable
ref RefEdit Control frm UserForm Variable col VBA.Collection
*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 vari- able names very short. Doing so will make your code difficult to under- stand 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 charac- ters 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 char- acters, the list continues to narrow down. In Figure 3-1 the Ctrl+Spacebar shortcut has been used to display a list of message string constants avail- able to add to a message box.
3. E XCEL AND VBA D EVELOPMENT B EST P RACTICES
FIGURE3-2 The Excel paper size enumeration list A Few Words about Enumeration Types
Enumerations are a special type of constant available in Excel 2000 and higher. They allow you to group a list of related values together using sim- ilar, logical friendly names. VBA and the Excel object model make exten- sive 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.
These names actually represent numeric constants whose values you can examine by looking them up in the Object Browser, discussed in 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. Next, the first part of their name is a descriptive term that ties them togeth- er 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 xlPaperA4represents A4 paper. This system for naming enumerated con-
stants is common and is the one we use in this book.