VBA Code Guide
General
Following guidelines during coding makes code easier to read and to understand for you and others.
The following code guide is a summary of best practices in VBA projects.
Writing
- Line width
- Keep your line lengths under 80 characters.
- Printing code, that makes extensive use of unlimited line lengths looks very irritating when printed. Make use of line breaks
- Empty lines
- Use blank lines carefully.
- Single blank lines can improve the readability of the structure of your code.
- Two blank lines can help to improve the separation into distinct sections (see section comment).
- More than two blank lines are not allowed.
Indentation
- Indentation
- Use four spaces instead of tabs
- Using tabs in other editors than the VBE results in whitespace dependent on the application you are using the code in, which resulsts in inconsistent code layout.
- Four spaces indent enough to emphasize the indentation, two spaces are often to few, eight spaces too much.
- Keyword alignment
- In If and Case statements the "choice" keywords should be indented to the same level.
- Sometimes you see in Case statements that the Case lines are indented, but this is not consistent compared If/Else.
If blnResult Then
'Result code here
Else
'Else code here
End If
Select Case strResult
Case "My"
'My code here
Case "Their"
'Their code here
Case Else
'Default code here
End Select
Comments
- Section comment
- Use hashes / sharps (#) for section comments.
- Hashes / sharps are very "dark" and therefore ideal for making a significant separating comment.
- The comment is indented to the same level as the commented code
- Make the comment 80 characters wide in order to remind you of the desirable maximal length of your line; if indented a few levels the comment should be shorter, but you can ignore this for the time being and for practical reasons.
'################################################################################
'# Section
- Sub section comment
- Use equals (=) or dashes / hyphens (-) for sub section comments.
- For sub sections you need a less "dark" character. Some people prefer equal signs, other dashes / hyphens.
- The advantage of dashes / hyphens is, that you can use plus signs (+) and pipes (|) to imitate a frame. "Closing" box comments on the right side is not necessary as it leads to an unnecessary editing overhead.
'+-------------------------------------------------------------------------------
'| Boxed comment
'+-------------------------------------------------------------------------------
Declarations
Scope
- Scope
- The scope of all variables and methods is private by default.
- The keyword "Private" should be specified explicitly.
- Enumerations tend to be public as you usually do not specify enumerations for some local / private use only.
Variables
- Naming
- Variables have to be named according to the Naming Conventions.
- Declaration Indentation
- Variable declarations are indented.
- Full Declaration
- Variables should be declared fully qualified.
- If a variable type comes from a library, the variable declaration should be fully qualified, ie the variable type is prefixed with the library name.
Private Sub saveRecord()
Dim cnnDatabase As ADO.Connection
Dim wbkReport As Excel.Workbook
'Code here
End Sub
- When to declare
- Declare a variable, if its value is used more than once.
- Do not declare a variable if its value is only used once.
Procedures
- Naming
- Procedures start with a verb followed by a noun.
- The verb describes what the procedure is doing.
- The noun describes the object the procedure is doing it to.
- Functions describe their result. If the result is of the data type Boolean, the verb has to be "is" or "has"; choose a function name that can be answered with "yes" or "no".
Private Sub createReport()
End Sub
Private Function getLastId() As Long
End Function
Private Function isReady() As Boolean
End Function
Abbreviations
- Use of abbreviations
- Do not use abbreviations.
- Abbreviatons make it very hard to read code, because you have to interpret what the author meant when he wrote the code.
- Keep in mind that allowed abbreviations are still to be written camel cased when used in names.
Exceptions
- Currency ISO codes
- Examples: EUR, USD, GBP
- Currency ISO codes
- Abbreviations which would be too long to write them out in full
- Examples: IETF, UTF, W3C, EU, NASA
- Abbreviations which would be too long to write them out in full
Hard Coding
- Hard coded values
- Do not hard code values.
- User names, file and folder names and constant values should be kept in lookup facilities like databases and files or defined in constants.
- "Magic numbers"
- Define constants for all "magic numbers".
- "Magic numbers" are values that make sense in a certain context, but are not universally understandable without the context.
- Example: 32 is well known as the ASCII code for "Space"; without a function that deals with character analysis, but out of this context it could also be your shoe size or the number of your appartement.
Code Skeletons
Functions and Get Properties
- Function declaration
- The declaration of a function or Get Property has to define the return data type.
- Specifying the return data type makes it easier to debug the code.
- Result variable
- The declaration of the result variable is the first line of the function or Get Property code.
- The assignment of the result variable to the function or Get Property is the last line of the code.
Private Function getLastId() As Long
Dim lngResult As Long
'Code here
getLastId = lngResult
End Function
Select Case
The Select Case Statement should always contain a Case Else part in order to assure that every possible value has been considered and to allow error handling. Note that the Case keyword is indented to the same level as the Select Case. Blank lines help optically to better keep track of the cases and the code that is run.
Select Case strAnimal
Case "Mouse"
'Code here
Case "Elephant"
'Code here
Case Else
'Code here
End Select
Code
Line Breaks
- Code lines must not exceed 80 characters per line.
- This is for reasons of readability, navigation and print outs.
If you have expressions that are too complex or lines that exceed the 80 character limit, you should use line breaks in order to maintain readability.
If objCustomer.Country = strLocalCountry Or ( objCustomer.Language = strLocalLanguage And objCustomer.OptOut = False ) Then
'Code here
End If
would be better readable
If _
objCustomer.Country = strLocalCountry Or _
( objCustomer.Language = strLocalLanguage And objCustomer.OptOut = False ) _
Then
'Code here
End If
You always have to try to keep the balance between readability and too many line breaks though. Especially when it comes to nested function calls and functions with multiple parameters, putting a line break after each function call and after each parameter might become counter productive.
For the same reason SQL statements are split up into multiple lines.
With statement
- Use With statements when you have more than one reference in the code.
- With statements are a nice way of pointing out the scope you are dealing with.
The code used in the section Line Breaks could also be written as
With objCustomer
If .Country = strLocalCountry Or ( .Language = strLocalLanguage And .OptOut = False ) Then
'Code here
End If
End With
The With statement might even lead to omitting the line breaks, anyway the logic is even more readable when you write
With objCustomer
If _
.Country = strLocalCountry Or _
( .Language = strLocalLanguage And .OptOut = False ) _
Then
'Code here
End If
End With
Yes, those are more lines of code, but the most important thing is to improve the readability and to point out the logic at the same time.
Another nice side effect of using the With statement is, that you will easier see which objects you are dealing with in what part of the code and this might sometimes even implicitly show you that, for example, the initialization of a certain variable could be done earlier in the code than you thought.
For Each
- Use For Each instead of For To whereever you can.
- For Each does not have to be set up and works nicely with empty ranges.
When you use a For To statement, you usually write something like
Dim lngCounter As Long
Dim objCustomer As clsCustomer
For lngCounter = 0 To colCustomers.Count - 1
Set objCustomer = colCustomers.Item(lngCounter)
'Code here
Next lngCounter
So you will have to
- define a variable for the iteration
- find out whether the index starts with 0 or 1
- find out the object's property which will give you the amount of iterations
- adjust the property if necessary
- carry out some tests how your code will handle an empty object and
- assign the correct object to your variable.
Using For Each would result in
Dim objCustomer As clsCustomer
For Each objCustomer In colCustomers
'Code here
Next objCounter
It is obvious that you need less lines of code and that the structure of the construct is much more straightforward.
Controls
Default Property
- Specify the default property explicitly.
- Most controls have default properties, but specifying the default property explicitly makes the code more readable and understandable.
'Implicit use of the default property
txtCountry = cmbCountry
'Explicit use of the default property
txtCountry = cmbCountry.Value
To Do
- Select Case vs ElseIf: if possible switch to case
- Recordset vs SQL
- (Well formed SQL)
- Named parameters: when and when not, no multi commas
- MsgBox: Message, Buttons, Title
- (vbInformation|vbQuestion|vbCritical) + (vbOkOnly|vbYesNo|vbYesNoCancel) [+ (vbDefaultButton1|vbDefaultButton2)]
- Class Properties > Class variables
- Full featured number formats
- Double, Single, Currency: #,##0.00;[red]- #,##0.00;-
- Long, Integer (Count): #,##0;[red]- #,##0;-
- Date: yyyy-mm-dd
- Field order: Id field before other field of same table
- Rounding: when, how much, data type
- Code Organization
- Form code: interactive
- Control
- Private sub/function
- Module code: public sub/function, optional parameters
- Class code: no optional parameters
- Form code: interactive
- Object orientation:
- Dos and Don'ts
- When to make a class
- singleton vs instantiation
- SaveSettings / GetSetting (table!)
- System (winrar.exe)
- User (user preference)
- Subject (PNR, ...)
- Project (Client, Partner, RunTime)
- Differentiation by environment?
- Use suitable objects (FileSystemObject)
- Don't use old school
- While Wend
- Open ... For Input As #n
- Dir
Summary
- be precise
- be compact
- reuse
- be flexible
- don't trust default settings