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.
Error Handling
ByVal, ByRef
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.