It's amusing to look at applications you wrote a year or two ago. If you're constantly improving yourself and learning new techniques, the approaches demonstrated in your older applications can seem outdated now, almost embarrassing. However, even if your development style today is different from yesterday, it would be nice to still be able to understand the structure of applications you wrote in the past and to be able to debug or upgrade their code. These tasks are facilitated by the use of standardized naming conventions. In this chapter, I detail for you the following:
"I learned the value of naming conventions years ago. Before I was at Microsoft, I was working on an accounting application for a client and needed to hire a contractor to do some of the work on the project. At the time, I didn't have any formal naming conventions, or a specific structure or style that I wanted the code to look like-I just wanted the app to work. Well, the contract coder did his part of the application, and it seemed to work fine. Some time later, however, I had to modify the application, and I couldn't figure out what the code did. For example, his code looked like this: Barney = 500 Do While Fred < Barney ... Fred = Wilma + 1 ... Loop "My confusion stemmed from the fact that he had named all of his objects and variables after cartoon characters, and they had no consistency or link to the application at all!"
Tod Nielsen, General Manager, Microsoft Access Business Unit
Developers by nature have a love/hate relationship with naming conventions. Such standards are often seen as slowing the development process, increasing the size of object names and files, and stifling true programming creativity. And yet, without order, the laws of entropy invariably draw every project toward incoherent spaghetti code (or perhaps spaghetti objects). Thus, few developers would argue against the need for an ordered approach to development, but they want the least intrusive system.
Sorry, but you can't have it both ways! A system that is comprehensive and applied consistently will also, by nature, be intrusive. If you want to apply a naming convention to your objects, you will incur a penalty of a few keystrokes every time you type an object name. For a detailed explanation of why the small pain of extra keystrokes produces a large gain, refer to "Why Use Naming Conventions?" in Chapter 5, "Creating Naming Conventions."
In Chapter 5, I describe the methods you might use when creating your own naming conventions; you may want to review that chapter before proceeding for two reasons:
In this chapter, I detail for you the Leszynski Naming Conventions (LNC), a set of standardized approaches to naming objects during Access development. These naming conventions were born of necessity because some members of my staff would spend all day in Access development, year after year. They were also born of a different need-a void that existed in the marketplace due to a lack of consensus about development styles among leading Access developers.
LNC began in the days of Access 1.0, when it was referred to as L/R (the Leszynski/Reddick conventions). The L/R conventions were distributed broadly-with over 500,000 copies in print, and have become the most widely used conventions in the Access community. Over the last few years, we have received feedback about L/R from hundreds of developers and companies, and we have tried to accommodate some of their input, as well as our ongoing experiences, into LNC.
LNC improves upon the previous Access style by considering developers who work with multiple Microsoft development tools. Access, Visual Basic, Office, SQL Server, and other Microsoft products have more in common in their 97 versions than in any previous iterations. Consequently, this Access style dovetails with the LNC development style for the Microsoft toolset, as detailed in Appendix C, "Leszynski Naming Conventions for Microsoft Solution Developers."
I will use the terms naming conventions, style, and LNC interchangeably throughout this chapter.
Naming conventions are one of the foundation elements of your overall development style. We developed our naming conventions primarily to achieve the following four objectives (as described in detail in Chapter 5, "Creating Naming Conventions"):
To meet these objectives, we create and apply consistent naming conventions to the Access objects. The following are target objects for naming conventions:
Because object names are the foundation upon which you build your entire application, it is almost impossible to change them once you begin development. Therefore, you will not find it cost-effective or time-efficient to retrofit naming conventions into your existing applications. For new applications, however, you should apply these conventions consistently from the moment you create your first object in a new database application file.
Our naming conventions rely primarily on leading tags, which are several characters placed before an object's name (for example, qryOrderByMonth). This approach, known as Hungarian Notation, is defined in Chapter 5, "Creating Naming Conventions." Leading tags provide the following benefits:
If you are averse to Hungarian Notation for some reason and prefer trailing tags, LNC will still work for you. However, LNC prescribes no standard for locating and punctuating trailing tags. You will have to decide to offset them with underscoring (OrderByMonth_qry), by capitalization (OrderByMonthQry), or by some other technique.
Using trailing tags on database objects is problematic when your application also contains VBA code. The primary justification given by developers who prefer trailing tags on database objects is that it allows the objects to sort by base names rather than tags in ordered lists. However, such developers often still use leading tags and prefixes in their VBA code because there is no compelling argument for trailing tags on VBA objects such as variables. If you mix your styles like this, be prepared to justify your lack of consistency.
Because some developers, especially newer ones, prefer to minimize the complexity of a naming convention, LNC provides two levels for Access users. Level One has the minimum realistic subset of tags but provides lesser detail about the application. It is intended for users whose work is centered around the Database window and who develop database objects rather than applications. Level Two provides greater detail and the flexibility to create extensions. It is intended for application developers building expert solutions.
For purposes of this chapter, I have created the following standardized terminology for grouping objects. I will use these group names when discussing naming conventions:
In LNC, object names are constructed using the following syntax for Level One:
For Level Two, the syntax varies slightly:
The brackets indicate optional syntax elements. Notice that, for Level One, the BaseName element is required and the tag is optional in some cases. At Level Two, the tag element is required even though the BaseName is not in some cases. (This will be explained later in this chapter; see "Naming Conventions for VBA Objects.") Table 6.1 shows sample object names using these constructions.
Table 6.1 Object Names Constructed in LNC Format
Note in the syntax diagrams that the case of each element reflects its case in actual use. The element tag is in lowercase because the tags themselves are always lowercase.
A prefix is an identifier that precedes a tag and clarifies it narrowly. Prefixes describe one or more important properties of an object. For example, a Long Integer variable that is public in scope (declared Public) has a prefix p, as in plngRecNumMax. Prefixes are one or two characters long and lowercase. Multiple prefixes can be used together on one object, as in ialngPartNum, where i and a are both prefixes.
A tag is a character phrase placed against an object base name to characterize it. In object-oriented programming terms, the tag is basically an identifier for the class. At Level One, tags define an object's general class: for example, qry for a query of any type. At Level Two, tags define the specific subclass: for example, qdel for a delete query.
Note that the word class here refers to a naming convention construction, not an object model construction. For example, there is only one Query (or QueryDef) class object in Access, and the data action (delete, update, etc.) is determined by its SQL statement rather than its class. LNC prescribes several tags for this one Access class.
Tags are three or four characters long for readability and to allow for the hundreds of combinations necessary as the Microsoft Office object model grows over time. They are always to the left of the base name and in lowercase, so that your eye reads past them to the beginning of the base name.
Tags are ideally created to mnemonically represent the word they abbreviate, such as "frm" for "form." However, some tags may not seem fully mnemonic for two reasons. First, the most appropriate, or obvious tag for a particular object may already be assigned to another object. Second, common objects (those with similar properties and usage) may exist in multiple Microsoft applications; therefore, the tag for one may be used to represent similar objects in other products, even if the names differ. For example, an Access Rectangle object is almost identical in structure and purpose to a Visual Basic Shape object. Since our Visual Basic conventions have existed longer than our Access conventions, I used the Visual Basic Shape object tag shp to also represent the Access Rectangle.
When you name a particular object, the base name is the starting point-it is a name you would use anyway if you had no naming conventions. For example, you might use Customer as the name for a table filled with customer records. The LNC guidelines for creating base names is driven by a set of guidelines discussed in the section, "Creating Database Object Base Names," later in this chapter.
A qualifier is an extension following the base name that provides context to the specific use of an object. Unlike prefixes, which detail properties of the object (for example, that the variable has public scope), qualifiers describe how the object is being used in a context. For example, plngRecNumMax is obviously the maximum record number in an application that could also have variables for the minimum (plngRecNumMin) and current (plngRecNumCur) record numbers. Qualifiers are short and written in upper- and lowercase, using the list in Table 6.2.
Table 6.2 Suggested LNC Qualifiers
|Curr||Current element of a set|
|First||First element of a set|
|Hold||Hold a value for later reuse|
|Last||Last element of a set|
|Max||Maximum item in a set|
|Min||Minimum item in a set|
|Next||Next element of a set|
|New||New instance or value|
|Old||Prior instance or value|
|Prev||Previous element of a set|
|Sub||Subform/subreport (Level One only)|
Suffix elements provide specific information about the object and are only used as "tie breakers" when more detail is required to differentiate one object name from another. These are the only elements in the syntax for which our naming conventions do not specify standardized values. You will create suffix items as needed by your company, development team, or application. For example, a series of queries that summarized the best sales performance by state would need the state name in the object name to properly qualify it, as in qsumSalesPerfBest_AK. Placing the state name at the very end of the name as a suffix item allows the entire collection of related queries to sort together, like this:
Since the suffix is the last piece of information in a name, it can be easier for the eye to find if delimited from the rest of the object name with an underscore, as shown. Use of the underscore is optional.
The building blocks of your Access application are its database objects. When you create a base name for an object, consider its purpose, the approaches used in naming associated objects, and the following rules of thumb.
Observe the following rules when developing a base name for a new database object:
The first two rules also apply to the other naming convention elements: prefixes, tags, qualifiers, and suffixes. These elements should never include spaces or special characters.
You should abbreviate object base name elements wherever possible using a standardized abbreviation table such as Table 6.17 in the section "Standardized Abbreviations." You can extend LNC with your own standard abbreviations as well. You should create and use standardized terminology in your applications wherever possible; for examples, see "Standardized Terminology."
LNC includes some constraints and suggestions for object name lengths.
I target table name lengths at a 15-character maximum for the following two reasons:
Beyond the 15-character target, I absolutely limit table name lengths to 30 characters, which maintains compatibility with the table name length limit in SQL Server. For other objects, I target a 30-character limit as well because Access shows no more than the first 30 characters of object names in the default width of any of its lists or property grids.
An object name that is driven by a table must include the base name of that table. Thus, for the tblCust table, the primary query would be qryCust, the primary form frmCust, and so forth. Queries, forms, and reports that are sourced from multiple tables should reflect the base names of all the tables if it is practical. If not, you must decide which tables are the primary tables and list as many as possible in the name.
Generally, in a multi-table query, form, or report, the most "important" tables are not necessarily the first and second, but more often the first and last. So, a query joining tblCust to tblAddr to tblPhone to get the phone numbers for customers would be named qryCustAddrPhone if the address information is included in the query result. If the address information is used simply to join to the phone numbers and is not displayed, the query would be simply qryCustPhone.
Bound control base names on forms and reports are always equivalent to the base name of the bound field (the ControlSource). For example, a text box tied to the LastName field is named txtLastName.
As a part of standardizing terminology, I adhere to the concept of a centralized data dictionary. This principle dictates that any fields in the data structure having the same name must have the same properties and data purpose. For example, if the LastName field in tblCust is of type Text 30 and holds the customer last name, then any other field named LastName in the same application must have the same type, length, properties, and purpose. If your application needs last name fields for both customers and dealers, this rule dictates that you name them differently (such as CustLastName and DlrLastName).
Applying the centralized data dictionary principle also means that table fields do not get leading prefixes or tags because I prefer my data dictionaries to be platform-neutral. That way, a field does not have to be renamed if data is upsized or ported to a platform with different data types. A table is still called a table in SQL Server, so moving tblCust there from Access would not require renaming.
However, if tblCust had a field lngCustID defined as a Long Integer in Access, moving the database to SQL Server would require a field rename to intCustID because SQL Server uses the data type name Integer to mean the same as the Access Long Integer. Because renaming fields affects all dependent objects and code, avoid it at all costs. Therefore I would call the field simply CustID.
If field tags are part of your development style and you must use them, LNC provides the tags listed in Table 6.3.
Table 6.3 Database Field Object Tags
|AutoNumber (Random non-sequential)||idn|
|AutoNumber (Replication ID)||idr|
As much as is practical, LNC maps similar Jet and SQL Server data types to the same tag so that the data structure can be upsized with a minimum of object name changes. See Appendix C, "Leszynski Naming Conventions for Microsoft Solution Developers," for an expanded discussion of this subject.
Unlike tags, qualifiers and suffixes are acceptable in field names because they describe the object's data purpose and not its type.
Object base name elements should be ordered from left to right with respect to their importance, readability, and desired sort order. In the example from the previous paragraph, CustLastName is a better name than LastNameCust because the group name portion (Cust or Dlr) carries greater weight in an object's name than the specific item name (LastName or PhoneNum). Think of Cust as the name of a collection of customer-related items and this rule becomes clear-what you are really saying is that CustLastName is analogous to Cust(LastName) or Cust.LastName in Collection.Object terminology.
Some of you will naturally carry this example to its extreme and say that the Customers collection really has a Names collection with multiple elements, including Last, thus the representation of that idea as Cust.Name(Last) would lead to the field name CustNameLast instead. Such a construction model still fits within the rules of LNC, and I won't debate you against using it. In practice, however, such names often become fairly unreadable, even if they are accurate.
In Level Two of LNC, the following Access (and Jet) database objects require tags:
Level One also recommends that you place tags on every object name. However, Level One recognizes that nondevelopers may prefer to save time, effort, and complexity by leaving tags off of objects where the context is obvious while viewing the Database window. Thus, Level One users are required only to place the qry tag on queries in order to differentiate them from tables in any combined lists, such as the Choose the Table or Query combo box on Form and Report Wizards. Placing tags on other objects in the preceding list is optional.
I described earlier how difficult it can be to propagate name changes throughout a database, so if you are a casual user now but expect to become a developer later-and thus migrate from Level One of LNC to Level Two-you would be unwise to leave tags off any object names. Use the Level Two tags now on all objects.
Table 6.4 lists the Level One tags for Database window objects. Note that only one tag exists for each object type.
Table 6.4 Level One Database Window Object Tags
Though Level One is the simplified naming model, it is necessary to provide tags to identify subform and subreport objects specifically. The distinction between objects and subobjects is critical for nondevelopers who navigate using the Database window. Because it is not appropriate to open subforms and subreports directly from the Database window, they must be clearly identified and grouped using tags. Table 6.5 lists the Level Two tags for Database window objects.
Table 6.5 Level Two Database Window Object Tags
|Form (class module)||fcls|
|Form (dialog box)||fdlg|
|Form (lookup table)||flkp|
|Form (wizard main)||fwzm|
|Form (wizard subform)||fwzs|
|Macro (for form/report)||m[obj]|
|Macro (bar menu)||mmbr|
|Macro (general menu)||mmnu|
|Macro (shortcut menu)||mmct|
|Query (form/report source)||q[obj]|
|Query (data definition)||qddl|
|Query (form filter)||qflt|
|Query (make table)||qmak|
|Query (SQL pass-through)||qspt|
|Table (attached Btrieve)||tbtv|
|Table (attached dBASE)||tdbf|
|Table (attached Excel)||txls|
|Table (attached Fox)||tfox|
|Table (attached Lotus 1-2-3)||twks|
|Table (attached ODBC)||todb|
|Table (attached Paradox)||tpdx|
|Table (attached SQL Server)||tsql|
|Table (attached text)||ttxt|
|Table (many-to-many relation)||trel|
|Table (summary information)||tsum|
The tags for Level Two provide rich detail about the objects and sort objects with similar attributes. For example, lookup tables and their maintenance forms are often used repeatedly in multiple applications. The tags tlkp and flkp clearly identify these objects, making it easy for you to perform tasks such as importing all lookup-related objects from an existing database into a new one when using the object list in the Access Import dialog box.
If you like the idea of using tags to sort objects but still need access to the base name of the object, place each object's unmodified base name in its Description property. When you need to view database objects sorted by their base name, sort the Database window using the Description column. To add a description to a database object, highlight the object in the Database window and select View Properties.
In two special cases above as indicated by the [obj] placeholder, the conventions prescribe a single character tag added to the front of the full object name (including the tag) of a related object. This situation occurs when
Table 6.6 lists the Level One tags for control objects on forms and reports.
Table 6.6 Level One Form and Report Control Object Tags
These Level One control tags provide no differentiation of control type other than to distinguish labels, which do not interact with the user, from controls that can display or modify data. This level of detail is inadequate for applications where VBA code will be written behind forms or reports.
Table 6.7 lists the Level Two tags for control objects on forms and reports. A different tag is provided for each built-in control type. VBA code written behind forms and reports using this convention will reflect a control's type in its event procedure names (for example, cboState_AfterUpdate). The automatic sorting provided by this notation in the Access form and module design windows can be very helpful during development. The table lists controls by their Access internal names rather than by more commonly used terms.
Table 6.7 Level Two Form and Report Control Object Tags
|Bound object frame||frb||BoundObjectFrame|
|Footer (group section)||gft[n]||Section|
|Form footer (section)||fft||Section|
|Form header (section)||fhd||Section|
|Header (group section)||ghd[n]||Section|
|Page footer (section)||pft||Section|
|Page header (section)||phd||Section|
|Report footer (section)||rft||Section|
|Report header (section)||rhd||Section|
|Unbound object frame||fru||ObjectFrame|
The placeholder [n] in the headers and footers for reports indicates the GroupLevel index, as in gft0 for the first group footer.
All control tags are three characters long.
Appendix C, "Leszynski Naming Conventions for Microsoft Solution Developers," includes a listing of tags for ActiveX controls that can be used in Access applications.
The following list describes the database object prefixes and their usage. With the exception of zz for backup copies of objects, these prefixes are Level Two (i.e. developer) elements only.
Note that most of these database object prefixes use z as the first character. Database objects with such prefixes sort to the bottom of the Database window, below the user- or developer-oriented objects that are accessed more frequently.
Menu macros behave differently than standard macros; thus, they fall under separate guidelines when you create their names. Menu macros are used either for bar menus or for shortcut menus.
When creating menu macros, bar menu macros should be prefixed with mmbr, submenus (drop-down) with mmsb, and shortcut menus with mmct. The tags for command bar menu objects are similar: mbr for a menu bar (which includes its submenus), mct for a shortcut menu (also now called a popup), and tbr for a toolbar object.
Detailed tags like theses will help greatly when you are selecting from a list of menus to assign to the MenuBar or ShortcutMenuBar properties of a form or in the Startup properties dialog box. This convention also sorts menu macros by type in the Database window.
Because any saved submenu macro can be selected as the primary object in a shortcut menu, this convention gives you the flexibility to create component submenus, which can be called from both multiple bar menus and multiple shortcut menus and thus reused. The structure of command bar menus is different from that of menu macros; you cannot create a component submenu and use it by name in multiple bar menus. You can, however, make a copy of a submenu to another bar menu when customizing toolbars.
Table 6.8 shows examples of database objects, applying the various conventions in this section.
Table 6.8 Database Object Naming Convention Examples
|zhtxtUser||Hidden system text box|
|mfrmCust||Macro for form|
|zttfoxCustHist||Temporary Fox attachment|
|qupdCustBal_Dlr||Update customers that are dealers|
|trelCustAddrPhone||Link many addresses to phones|
When creating VBA object base names, remember that the base name must be descriptive even in the absence of its tag. For many programmers, the syntax Dim I As Integer for a loop variable is quite acceptable. Within LNC, however, the variable named I would become iintLoop. Single character variable names, especially without tags, are not allowed. Instead, create a list of short and standardized work variables to handle common daily needs. Table 6.9 suggests the LNC approach to commonly used variables.
Table 6.9 Standardized LNC Work Variables
|blnRet||Captures a True or False return value from a function call|
|cccDebug||A conditional compilation constant for turning conditional debugging on and off|
|dbsCurr||Database object variable for the current Access database (CurrentDB)|
|intErr||Preserves the value of Err|
|iintLoop||A counter for For...Next loops|
|intMsg||Captures a return value from a MsgBox() function call|
|intResult||Holds the result of math operations (also dblResult, lngResult, etc.)|
|intRet||Captures a numeric return value from a function call (also dblRet, lngRet, etc.)|
|intWork||Used for any temporary work (also dblWork, lngWork, etc.)|
|strMsg||Used to build long message box strings|
|strOrder||Used to build long SQL ORDER BY strings|
|strSQL||Used to build long SQL strings|
|strWhere||Used to build long SQL WHERE clauses|
Creating VBA object base names involves observing the same rules listed in the section "Rules for Base Names" for creating database object base names, as follows:
Note that the last rule is an expanded version of the corresponding rule for database objects, which states that table base names should propagate into names of dependent objects. In VBA, that rule expands to require a reference in variable names to objects of any type that they relate to. For example, a RecordSet variable created on tblCust should be named rstCust. Also, if a string array variable of part numbers astrPartNum has an Integer index variable, its name should include the array's base name: iaintPartNum.
There is no rule limiting variable name length, but common sense would dictate that variable names longer than 15 or 20 characters waste a lot of keystrokes. The VBA module editor shows roughly the first 30 characters of a procedure name by default, so 30 is suggested as the target maximum procedure name length.
Abbreviate VBA object base name elements wherever possible using a standardized abbreviation table, such as in Table 6.17 in the section "Standard Abbreviations." You can extend LNC by creating your own standard abbreviations as well. You should create and use standardized terminology in your applications wherever possible; for examples see the section "Standardized Terminology."
Procedure base names follow the construction ObjectVerb, where the Object portion describes the primary object type affected (often the same as the primary argument), and Verb describes the action. This style sorts functions and subs by their target object when shown in ordered lists:
In Level Two of LNC, the following VBA objects require tags:
Optional tags also are available for some types of procedures. By definition, if you are a Level One user, LNC assumes that you are not writing VBA code. If you are creating procedures, you are a Level Two user and should always apply Level Two tags and prefixes throughout your application.
In the syntax diagram earlier, I noted that base names are optional in some Level Two constructions. When programming in VBA in Level Two, the tag element is always required, but the base name is optional for local variables only. For example, a procedure that declared only one form object variable could legitimately use the variable name frm, which is a tag without a base name. Type structures, constants, and variables that have module-level or public scope must have both a tag and base name.
Visual Basic for Applications variable tags are noted in Table 6.10, Table 6.11, and Table 6.12, grouped by type of variable.
Table 6.10 Tags for VBA Data Variables
|Conditional Compilation Constant||ccc|
In the table above, note that Conditional Compilation Constant, Error and User-Defined Type are not true data types (created with Dim name As type) but rather programming concepts. A Conditional Compilation Constant variable is a flag of type Boolean, an Error variable is a Variant created with the CVErr() function, and user-defined types are unique constructs.
Table 6.11 Tags for VBA Object Variables
Table 6.12 Tags for Data Access Object Variables
|Database (any type)||dbs|
|Database (Lotus 1-2-3)||dwks|
|Database (SQL Server)||dsql|
|QueryDef (any type)||qdf|
|QueryDef (Lotus 1-2-3)||qwks|
|QueryDef (SQL Server)||qsql|
|Recordset (any type)||rst|
|RecordSet (Lotus 1-2-3)||rwks|
|RecordSet (SQL Server)||rsql|
|TableDef (any type)||tdf|
|TableDef (Lotus 1-2-3)||twks|
|TableDef (SQL Server)||tsql|
The tags dyn, snp, and tbl above for dynaset, snapshot, and table objects are directly relevant to users of Access 1 and Access 2. Starting with Access 95, these object types are allowed only as a subtype of recordset variables, thus the recordset tags rdyn, rsnp, and rtbl.
In Tables 6.11 and 6.12, tags for collection variables are made by adding s after the tag for the object type stored in the collection.
Even though I noted above that a tag by itself is a legitimate variable name, some variable tags shown (such as int) are reserved words and will not compile in VBA-such tags require a base name.
Table 6.13 lists the LNC tags for Automation server variables.
Table 6.13 Tags for Automation Server Variables
Note that variables for objects in the object hierarchy of a referenced type library can be dimensioned directly by class, as in this line:
Dim xlsapp As Excel.Application
Alternately, if the variable is created with late binding (i.e., as a generic object) rather than early binding (as an object class), the prefix o is added to denote an object variable:
Dim oxlsapp As Object
Set oxlsapp = CreateObject("Excel.Application")
Our naming convention for entry point variables into Automation server applications follows this syntax:
applicationtag [entrypointtag] primaryobjecttag BaseName
The item applicationtag is a three-character notation for the server application, and entrypointtag is three characters denoting the entry point used. The entrypointtag is optional and should be used when clarification is necessary (when variables for several entry points are declared in the same procedure), or when the entry point is not the standard Application object. The primaryobjecttag describes the ultimate class of the object (the one you intend to address with the variable). The BaseName is optional and clarifies the use of the variable, as with other VBA variables.
For example, the following code creates an Excel Range object and manipulates it.
Dim xlswksSales As Excel.Worksheet
Dim xlsrngYTD As Excel.Range
Set xlswksSales = GetObject("C:\Data\Sales.Xls", "Excel.Sheet")
Set xlsrngYTD = xlswksSales.Range("YTDSales")
If xlsrngYTD.Value < 100000 Then
MsgBox "Sales are lame.", vbOKOnly, "Get to Work!"
Set xlswksSales = Nothing
In this example, the Range object is technically several layers deep in the application hierarchy, and a purely accurate combination of tags and code structure would yield this line of code, which actually runs:
Set xlsappwkbwksrngYTD = _
In practice, of course, such nomenclature is unwieldy, and the shorter style is more friendly yet still accurate.
I prefer to show the server name in the variable declaration for clarity of code-while both lines below will run, the second is less ambiguous:
Dim xlsrng As Range
Dim xlsrng As Excel.Range
See the section "Creating Your Own Tags" for more discussion of Automation syntax.
The prefixes for VBA variables can be categorized into two groups: prefixes for scope and all other prefixes. Because the model for variable scope changed somewhat between Access 2 and 95, I will discuss scope prefixes first. The following prefixes are ordered by increasing (broader) scope.
Use no prefix for variables that are local to a procedure.
Place this prefix before variables that are declared locally to a procedure with a Static statement.
Use this prefix for module-level variables that are declared with Dim or Private statements in the Declarations section of a module.
Use this prefix to denote variables declared as Public in the Declarations section of a form or report module. Such variables are publicly available to other procedures in the same database only.
Use this prefix to denote variables declared as Public in the Declarations section of a standard module. Such variables are truly global and may be referenced from procedures in the current or other databases.
When used, scope prefixes always begin a variable name and precede any other prefixes.
In addition to scope, there are other characteristics of variables that can be identified by prefixes, as follows:
Use this prefix to denote a variable that is declared as an array, including a ParamArray argument to a function.
This prefix is placed before constants defined with the Const statement.
Use this prefix to denote a variable (usually of type Integer) that serves as an index to an array or an index counter for a For...Next loop.
This prefix is placed before object variables that reference Automation servers through late binding (and Object variable), where the tag denotes the type of server.
Use this prefix for variables that are arguments (parameters) passed in to a procedure and declared as ByRef, or not declared as either ByRef or ByVal (including a ParamArray), which implies ByRef.
Use this prefix to describe a variable that is declared as a user-defined Type structure. The variable should inherit the base name from the original declaration for the type.
Use this prefix for variables that are arguments (parameters) passed in to a procedure and declared as ByVal.
Because a prefix provides a very detailed description of a variable, the number of allowable prefix combinations is limited, as shown in Table 6.14.
Table 6.14 Allowable Prefix Combinations
|Any One of These...||...Can Come Before This|
|s, m, p, g, r, v||a|
|m, p, g||c|
|s, m, p, g, r, v||e|
|s, m, p, g, r, v||i|
|s, m, p, g, r, v||ia|
|s, m, p, g, r, v||o|
|m, p, g||t|
Variables require a unique prefix when declared Public in a widely distributed application. See the "Tags and Prefixes for Procedures" section below for more information.
Access 95 introduced sweeping changes in the area of constants. The changes most relevant to naming conventions include the following:
When creating constants, use a scope prefix (if appropriate), the prefix c, and the suitable tag for the constant's data type. To properly synchronize the tag and the data type, do not let Access assign the type; always use the full Const name As datatype syntax.
Constants require a unique prefix when declared Public in a widely distributed application. See the "Tags and Prefixes for Procedures," section for more information.
Whether to prefix and tag procedure names and how to do so are debatable subjects. This style neither requires nor encourages placing characters before a procedure name except in the situations discussed next.
Prefixes for Procedures
Procedures can have scope similar to that of variables-s (Static), m (Private), p (Public), or g (global Public). LNC supports the use of these scope prefixes on function names if they solve a particular need and are used consistently throughout an application.
If you are creating software for retail, for inclusion in the public domain, or for some other form of broad distribution, LNC requires that you prefix Public variables, constants, and procedures with a unique author prefix identifying you or your company. The prefix consists of two or three unique characters and an underscore and prevents your object names from conflicting with others in databases on a user's machine.
To create an author prefix, use your personal or company initials. For example, author prefixes for my companies are lbi_ for Leszynski Barker Inc., lci_ for Leszynski Company Inc., and kwc_ for Kwery Corporation. Before using your selected prefix, make an effort to determine if the prefix is already widely in use.
Tags for Procedures
The LNC style prescribes the following naming convention tags for procedures:
Use this tag on procedure names for code behind a form or report that is Private to the object. This tag clearly differentiates such procedures from Property procedures and event procedures.
Use this tag to differentiate class method procedures, which are method procedures defined in a class module and Public procedures in a form or report object (which are de-facto methods).
Use this tag on Property procedure names defined with Property Get, Property Let, and Property Set statements. This tag clearly differentiates such procedures from functions, subs procedures, and event procedures.
LNC does not require or suggest assigning a data type tag to functions to reflect their return value. However, if you have a specific need to tag procedures to reflect their return value type, use the appropriate tags from the section "Tags for Variables" and apply them consistently to all procedures in an application.
Occasionally, you might have a good reason to using macros to create action scripts. If so, apply the rules described earlier for VBA procedure base names to your macro base names. Macro groups in the Database window should utilize the macro prefixes and tags previously noted. Individual macros within a macro group do not have prefixes or tags except for event macros.
All macros for a specific form or report should be placed into one macro group with the form or report name as the macro base name. Within the macro group, create standard macros and event macros for the form or report. If a macro is tied to a form or report event, show the event name or an abbreviation in the macro name. For example, you would store macros related to frmCust in macro group mfrmCust. Event macro names in the group might include Form_Current and txtLastName_Change.
Macros that are not specific to a form or report should hold actions that have some common functionality or purpose. Use the same grouping methodology (related items together) that you would apply to locate procedures in VBA modules.
Table 6.15 shows examples of VBA variables applying the various conventions in this section.
Table 6.15 VBA Variable Name Examples
|Dim oxlsaBudget As Object||Excel.Application|
|Function lci_ArraySum (ParamArray ravarNum() As Variant) As Double||Company identifier|
|Public giaintPartNum As Integer||Global index into array|
|Const clngCustNumMax As Long = 10000||Const for max CustID|
|Function FileLock(ByVal vstrFile As String) As Boolean||ByVal argument|
Creating Your Own Tags
What do you do when LNC doesn't address a particular object naming need? First, contact me at firstname.lastname@example.org and let me know why so that we can improve the style for the benefit of all users. Second, consider whether what you are trying to do is covered by the style in some other way. For example, in your development team you call tables that link two other tables in a many-to-many relationship linking tables, and you want to create a new table tag, tlnk, as a result.
However, on examination of all table tags, you find trel already exists, defined as "Table (many-to-many relation)," which is the correct tag for what you need. Even though the nomenclature is not exactly what you use, it is better to use an existing tag than to create another one.
Finally, when other options are exhausted, you can create a custom tag to address your need. When creating a custom tag, use the following guidelines:
When you create a new tag, use a character combination that is mnemonic enough to uniquely shorten the word the tag represents, and only use characters from the root word or a generally accepted shorthand.
Table 6.16 Some Standard Tag Components
To create tags for object variables pointing to Automation server applications, start with the three-character MS-DOS file extension for the files created by the server application if unique and applicable (for example, xls for Excel files). If not unique, create a meaningful abbreviation of the application name. Add to the three-character abbreviation a single character for the actual object that serves as the entry point for the application, such as sht for "sheet" in Dim xlssht As Excel.Sheet.
For example, to create a tag for Automation with Shapeware's Visio program, which is an OLE server, use either vsd (the data file extension) or vis (a good mnemonic for Visio) as the basis for the tag, then add app for Application because the entry point to Visio's automation engine is a call to Visio.Application. Thus the tag and its use in variable declarations would look like the following:
Dim ovisapp As Object
Dim ovisdocHouse As Object
Set ovisapp = CreateObject("Visio.Application")
Set ovisdocHouse = ovisapp.Documents.Open("C:\VISIO\HOUSE.VSD")
In addition to object naming conventions, LNC proposes several standardized coding conventions for VBA procedures. The use of standardized coding conventions makes your code more readable when your are debugging, doing a code review for yourself or another developer, or documenting an application.
Coding conventions and style issues that are not specifically related to object names are detailed in Chapter 11, "Expert Approaches to VBA."
There are as many in-line VBA code commenting styles as there are Basic coders. Whatever convention you use, the keys are to be terse yet descriptive and be consistent.
LNC suggests placing the following minimum set of comments at the beginning of each procedure:
Comments placed on the same line as code should be separated from the code by two spaces. Comments placed on their own line should be no longer than 60 characters so they are displayed fully in the Access default module design view size.
Every procedure that can fail, which is virtually every procedure with more than a few lines, should have an error trap. You can create an error trap by placing the following line at the beginning of the procedure, after the header comments and before any other statements:
On Error GoTo procname_Err
The marker procname should be replaced with the full procedure name. The error handler is placed at the bottom of the procedure, denoted with the label procname_Err. At the end of the error handler, control is returned somewhere in the procedure, usually to a line label name procname_Exit that precedes a block of code immediately above the error handler.
To turn off error trapping during program debugging, LNC suggests that you place the On Error statement inside a conditional compilation directive such as the following:
#If pcccDebug Then
On Error Goto 0
On Error Goto procname_Err
Before running an application, you can enable or disable error trapping by setting the value of pcccDebug to -1 (True) or 0 (False) in the Conditional Compilation Arguments text box on the Module tab of the Tools Options dialog box.
Table 6.17 lists some of my standard abbreviations for use in building object names. This is not a comprehensive list and is meant only to give you a starting point for your own taxonomy.
Table 6.17 Standardized Object Name Abbreviations
|Sum||summary, summaries, summation|
When creating code comments, object names, help files, and system documentation, it is important to use terms that have an accepted and nonambiguous meaning. Build a list of standardized terms for your specific industry or application to ensure consistency. Table 6.18 provides a short list of standardized terminology. These terms are not required by LNC; they are only examples.
Table 6.18 Examples of Standardized Terminology
|Add||To create a new record. Select Add, Create, Enter, or New, and be consistent.|
|Beg||The start of a process.|
|Close||To close an open object.|
|Comment||A more familiar term for text originating with a human than Remark or Notes.|
|Desc||A description, often a long text string.|
|Edit||To change or modify.|
|Editor||The last person to change a record.|
|End||The end of a process.|
|Flag||A programming item with fixed set of values, usually True/False (a Boolean).|
|Key||An index used to find a record.|
|Max||The maximum, better than Most.|
|Min||The minimum, better than Least.|
|Open||To open an object.|
|Owner||The creator of a record, process, or object.|
|Save||To commit a record.|
|User||The person currently running an application.|
This chapter details comprehensive standardized naming conventions for Access. Application development examples found throughout the book will use these naming conventions.
© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.