VBA Code Guide

From vb24
Jump to navigation Jump to search

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
  • Abbreviations which would be too long to write them out in full
    • Examples: IETF, UTF, W3C, EU, NASA


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
  • 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
Retrieved from "http://wiki.vb24.net/index.php?title=VBA_Code_Guide&oldid=57"