VBA Good Practise

VBA Good Practise

VBA Environment

Compared to the worksheet environment, it’s probably easier to construct and test code in the VBA environment as much has been written on traditional code structures.

Settings

The use of “Option Explicit” is recommended for all VBA code. This forces all variables to be declared. This ensures that no renegade variables can creep into your code without your knowledge.

To ensure that “Option Explicit” appears in each module, from the Visual Basic Editor (VBE), Menu > Tools> Options and ensure that “Require Variable Declaration” is checked.

Object Naming

This will be limited to Projects, Modules, Class Modules and Forms.

All names should use REF _Ref296592329 \h Pascal Casing (see below).

All modules should be named according to the process or processes they contain. All modules should be numbered, especially where they are processed in a particular order, for example:

Note that here each module name begins with an alpha acronym. Modules cannot begin with a number and they have been preceded here by an alpha identifier that describes the workbook name.

Class modules should be named according to the object they represent, using Pascal Casing. Class modules should begin with the letters “cls”. For example, a policy holder object could be called “clsPolicyHolder”.

Forms should also be named according to their function using Pascal Casing. Forms should begin with the letters “frm”. For example, a form for taking user inputs could be called “frmInputs”.

Pascal Casing

In computing, words that define names of, say, procedures and ranges, are delimited by spaces. That is to say, a space signals the start or end of a word. In order to use a meaningful series of words to describe a procedure they have to be strung together of have the spaces replaced by underscores. Pascal Casing is a form of constructing names that do not use spaces where multiple words are used. For example:

FileName is a legitimate name, whereas File Name would be considered as two names.
Where a space might be visually preferable, File_Name is also legitimate (note the use of an underscore character to join the parts in place of a space).

Pascal Casing is sometimes known as “Camel Casing”.

Standard Naming Convention for VBA

Variable names should comprise of three elements, i.e. their scope, their data type, and their purpose.

The scope is defined as whether a variable is readable within only one procedure (often referred to as “local variables”), across multiple procedures in a module, or multiple modules of a VBA project (often referred to as “global variables”). The first element of a variable name represents the scope and is determined by a single character, as shown in the table below.

Scope		Scope Identifier
Local variable
	Procedure Argument	a
	Procedure Variable	l

Program Level
	Static Variable	s
	Module Variable	m
	Global Variable	g

The data type is defined by the initial declaration of the variable. The variable name should contain a three character short-hand identifying the data type for easy recognition throughout the project. These data type short-hands are summarised below. They should appear directly after the scope identifier described above..

Data Type	Identifier	Data Type	Identifier
Boolean	Boo	Long	Lng
Byte	Byt	Object	Obj
Currency	Cur	Single	Sng
Date	Dtm	String	Str
Double	Dbl	String (fixed length)	Stf
Integer	Int	Variant	Var

This table is not exhaustive and will be updated to reflect other definitions.

The variable name follows the scope and data type identifiers, separated by an underscore “_”. Examples of local variables are given below.

Dim lStr_PolicyIdentifier As String
Dim lSng_InterestRate As Single

Example of Variable Naming

Declarations

All global variables, that is, those declared outside a procedure, must be contained in a separate “globals” module.

All variables should be declared on separate lines:

Dim lLng_Index As Long
Dim lStr_Msg As String

All variables passed to a procedure should appear on separate lines:

Function Byg_SortArr(ByVal pVar_Arr as Variant, _
Optional pBoo_AtoB As Boolean = True)

Procedures

There are two main types of procedures that can be programmed:

· Sub

· Function

For these we can apply the V model approach of unit, integration and operational testing.

The primary goal of unit testing is to take the smallest piece of testable software in the application, isolate it from the remainder of the code, and determine whether it behaves exactly as you expect. Each unit is tested separately before integrating them into modules to test the interfaces between modules. Unit testing has proven its value in that a large percentage of defects are identified during its use.

Source: http://msdn.microsoft.com/en-us/library/aa292197(VS.71).aspx

Accessed: 6 July 2010

VBA Testing: Example Layout for Unit and Stub

Here is an example of how this might appear in a VBA module.

Option Explicit

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

'' Purpose : Output TEST results to the immediate window
'' Written : 21-May-2002 by Andy Wiggins, BygSoftware.com
''

Sub TEST_Byg_LastCell()

    Debug.Print " - - - - - - - -"
    Debug.Print " "
    Debug.Print " ""R"", last row (including no argument given)"
    Debug.Print Byg_LastCell("R")
    Debug.Print Byg_LastCell("r")
    Debug.Print Byg_LastCell
    Debug.Print " "

    Debug.Print " Anything else returns the last column"
    Debug.Print Byg_LastCell("C")
    Debug.Print Byg_LastCell("c")
    Debug.Print Byg_LastCell("X")
    Debug.Print " "
    Debug.Print " - - - - - - - -"

End Sub

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'' Purpose : Returns the last cell, in use, on the active sheet
'' Written : 21-May-2002 by Andy Wiggins, BygSoftware.com
'' Notes : This could be split into two functions; for rows and columns
''

