Coding Conventions

Separators

Separators are a good measure to organize sections in a module. The main separator is a comment with 80 hashes followed by a short description of the section:

'################################################################################
'# Section

Class Module

The separation in a typical class module looks like this:

'################################################################################
'# Constants and variables

'################################################################################
'# Enum and Type

'################################################################################
'# Class

'################################################################################
'# Events

'################################################################################
'# Properties

'################################################################################
'# Methods

Code Module

A plain has less sections than a #Class Module:

'################################################################################
'# Constants and variables

'################################################################################
'# Enum and Type

'################################################################################
'# Methods

Object Module

An object module is based on an object like a form (Microsoft Access) or a worksheet (Microsoft Excel). Let's take a Microsoft Access form as example:

'################################################################################
'# Constants and variables

'################################################################################
'# Enum and Type

'################################################################################
'# Form

'################################################################################
'# Controls

'################################################################################
'# Methods

You notice the parallels to a class module, just the "Class" is here replaced by "Form" and the "Properties" are the "Controls".

Subsections

If the code should become lengthy (which it should not, but sometimes it happens), it is wise to subdivide the sections into subsections. The separator for a subsection is a lighter variant of the section separator built out of hyphens:

'--------------------------------------------------------------------------------
'- Subsection

The right choice of subsections affects the readability of code, typical use cases are the separation of

• public and private and / or
• functional domains

Applying at least one of these types of subsections should divide your code into smaller more easily maintainable parts and enhance at the same time the readability and coprehension of your code.

Organization

Organizing the code beyond the subdivision into sections also simplifies the code maintenance:

Order

If your module is for example a Microsoft Access form, you should rearrange the methods according to their (likeliness) of occurrence.

In the "Form" section this would result in the order: Open, Load, Resize, Activate, Current. Unused functions can be omitted of course.

'################################################################################
'# Form

Private Sub Form_Open(Cancel As Integer)
End Sub

Private Sub Form_Load()
End Sub

Private Sub Form_Activate()
End Sub


Private Sub Form_Current()
End Sub


Private Sub Form_Deactivate()
End Sub

Private Sub Form_Unload(Cancel As Integer)
End Sub

Private Sub Form_Close()
End Sub

Hierarchy

There are functions that will be called first and functions that will be called by other functions (and which are usually declared private). It is good practice to define the called functions after the calling functions.

By doing so you allow the reader of your code to first envision your intention and then to read how you solved the different tasks.

Public Function getFilePath() As String
    Dim strResult As String

    strResult = getFolderPath & getFileName

    getFilePath = strResult
End Function

Private Function getFolderPath() As String
    '...
End Function

Private Function getFileName() As String
    '...
End Function

Comments

Comments are good and comments are bad.

The good thing about comments is that they can explain a complicated code manoeuvre and by that shorten the time for understanding the code a lot.

The bad thing about comments is that they are usually not as well maintained as the code, which leads the possible evil side effect that the comment explains something that the code actually does not do.

Besides that should comments be completely superfluous, if your code is clean.

So what do we do with comments now? We try to avoid them whereever we can and whenever we come across a comment, we will try to remove it, unless it is crucial for understanding the code underneath … and we do not have the time to refactor the code to some better understandable state.

Names

Choosing the right name for a class, module, form, variable, constant, method, table or whatever object we are dealing with is our foremost duty as these names are hard to be changed later on and a good naming also paints a better picture of our idea about the system and how it is supposed to work.

General

First we need to avoid typical pitfalls in naming, mainly

• Everything is singular
• Nothing is abbreviated

Singular

Most classes, tables, forms represent a single object. Given the example of a database table, many people tend to name a table "Customers", as there are many customers in this table. But it is closer to the essence of such an object to regard the object itself as some kind of pattern for all the representations it hosts.

Besides that academic view on objects, it feels semantically better to refer to "Customer.Name" than to "Customers.Name". Why? Simply because "Customers.Name" suggests that all customers have the same name or some other weird association.

As there is no rule without exceptions, there are a few here too:

If an object represents a multitude of other objects like a collection, it is natural to name the object accordingly. This allows you with a little differentiating "s" to refer to two very closely linked objects like in

