Coding guidelines for Visual Basic 5/6 and VBA for Office

Personal coding guidelines

A collection of recommendations, conventions and some scaffolding, for naming and crafting solutions in classic (VB5/6) and Visual Basic for Applications.

I wrote the first version of these guidelines back in 2014.

I know every one of them is debatable and every developer has his or her own style and convictions.


MIT Licence

Feel free to use them as they are, or customize and build your own, it is a motivating exercice that may help or prevent consistency issues from the beginning.

Table of Contents


Naming conventions

Hungarian notation

The”Hungarian notation” principle basically consists in using a set of standard prefixes (all in lower case), before each variable name to indicate its scope and type. Use it.
Exceptions to this rule, or when it may not be appropriate to use the Hungarian notation to name things, are indicated along, and there’s a paragraph about those exceptions later.


Do not use Hungarian for constants (and there’s your first exception).
All constant names are in upper case and, if they consist of multiple “words”, they may use an underscore to separate them.


					'Like API constants:
Private Const SW_MAXIMIZE As Long = 3&
Private Const SW_NORMAL  As Long = 1&
Private Const SW_HIDE   As Long = 0&
Private Const SW_SHOW   As Long = 5&
'And we may do:
Private Const COLFLAG_DATETIME   As Long = 1&
'Which I prefer over:
Private Const COL_FLAG_DATETIME   As Long = 1&

All constants should declared with their type (As …)


					'DON'T DO THAT:
Private Const SW_MAXIMIZE = 3
Private Const SW_MAXIMIZE As Long = 3&

Note the ampersand (“&”) after the “3”, that matches the type of the constant (here a Long).


As long as we’re talking about where a variable is defined inside a project, scope in Visual Basic is simple. A variable can be global to the entire application, or local to a class module or standard module (or any other document provided by the development environment like a Form or a Sheet), or local to a function (or Sub).

gApplication global scope
mClass, module or other document (Form, Report, Sheet, etc…). “m” stands for “Member” variable.
(none)Local to the current function (or Sub), or see [Exceptions]

There is no underscore following this 1 letter prefix.

Visual Basic native data types prefixes

Visual Basic data typePrefix
Long“l” (minus L). Although in rare cases I don’t mind loosely using “i” when confusion wouldn’t matter so much.
String“s” (and not that horrible “str”)
Boolean“f” (stands for “flag”, while I use “b” for the Byte data type)
Currency“c” or “cur”
Object“o” (and not “obj”)
Date“dt” (I use the Date datatype in very rare occurrences, I mostly use Variants for dates)

Local variables used as loop iterators (i, j, k, …)

There’s nothing wrong in using one letter variable names for Integer or Long indices (or indexes), whether it is in loops (For, Do, While, Repeat, …) or for index access to an item. But it may help using longer indices names (like iCustomer, iLine, iCol) when it helps disambiguate heavily packed code. For iteration on other data types (object reference, interface, structures, etc…), avoid one letter iterator variable names.

#personal: Use “i” for rows, and “j” for columns, then “k” for anything else. If nesting more, think of using more explicit variable names.

UI elements classes

UI elementPrefix
Form used as a (modal or modeless) dialog“frm” + name + “Dialog”, (eg. frmLoginDialog)
Subform (Access)“sfrm”
Class module as an interface“I”
Tab control“tab”
Option button“opt”
Scrollbar“hsb” or “vsb” (horizontal, respectively vertical)

Other datatypes

Data typePrefix
Type definition (structures)“T”
Object of any other class“o” or a 2 or 3 letter prefix for the class, like “cn” for and ADODB connection, or “rs” for a Recordset, etc…
Standard module“M”
Class module“C”. This is unless you expose by any means the class outside of your project or database, in which case you should use a non prefixed class name (also see [Exceptions]).
Class module, used as an interface“I” (uppercase “i”)
Defined Type“t” or “tag”

Inside a type definition, use Hungarian notation for member variables, example:

					'Inside the CQueue class module (
Private Type TQHeader
  iQCount       As Integer
  iQBack        As Integer
  iQFront       As Integer
  iMaxCapacity  As Integer
End Type
'The queue header holds runtime information about the queue
Private mTHeader    As TQHeader

Function parameters

Every function / sub parameter variable is prefixed with a “p”. That’s another instance of an invaluable indicator in the body of the function’s code that allows to quickly identify if a variable is a parameter that has been passed to the function, among the ones that have been declared in its body. Without this prefix, it may be sometimes very difficult to distinguish between the origin of variables while reading the function’s code.

