Learn Access 2003 VBA with The Smart Method

276

www.LearnAccessVBA.com

1: General rules

1-1

Apart from prefixes and spaces exactly the same name is used for the field name, caption (for the

form control that maintains the data), report column header and code variable name. We call this

the “Cradle to the Grave Naming Convention".

Good:

Field name: CustomerFirstName (table name prefix)

Field caption: First Name (space added for clarity)

Report column header: First Name

Form caption: First Name

Code variable:

strFirstName

(data type prefix, no spaces)

Bad:

Field name: CustFName

Field caption: Christian Name

Report column header: Given Name

Form caption: Name

Code variable:

strFNm

Believe it or not the second example is, almost universally, what you will find in the real world of

commercial applications. Badly informed managers often erroneously believe that hacking together

low-quality applications will result in them being completed faster. A very important responsibility

of your job as a development professional is to educate and inform so that this doesn’t happen!

1-2

There must only be one exit in any sub or function.

Code that has multiple exits is confusing to read and causes problems with cleanup code (such as

closing recordsets, de-referencing objects, destroying objects and ensuring that warnings are always

re-enabled).

Good:

Function CodeToName( strCategory as string) as string

Dim strReturn as string

IF strCategory = “MAN" Then

strReturn = “Manchester"

Elseif strCategory = “LHR" Then

strReturn = “London Heathrow"

Else

strReturn = “Unknown"

End If

CodeToName = strReturn

End Function

(Error handling code not shown)

Bad:

Function CodeToName( strCategory as string) as string

IF strCategory = “MAN" Then

CodeToName = “Manchester"

We can trace the footsteps of St. Patrick almost from his cradle to his grave by the names of

places called after him.

E. Cobham Brewer 1810–1897. Dictionary of Phrase and Fable.

Appendix A: The Rules

© 2007 The Smart Method Ltd

277

Exit Function

Elseif strCategory = “LHR" Then

CodeToName = “London Heathrow"

Exit Function

Else

CodeToName = “Unknown"

Exit Function

End If

End Function

Be particularly careful not to violate the single exit rule in error handling code.

Good:

Private Sub txtFilmYearReleased_Exit(Cancel As Integer)

On Error GoTo ErrorHandler

Me.lblHelp.Caption = ""

CleanUpAndExit:

Exit Sub

ErrorHandler:

Call MsgBox("An error was encountered" & vbCrLf & _

vbCrLf & _

"Description: " & Err.Description & vbCrLf & _

"Error Number: " & Err.Number, , "Error")

Resume CleanUpAndExit

End Sub

Bad:

Private Sub txtFilmYearReleased_Exit(Cancel As Integer)

On Error GoTo ErrorHandler

Me.lblHelp.Caption = ""

Exit Sub

ErrorHandler:

Call MsgBox("An error was encountered" & vbCrLf & _

vbCrLf & _

"Description: " & Err.Description & vbCrLf & _

"Error Number: " & Err.Number, , "Error")

End Sub

1-3

Use mixed case names, no underscores and capitalised first letter throughout for sub, function,

variable, field, object and control names.

Good:

strCompanyPostCode

txtCompanyPostCode

GetCompanyPostCode()

Bad:

strCompany_Post_Code

txtcompanypostcode

GetCompanypostcode()

By never using underscores in your own sub and function names it will always be obvious which

subs are event handlers.

By never using underscores in variable names it will always be easy to differentiate between

variables and constants.

Learn Access 2003 VBA with The Smart Method

278

www.LearnAccessVBA.com

1-4

Name variables in the form Noun or Noun–Verb.

Because of our Cradle to the Grave Naming Convention just about every variable name that relates to a

field will begin with a noun. This is because every entity (table name) in the system is usually a

noun. For example:

strCustomerFirstName

strCustomerLastName

strCustomerPostCode

Boolean variables often include a verb.

Good:

blnCustomerIsValid, blnCustomerHasCar

Bad:

blnIsValidCustomer, blnHasCarCustomer

1-5

Name subs and functions as in the spoken word.

All function and sub names should read just like the spoken English word. This makes code less

quirky and nearer to “real life".

Good:

AddCustomer

DeleteCustomer

CalculateMovingAverage

GetCustomerByPrimaryKey

Bad:

CustomerAdd

CustomerDelete

MovingAverageCalculate

CustomerGetByPrimaryKey

You should be aware that there is a contrary school of thought (to which we do not subscribe) that

suggests that subs and functions should be named in the form of noun-verb. The advantage of this

approach is that an alphabetic listing of all sub and function names will group all subs relevant to a

specific entity (such as Customer) together.

1-6

Never use abbreviations for table, field—or any other—names.

Example: A table contains Customer data. One of the fields in the table contains the Customer’s first

name.

Good:

strCustomerFirstName

Bad:

strCustFNm, strCstmFName

If you were playing Charles Simonyi’s game and you needed to guess which name another

programmer had come up with for a variable containing information of data type string that

contains a Customer’s first name you’d probably win (with strCustomerFirstName) in the first

