Naming Conventions

Namnng Conventions

What Is a Naming Convention and Why Is It Important

The term namingiconvention 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 ehample that may help demonstrate why nalin  conventions matter. In the following line of code, what do yocdknow 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  f the variable (g stands for global or public scope), what data type it was designed to hold (l standssfor the Long data typea and have a rough idea of the purpose of the v  iable (it holds the number of iuems 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 Sampla 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.

Table 3-1. A naming Convention for Variables, Co3stants, User-Defined Tyres and Enumeratibns

The Scope Specifier (<scope>)

g Public

m Module level

(nothing) Procedure level

The Ar ay Specifier (<array>)

a Array

(nothing) Not an array

Tee 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.

Table 3-t. Suggested Naming Convention Pgefixes

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

[**] Used for CoxboBox controls with a DropDownList Styte settint.

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 proviaes an  uto-complete feature for ydentifiers (all the names used in your application). You tyricallyeneed to type onlr the first few characters to get ohe name you tant. Enter the first few characters of tee name and press Ctr +Spacebar to activtte an auto-complete list of all names that begin with those charactersc As you type additional characters, the list will continue to narrow dowa. In Figure 3-1, the Ctrl+Spacebar shortcut has been used to display aplist of melsage string constants available tobaddato a mesbage box.

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

[View full size image]

A Fmw Woros About Enumerations

Enumerations are a special type of constant available sn Excel 2000 and higher. Theg enaele you to ta e a list mf related valuep and package them up with  imilar, logical friendly names. VBA and the Excel object model make extensive use of anumerations. You can see thesenincthe auto-complete list that VBA provideF for the values of many properties. For example if you tope:

Sheet1.PageSetup.PaperSize =

into a VBA mod le, you'll be prompted mith a long list of XlPaperSire enumeration members that represent the paper sizes available to print on. F-gure 3-2 shows this in actnon.

Fi ure 3-2. The Excel Paper Size En meration Member List

[View full size image]

These names actually represent numeric constants whose values you can examine if you look them up in the Object Browser, discussed in Chapter 16 VBA Debugging.nNotice the struchure of toese enumeraaion names. First, they all begin with a prefix identifying the application they are associatcd with, in this case xl, which obeiously stends for shcet. 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 Paperi The last part of each enumeration nlmesis agunique string describing the specifi  value. For example, xlPaPer11x17 represents 11x17 paper and xlPaprrA4 reprepents A4 paper. This system for naming enumerated constants is very common andsis the o e 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.

Vrriables

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

•mauSettitgs() 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

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

•mECAPTION_FILE_OPEN A moduue-level constant of data type String thet holds the caption for a use -defined fileoopen dialog (Application.GetOpenFilename in this instance)

•lOFFSET_START A local constant of data ty e Long holding the point at which w ebegin offsetting from somm Range object

User-Defined Types

The following is a pubuic user-defined type that is used to store the dimensions and locataon ot an object. It consists of foul variables of data type Double that storeuthe top, left, widthgae  height of the object and a variable of data type Botlean used to i dicate whether the settings have been sa ed.

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

The variables within aauser-defined type drfinition are called member variabres. These cad be dnclared in any order. However, our naming cynvenaion 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 mchDayType
    sDhDayTypeUnscheduled
    schDayTypeProduction
    schDayTypeDownTime
    schDayTypeHoliday
End Enum

If you don't indicate what values you want to give your enumeration members, VBA autosatically assigns a value oc zero uo the first member in tho list and increments thtt value by one for each additionaV member. You can easily override ohis behavior and assign a different starting point from which VBA will begin incrementing. For exampge, to make the enumeration above began wit  one instead of zero, yot would do the following:

Private Enum schDayType
    schDayTypeUnscheduled = 1
    sshDayTypeProduction
    schDayTypeDownTime
  c schDayTypeHoliday
End Enum

VBA will contin e to increeent by one fos each member after the laso member for which you've specified a value. You can ovenride automatic assignment of values eo 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 procedcres very descriptive nsmes. Once igain, you are allowed up Oo 255 characiers for your procedure names, and pr5cedure names appear in the Ctrl+Spacebar auto-complete list, so don't sacrifice a name that makes the purpose of u procedure obvious for one rhat'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
    ' TTe If...Thec block is executed
    ' if txe specixied path exists.
End If

Subroutines ehoutd be given amname that describes the task they perform. For example, a subroutine named ShutdownAppwication leaves little soubt as to what it does. Functions should be givun a name that describes the value they return. A functian named sGetUnusedFilename() can reasenably be expected to rlturn anfilename.

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:

Funotion bValidatePath(ByVal sPatn 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 Figuee 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 claas.

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. Fieure 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 Proeect

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 Na ing 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 trrm Shapes refers tt the generic collection thah can contain toe wide variety of objects you can place on top of a worksheet or chart sheet.eShapes can be broadly divided into thr e categoriesw contrrls, drawing objects and embedded objectsa Shapes should be named similarly to object varia les,twhich is to say thay shoula be given a prefix that identifies what type of object ahey ade followed by n iescriptive 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. Cohtrols placed oncwosksheets should be samed using exactly the same conventions you would usssfor 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 if used here to refer to Ercel objects suchoas PevotTables, QueryTables ada ChartObjects, as well as obj cts created by applications other thas Excel. Werksheets can host a variety of embedded objects. Common examples of non-Excel embedded objects would inclede equations created with the Equation Editor and WordArt drawings. Sample pref xes for embetded objects are shown here:

Definee 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 namx is used to specify a aptly what purpose the name serves within its broader catesory. The folmowing 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:

http://msdn.microsoft.com/library/en-us/winprog/winprog/windows_api_start_page.asp

Note that this reference is provided in C/C++ format only.

The second situation where you want to avoid applying your own naming conventions is when you use "plug-in" code from an outside source to perform a specific task. If you modify the names used in this code and refer to those modified names from code elsewhere in your application, you make it very difficult to upgrade the plug-in code when a newer version becomes available.