#personal: You’ll also quite commonly see the prefix “Ret” inserted after the variable scope and type prefix, when a function parameter variable (ByRef) is going to be modified in a meaningful way for the caller, by the function.
I also insert “New” after “Ret” if the (ByRef) parameter variable is an object that will be created inside the function.

					'This function expects a reference to an existing CList instance in poRetList,
'and it will modify the passed object, but will not create it.
'Note that I crafted the "lst" prefix for a CList object reference variable in plstParams.
'I also crafted the "cn" prefix for an ADODB connection object reference.
'But I kept the generic "o" preifx in poRetList; it's OK to mix the two as they come.
Public Function ADOGetSnapshotList( _
    ByRef pcnDatabase As ADODB.Connection, _
    ByRef psSQL As String, _
    ByRef poRetList As CList, _
    Optional ByRef plstParams As CList
  ) As Boolean
End Function
'Restyled and modified to populate a CList.
'Returns True if successful or False and sets the module error context
'Returned list (poRetNewList) will have 2 columns:
' PaperNumber : Integer
' PaperName   : String
'Sorted on PaperNumber
'Note that here I use the more generic "o" prefix in poRetNewList again.
Private Function GetPaperList( _
    ByVal psPrinterName As String, _
    ByRef poRetNewList As CList _
  ) As Boolean
  Dim lPaperCount     As Long
  Dim lCounter        As Long
  Dim hPrinter        As Long
End Function


Every array variable is prefixed with an “a”, after the scope letter prefix, examples:

					'A global array of 250 strings
Public gasLabels(1 To 250) As String
'A local array of strings
Private Sub Foo()
  Dim asUnit(1 To 5) as String
End Sub
'In a standard module
Private masText(1 To 100) As String


Enumeration definitions are prefixed with a minus “e”, as are the enumeration value names. Try to choose value names that can tip enough about what type of enumeration the values refer to, to mitigate confusion. In the sample below, the “eLeft”, “eCenter” and “eRight” symbolic names, may still be slightly ambiguous; this is acceptable if there is no near possibility of ambiguity.


					'We're inside the CCGrid class module (a [C]ustom, [C]haracter (or [C]onsole) based [Grid])
Public Enum eGridViewColAlign
End Enum

Public Enum eGridViewCellWrap
End Enum

Public Enum eTableChar
End Enum
Private masChar(eCharE_W To echarN_S_E_W) As String

'Sample usage (focus on enum usage, don't worry about the CCGrid class):
Set oGrid = New CCGrid
fOK = ADOGetSnapshotList(cnData, SQL, lstData)  'Get some db data
oGrid.FormatCols "Title::10;ContactID:#:3;Zip:6:right;" & _
Set oGrid.DataList = lstData
'use of enums; full qualification "eGridViewCellWrap." is not required.
oGrid.ColWrap(lstData.ColPos("Address")) = eGridViewCellWrap.eWrapText
oGrid.ColWrap(lstData.ColPos("Mnemonic")) = eFoldText
oGrid.ColWrap(lstData.ColPos("Floor")) = eFoldText


Functions names

Let’s assume that when we refer to functions, we’re also talking about procedures (sub).

  • Use Pascal case convention, where each word in the function name is capitalized.
    No Camel case (first letter of the function in lower case, the other uppercase).
  • Never separate words with an underscore. This is because underscores are automatically added by Visual Basic in event procedures (and interface implementations), so they would only add unnecessary confusion.

Name of functions in standard modules

API and module mnemonic

When crafting a standard module to handle a specific set of tasks as an API, prefix all the public functions in the module with more or less one to five uppercase characters that qualify the module where they belong: the module mnemonic.

Don’t prefix the private functions.


Module name is: MADOAPI (which I prefer over MAdoApi as ADO and API are rarely seen in lower case).
The chosen module mnemonic is “ADO”, it will prefix each module member function.

Sample of some of the functions signatures, prefixed by “ADO”:

					Public Function ADOGetAccessConnString(ByVal psDatabasePathname As String) As String
End Function
Public Function ADOOpenConnection( _
    ByRef psConnString As String, _
    ByRef psDatabase As String _
  ) As ADODB.Connection
End Function
Public Function ADOExecSQL( _
    pcnExecute As ADODB.Connection, _
    ByRef psSQL As String, _
    Optional ByRef plRetAffectedCt As Long_
  ) As Boolean