Function Byg_LastCell(Optional aStr_Rc As String = "R") As Long

    With ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
        Select Case aStr_Rc
            Case "R", "r"
                Byg_LastCell = .Row
            Case Else
                Byg_LastCell = .Column
        End Select
    End With

End Function

Comments

If Visual Basic for Applications (VBA) is used, then the code should be commented. If it is possible, the modules should also be given some numeric identity to indicate the order in which they are used. VBA modules, however, cannot begin with a number so a prefix, so a reference to the workbook name is required.

All VBA code should be commented. Despite the naming and style of the language being fairly straight forward, a good commentary adds value. As a rule of thumb, aim for one character of commentary for each character of code.

There are two main types of comment:

Headers
Inline

Header commentary introduces the procedure, whereas inline comments aid the understanding of the process at that point.

Examples of commented code:

Source: http://www.exceluser.com/explore/questions/vba_textcols.htm Accessed: 22 June 2011

Option Explicit

''======================================================
'' Program:    ParseText
'' Desc:       Reads a text file into a variable then
''             writes it into a row, n chars at a time
'' Called by: user
'' Call:
'' Arguments:
'' Comments:   No error-checking.
'' Changes----------------------------------------------
'' Date        Programmer     Change
'' 6/14/06     Charley Kyd    Written
''======================================================

Sub ParseText()
Dim sText As String, sFile As String
''Get the full path to the source file

   With ThisWorkbook
      sFile = .Names("SourcePath").RefersToRange
      If Left(sFile, 1) <> "\" Then sFile = sFile & "\"
      sFile = sFile & .Names("SourceFile").RefersToRange
   End With

''Get the full text string from the text file
sText = GetText(sFile)

   ''Remove all nonprintable characters from the text
   ''Comment out if the characters are wanted
   sText = Excel.WorksheetFunction.Clean(sText)

   ''Write to the workbook
   WriteToSheet sText

End Sub

Source: http://www.dailydoseofexcel.com/archives/2007/12/13/vba-code-documenting-tools-project-analyser-and-visustin/ Accessed: 22 June 2011

Option Explicit

Sub GetFilesInDirectory(ByVal sDirToSearch As String, colFoundFiles As Collection)
'-------------------------------------------------------------------------
' Procedure : GetFilesInDirectory Created by Jan Karel Pieterse
' Company   : JKP Application Development Services (c) 2006
' Author    : Jan Karel Pieterse
' Created   : 04-10-2007
' Purpose   : Retrieves all files in sDirToSearch, stacks matches into cLookForFIles
'-------------------------------------------------------------------------
    Dim NextFile As String
    Dim lCount As Long
    Dim sFileName As String
    Dim sFileSpec As String
    Dim lFoundMatches As Long
    Dim oCtlNew As CommandBarButton
    Application.EnableCancelKey = xlErrorHandler

    If Right(sDirToSearch, 1) <> "\" Then
        sDirToSearch = sDirToSearch & "\"
    End If

NextFile = Dir(sDirToSearch & "*.xls")

    Do Until NextFile = ""
        If Err.Number = 0 Then
            If TypeName(oObj2Add2) Like "Command*" Then
                Set oCtlNew = oObj2Add2.Controls.Add(msoControlButton, , , , True)
                oCtlNew.Caption = NextFile
                oCtlNew.OnAction = "OpenFileFromMenu"
                oCtlNew.Tag = sDirToSearch & NextFile
            Else
                AddFile2Wizard oObj2Add2, NextFile, sDirToSearch
            End If
        End If

NextFile = Dir()

Loop

On Error GoTo 0

TidyUp:

Exit Sub

End Sub

XML Commenting

There is an up and coming method of documenting based on XML. This is being considered and might supersede these recommendations. Anyone aware of this method and currently using it, should continue to do so.

Add-Ins

The use of add-ins will differ from developer to developer. Also, their use will be dependent on the task or process. However, one important factor is to ensure that from a user’s perspective the add-in will always be available if required. This means ensuring that the add-in is either, located centrally, where the “central” location is available to all potential users, or, located with the Workbook or in the workspace.

Error Handling

Error handling could be included where the result can be trapped and properly handled without compromising the results. Occassionally it is better if no error handling is included so that an error stops a process and can be traced and repaired in real time.

Whether to use error handling must depend on context and any such decisions must be justifiable.

Use of Abbreviations

The use of standard abbreviations is sometimes preferable to a full name, but only where they make sense, for example:

“FileName” and not “FLNM”
But “DateOfBirth” or “DoB”

General guidelines for VBA modules:

Each module should contain a distinct part of the process.
All Subs and Functions should contain a header commentary.
All code should contain in-line comments, with the aim of making the code self-documenting.
Indent code to emphasise the process flow.
Leave white space.
Do not leave “commented” code without good reason.
As a rule of thumb, there should be one character of comment for every character of code.