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.

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.

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.

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

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.

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

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.

Table 3-1. A Naming Convention for Variables, Constants, User-Defined Types and Enumerations

Element	Naming Convention
Variables	DescriptiveName
Constants	DESCRIPTIVE_NAME
User-defined types	Type DESCRIPTIVE_NAME DescriptiveName End Type
Enumerations	Enum GeneralDescr GeneralDescrSpecificName1 GeneralDescrSpecificName2 End Enum

The Scope Specifier ()

g Public

m Module level

(nothing) Procedure level

The Array Specifier ()

a Array

(nothing) Not an array

The Data Type Specifier ()

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

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.

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

Dictator Applications

All of the architectures we have considered so far have sought to enhance Excel in some way to improve the end user's experience when using our application. In contrast, dictator applications seek to completely take over the Excel user interface, replacing Excel's menus with their own and exercising a very high level of control over the user. In the ideal dictator application, users cannot tell they are inside Excel.

These applications are created in Excel to use the features Excel provides, but those features are entirely controlled by the application and rarely (if ever) exposed to the user. Instead, the user interface is made up of tightly controlled data-entry worksheets and/or userforms, designed to appear like any other Windows application. These applications typically require large amounts of code to be able to have that level of control, but that degree of control enables us to write full-scale, fully functional Windows applications, on a par with any that can be written in Visual Basic or other "mainstream" application-development platforms. Indeed, by building our application within Excel, we are immediately able to utilize the incredible amount of functionality Excel provides.

If there is a requirement for our dictator applications to work in Excel 97, we are not able to have userforms and menu items available at the same time, because userforms are always modal. We therefore have the choice to base our user interface on using worksheets and menus, using userforms with buttons to navigate between the forms or by building modeless forms using VB6. Excel 2000 introduced modeless userforms, enabling us to make both userforms and menus available and allowing us much more flexibility when deciding whether to use a form or worksheet for the user interface. For this reason, we recommend that, if possible, Excel 2000 should be set as the lowest-level version dictator applications are designed to work within.

As dictator applications get more and more complex, they will often start to use functionality that only exists in the most recent versions of Excel (such as the XML import/export introduced in Excel 2003), so youas the designer of the applicationneed to decide what should happen if the application is opened in an older version. If the functionality being used is a core part of the application, it is unlikely the application will be usable at all in older versions. If the use of the new features can be limited to a small part of the application, it might make more sense to just disable that menu item, or provide separate routines for older versions to use. Making use of new Excel features will often result in compile errors if the workbook were to be opened in an older version, so many dictator applications use a "front-loader" workbook to do an initial version check, check whether all external dependencies (such as Outlook) are available, and then open and run the main application workbook if all the checks are okay. If the checks fail, we can provide meaningful error messages to the end user (such as "This application requires Excel 2000 or above and will not work in Excel 97").

There's no escaping the fact that dictator applications are much more complicated than either self-automated workbooks or application-specific add-ins and will require an intermediate to advanced-level Excel/VBA developer to create and maintain them. Although this type of architecture can get very complicated, the complexity can be mitigated by following the best-practices advice discussed in Chapter 3 Excel and VBA Development Best Practices (general advice) and Chapter 6 Dictator Applications (specific advice for dictator applications).

After the decision to build a dictator application has been made, we have an incredible amount of flexibility in terms of physically creating the application. The data could be stored in one or more separate workbooks, local databases (for example, Access databases), or a central database (for example, SQL Server). We could decide to include all the code in a single workbook or have a small "core" add-in, with numerous little applets that plug in to the core to provide the functionality, where each applet performs a single, specific task. The decision will probably be a trade-off between (at least) the following considerations:

• A single-workbook structure is easier for a single developer to maintain, because everything is in the one place.

• A multiple-workbook structure is easier for a team of developers to create, because each developer can work on his own applet without conflicting with another team member.

• If a multiple-workbook structure is built so each plug-in applet is not loaded until it is first used, the initial opening of the core add-in will be quicker than loading the full application of the single-workbook structurealthough modern PCs might make that difference appear immaterial.

• A single-workbook structure must be updated in its entirety, but the applets of a multiple-workbook structure can be updated and deployed independently.

• The code required to implement a multiple-workbook plug-in architecture is quite complex, and might be too complex for the intermediate VBA developer to fully understandalthough we explain it in Chapter 11 Interfaces.

Requirements of a Dictator Application

To look and operate like a standalone Windows application, a dictator application needs to modify many Application properties, from turning on IgnoreOtherApplications (so double-clicking an XLS file in Explorer will not use our instance of Excel) to turning off ShowWindowsInTaskBar in Excel 2000 and above (because we may have multiple workbooks to be managed under program control), as well as hiding all the command bars. Unfortunately, Excel will remember many of those settings, to reuse them the next time Excel is started, so every dictator application must start by recording the existing state of all the settings that will be changed and restore them all when it closes. If the code to do this is written as two separate routines and assigned shortcut keys, they also provide an easy way to switch between the application's display and Excel's during development.

After a snapshot of the user's settings has been taken, the dictator application can set the application properties it requires; it then needs to lock down Excel to prevent users from doing things we do not want them to do, including the following:

• Hiding and disabling all the command bars (including the shortcut command bars), then setting up our own.

• Protecting our command bars and disabling access to the command bar customization dialog.

• Disabling all the shortcut key combinations that Excel provides, and then optionally re-enabling the few we want to be exposed to the user.

• Setting Application.EnableCancelKey to xlDisabled at the start of every entry point, to prevent users stopping the code.

• When using worksheets as data-entry forms, we don't want the user to be able to copy and paste entire cells, because that includes the formatting, data validation, and so on. Therefore we need to turn off drag and drop (which does a cut and paste); trap both Ctrl+X and Shift+Delete to do a copy rather than a cut; and trap Ctrl+V, Shift+Insert, and the Enter keys to ensure we only ever paste values.

Having locked down the Excel environment while our application is running, we need to provide a mechanism for the developers to access the code, to enable them to debug the application. One method is to set a global IsDevMode Boolean variable to True if a particular file exists in the application directory or (more securely) depending on the Windows username. This Boolean can then be used throughout the application to provide access points, such as enabling the Alt+F11 shortcut to switch to the VBE, adding a Reset menu item and/or shortcut key to switch back to the Excel environment and not setting the EnableCancelKey property, to allow the developer to break into the code. The Boolean can also be used within error handlers, to control whether to display a user- or developer-oriented error message.

Structure of a Dictator Application

A typical dictator application uses the following logical structure:

• A front-loader/startup routine to perform version and dependency checks and so on

• A main "core" set of routines, to do the following:

  Take a snapshot of the Excel environment settings and to restore those settings

  Configure and lock down the Excel application

  Create and remove the application's command bars

  Handle copying and pasting data within the worksheet templates

  Provide a library of common helper routines and classes

  (Optionally) Implement a plug-in architecture using class modules, as described in Chapter 11 Interfaces

• A backdrop worksheet, to display within the Excel window while userforms are being shown, usually with some form of application-specific logo (if we are primarily using forms for the user interface)

• Multiple independent applets that provide the application's functionality

• Multiple template worksheets used by the applets, such as data-entry forms or preformatted report templates