End Function


Collection of related functions

If the module is just a collection of helper functions that are logically grouped together in the module, don’t prefix the module’s member function with a module mnemonic.


Module name is: MStrings (Not an API, a collection of functions helping with string manipulations). No Prefix:

					Public Function ssprintf(ByRef psModel As String, ParamArray pavParams()) As String
End Function
Function UUCase(ByVal psSource As String) As String
End Function


(You can find articles explaining both these functions on my personal website)


Do not use Hungarian notation for variable names, in these two cases:


1) Variables used as Public properties in a class module:
This happens when a variable, declared as Public, in a class module, is not wrapped with get/let/set property accessors, but is directly exposed (non managed R/W/Set).
Sample, in which we see a part of the declaration section of a class module:

					'Extract from the header section (declarations) of CConsoul(.cls)
Public FontName             As String
Public FontSize             As Integer
Public WindowTitle          As String
Public BackColor            As Long
Public ForeColor            As Long
Public MaxCapacity          As Integer
Public WithFrame            As Boolean
Public RefreshOnAutoRedraw  As Boolean

2) Global objects:

					'In the declaration section of a *standard* module.
Public MyApp as New CMyApp  'And not "goMyApp" or something like "gappMyApp"
'Then, as we may use an ini file, it could be a public property in CMyApp:
Dim asSectionName() As String
Dim lSectionCt As Long '"Ct" stands for "Count", that's another personal habit
lSectionCt = MyApp.IniFile.GetSectionNames(asSectionName())

Error management


  • Return only basic native types, or objects references from functions (and property procedures).
  • Do not return arrays, pass them by reference.
  • Return True to indicate success, False to indicate failure. In case of failure, use the following guidelines to provide more contextual information.
  • Avoid using error handlers to implement behavior as much as possible. Always jump from an error handler to an exit label in the same function, with VB resume label statement, don’t let the error handler be an exit point of your function, or jump conditionally at different points in the function.
  • Do not raise errors, do not use Visual Basic Err.Raise method, instead trap errors in your error handler, return False from the function that trapped the error and set the module or class “error context” (see SetErr() function below).

In standard modules

This applies if the module represents an API (not for collection type modules). According to these guidelines, you should already have your public functions prefixed with the module mnemonic. Add the following private member variables, in the declarations section of the module:
					'Error context
Private mlErr       As Long
Private msErr       As String
Private msErrCtx    As String

Add the following functions, prefixing them with your module mnemonic.
As an example, they’re here prefixed with “ADO”:

					Private Sub ClearErr()
  mlErr = 0
  msErr = ""
  msErrCtx = ""
End Sub

Private Sub SetErr(ByVal psErrCtx As String, ByVal plErr As Long, ByVal psErr As String)
  mlErr = plErr
  msErr = psErr
  msErrCtx = psErrCtx
End Sub

Public Function ADOLastErr() As Long
  ADOLastErr = mlErr
End Function

Public Function ADOLastErrDesc() As String
  ADOLastErrDesc = msErr
End Function

Public Function ADOLastErrCtx() As String
  ADOLastErrCtx = msErrCtx
End Function

In class modules

Add the following code:
					' Class error context
Private mlErr     As Long
Private msErr     As String
Private msErrCtx  As String

Private Sub ClearErr()
  mlErr = 0
  msErr = ""
  msErrCtx = ""
End Sub

Private Sub SetErr(ByVal psErrCtx As String, ByVal plErr As Long, ByVal psErr As String)
  mlErr = plErr
  msErr = psErr
  msErrCtx = psErrCtx
End Sub

Public Property Get LastErr() As Long
  LastErr = mlErr
End Property

Public Property Get LastErrDesc() As String
  LastErrDesc = msErr
End Property

Public Property Get LastErrCtx() As String
  LastErrCtx = msErrCtx
End Property

Trapping and handling errors

Once you’ve added the previous code, you can handle trappable errors in your functions, like in this example:
					Public Function DoSomething() As Boolean
  Const LOCAL_ERR_CTX As String = "DoSomething"
  On error goto DoSomething_Err

  Dim oAnyRef As Object
  Set oAnyRef = CreateAnObject()

  'Actually do something...

  DoSomething = True

  Set oAnyRef = Nothing
  Exit Function

  Seterr LOCAL_ERR_CTX, Err.Number, Err.Description
  Resume DoSomething_Exit
End Function

(end of VB/A coding guidelines)