For Each objCustomer in objCustomers
    '...
Next

The same logic applies when it comes to naming methods, where one is the "inner core" of the other like in

Public Sub importLines()
    Do While Not .EOF
        importLine
    Loop
End Sub

Private Sub importLine()
    '...
End Sub

Abbreviations

Abbreviations are a bad thing. We all are lazy, but abbreviations will give you a hard time whenever you try to understand somebody else's code or even your own code, which have not touched for a long time.

You will certainly struggle to understand what was meant by getAllCstAvgHrsChrg – while it might have seemed a brillant short name for getAllCustomerAverageHoursCharged at the time the code has been written.

The only exception to the rule are acronyms which are well known like ISO. Nobody would change the name of his function from getIsoCode into getInterationalStandardizationOrganizationCode.

Variables

Besides the prefix obtained by the Naming Conventions the name of a variable has to reflect its content.

Dim strText As String
Dim intCode As Integer
Dim blnDone As Boolean

Sometimes the prefix makes even a further differentiation unnecessary

Dim strCustomer As String  '  Name of the customer
Dim lngCustomer As Long    '    Id of the customer
Dim blnCustomer As Boolean 'Status of the customer

This is a matter of taste and subject to the coder's personal preferences as well as the meaningfulness in the context of the code. As we are striving for a clear, undisputable, unique and versatile coding style, it is better to stick to the more verbose variant:

Dim strCustomerName   As String 
Dim lngCustomerId     As Long   
Dim blnCustomerStatus As Boolean

Methods

A method name

• describes what the function does,
• starts with a verb and
• starts lowercase

Public Sub reportDifference()
    '...
End Sub

If the method is a function, it usually starts with "get" or – if the result is a boolean value – with "is" or "has". Some people use numeric verbs in order to indicate that the return value of a function is numeric like "countItems", but in favour of a clear distinction between subs and functions this should be rather defined as "getItemCount".

Line breaks

Long lines tend to stress the perception of the reader. For this reason we will use line breaks for a reasonable division of code, for example:

If _
    strCustomerName <> "" And _
    lngCustomerId > 0 And _
    blnCustomerStatus _
Then
    MsgBox "Hooray"
EndIf

The same applies for longer method calls with many parameters (see #Named Parameters).

Named parameters

Some methods accept a multitude of parameters. Let us take the Find method for Excel ranges as an example. The method accepts many optional parameters: Range.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat).

If Find is used to search for "text" case sensitive it is possible to write

Range.Find "text", , , , , , true

As it is quite self-explanatory let's have a look at the better practice:

Range.Find What:="text", MatchCase:=true

and with two or more parameters it is even better readable to break the parameters into separate lines:

Range.Find _
    What:="text", _
    MatchCase:=true

Blank lines

Line breaks are another means of separating code.

We use line breaks between all "units" like methods and it is also good practice to add another empty line before each separator in order to increase the readability.

Line breaks do also appear in code itself. While it is a matter of taste where these separating empty lines should appear in order to support the recognition of structures like if-then-else and select case, there will be only a single empty line in code, not more.

Referencing objects

Microsoft Access allows to reference controls by using an exclamation mark. The theory behind this weird kind of referencing is, that they want to distinguish controls from real properties and methods of the form.

The exclamation mark is still available for backwards compatibility and delivers the default property of the referenced object.

The usual and far more flexible and easier maintainable practice is to reference objects through collections, for example

frm.Controls("txtSubject").Value
rst.Fields("Id").Value

It is also good practice to explicitly name the property you are referring to instead of trusting on the default property being the same.

The only trade-off of using the string-and-property variant might be a slight performance loss, as the object has to be retrieved by scanning the underlying collection for the right name.

Obsolete constructs

Call function

The Call function is simply not needed to call subs or functions. It suppresses the return value and is able to call DLLs – that's it.

Set obj = Nothing

There was a time when resetting object variables was absolutely necessary in order to avoid heavy resource usage.

Those days are gone. Just end your method and the resources will be freed.

While … Wend

While … Wend has been replaced by the Do … Loop structure.