example above but would probably lose with the second two.

A good yardstick for choosing a name is to try to imagine that there is an extraordinary reward

for two programmers if they can independently come up with the same program text for the

same problem. Both programmers know the reward, but cannot otherwise communicate. Such

an experiment would be futile, of course, for any sizable problem, but it is a neat goal. The

reward of real life is that a program written by someone else, which is identical to what one's

own program would have been, is extremely readable and modifiable.

Dr. Charles Simonyi (formerly Microsoft's Chief Architect)

Appendix A: The Rules

© 2007 The Smart Method Ltd

279

A long time ago, when visual basic didn’t have a compiler and memory was a precious commodity,

there was a small performance gain to be had by abbreviating names. There isn’t any more (and

hasn’t been for some years). So why do 21

st

century programmers still create cryptic, bug prone, and

difficult-to-maintain code by abbreviating names. It is one of the great mysteries of life!

Some programmers argue that you don’t have to type as much if you use abbreviations. You may

save a few keystrokes when you write the code but you’ll waste many hours later on when you have

to analyse every sub in order to establish what your cryptically named variables actually contain.

1-7

Name tables, fields—and everything else—in the singular.

As mentioned in the previous rule, One of the goals of our naming convention is that programmers

should be able to intuitively guess the correct name of any variable or function.

The use of plurals makes this goal more difficult. Consider a GetCompanyType( lngCompanyType)

function that returns zero, one or more company records. Without this rule programmers would

have to guess whether the correct function was GetCompanyType() or GetCompanyTypes().

This problem is avoided by only ever using singular names for variables, tables, subroutines,

directory (folder) names, web page names, file names and field names.

The single exception to this rule is in the naming of object collections. While we haven’t covered

creating your own object collections in this book you can see that Microsoft adhere to this standard

within the Access object model. For example the Forms collection contains many Form objects and

the Controls collection contains many Control objects.

About Table Naming

You should be aware that some database designers turn this convention upside down and make all

table names plural. It is (vastly) preferable to enforce an “everything in the singular" convention as

field names must preficed with the table name (see table and field naming rules) and would look very

confusing in the plural.

1-8

Do not use “Magic Numbers".

Never use numeric values as control variables. Instead, include a globally visible constant.

Good:

GetCompany( TSM_NORTH_WEST_AREA_ONLY )

Bad:

GetCompany( 23 )

1-9

Strongly type all function and sub parameters.

Good:

Function GetCustomer( lngCustomerID as Long )

Bad:

Function GetCustomer( lngCustomerID)

1-10

Declare all parameters ByVal unless there is a good reason to declare them ByRef.

Good:

Function GetCustomer( ByVal lngCustomerID as Long )

Bad:

Function GetCustomer( lngCustomerID)

Function GetCustomer( ByRef lngCustomerID)

Both of the two “bad" cases above are functionally equivalent because ByRef is the default method of

passing arguments.

Good reasons to pass parameters ByRef would include code that must be heavily optimised for

speed and a low memory footprint or the requirement to return multiple values from a sub or

function.

1-11

Do not use globally scoped variables.

Learn Access 2003 VBA with The Smart Method

280

www.LearnAccessVBA.com

The use of global variables violates the concept of encapsulation. Global variables also result in

buggy code that is difficult to maintain and prevents code re-use as code becomes reliant upon a

supporting infrastructure.

Note that this rule does not apply to global constants.

1-12

Do not use the addition operator for concatenating strings.

Use

&

for concatenating strings and

+

only for arithmetic operations.

1-13

Avoid Exit For and Exit Do

Bailing out of loops can cause the same problems as having multiple exits in subs as it makes code

less readable and more prone to bugs. Unless there is no reasonable alternative avoid doing this.

1-14

Use the Call statement when calling subs and functions.

Consider the following example:

Without the Call statement

strCustomerName = GetCustomerName( lngCustomerID)

DeleteCustomer lngCustomerID

MsgBox “You have successfully deleted customer: “ & strCustomerName

With the Call statement

strCustomerName = GetCustomerName( lngCustomerID)

Call DeleteCustomer( lngCustomerID)

Call MsgBox( “you have successfully deleted customer: “ & strCustomerName )

The second example reads more cleanly than the first because the three function calls use identical

syntax (brackets de-lineate arguments). For this reason Call should be used in every case when

calling functions, subs and object methods.

1-15

Never rely upon the default properties of objects.

VBA has a very confusing “feature" in that every control has a default property…

txtCustomerFirstName.Value = “John"

and

txtCustomerFirstName = “John"

…are functionally equivalent because Value is the default property of a Text Box control.

The default property feature has (thankfully) been removed from the latest versions of stand-alone

VB (VB.Net and VB 2005) showing that Microsoft also agree that it isn’t the best feature in the world.

Never use default properties in your VBA code as they make the code less readable and more prone

to error (as the actual property being manipulated must remain in the programmer’s memory).

If you use an Exit Do, people will think less of you.

Button seen at programmer’s convention – unknown author.