Procedure Template

Function Template

I use a template for all procedures. This speeds development and reminds me what to include and how to structure the code so all my procedures feel the same and are easy to maintain (as much as possible).

This template follows structured programming best practices and, as such, flows from top to bottom and contains no GOTO statements (Except one and only one On Error GoTo). It also has a single exit point at the bottom (no EXIT SUB/EXIT FUNCTION statements). Additionally, this template integrates with modular programming best practices and, as such, has no need of GOSUB or RETURN statements.

NOTE!
There are many VBA developers who do not adhere to these practices and, throughout the decades, there have been heated debates as to whether such practices are good or evil. I've heard the arguments against and decided to standardize my work along these lines. These best practices have served me extremely well across many development languages (not just VBA) and across several decades. The have definitely not hindered my success. I highly recommend them.

Below is the procedure template that I copy, paste, modify in for each procedure I create. Below the code are explanations for each code block it contains.

Function Template(ByVal MyParameter as String) As Variant

' Description:Procedure Description

' Inputs: MyParameter Describe its purpose

' Outputs: me Success: <return this>

' Failure: <return this>

' Requisites Routines ModuleName.Procedure Name

' Classes Class Module Name

' Forms User Form Name

' Tables Table Name (etc.)

' Notes <add if needed>

' Example: ?Template(MyParameter)

' Date Ini Modification

' 01/01/01 CWH Initial Programming

' Declarations

Const cRoutine as String = ‘Template’ 'Routine’s Name

' Error Handling Initialization

On Error GoTo ErrHandler

Template = Failure 'Assume failure

' Check Inputs and Requisites

If sParameter = cvbNullString then Err.Raise DspError, , "Parameter missing"

' Initialize Variables

' Procedure

Template = Success 'Successful finish

ErrHandler: 'Error Handling, Clean Up and Routine Termination

Select Case Err.Number

Case Is = NoError: 'No error - do nothing

Case is = #: 'Add specific error handling here

Case Is = RtnError: Template = CVErr(xlErrDiv0) 'Return Error code to spreadsheet

Case Is = LogError: Debug.Print cRoutine, Err.Description 'Log the event and go on

Case Else:

Select Case DspErrMsg(cModule & "." & cRoutine)

Case Is = vbAbort: Stop: Resume 'Debug mode

Case Is = vbRetry: Resume 'Try again

Case Is = vbIgnore: 'End routine

End Select

' Clean Up

End Function

Procedure Documentation Block

I place a documentation block at the top of every procedure. It documents this module’s purpose, inputs, outputs, requisites (same as prerequisites), example usage, and version history (date, Initials, and modification description). Whenever I change any code in the module I add a version history line. Before I save a module I change the version date to the current date.

Declarations Block

I place all variables at the top of every procedure so I never have to hunt for them. The template requires the procedures name in the constant: cRoutine

Error Initialization Block

Every routine I write contains error handling so should something unanticipated go wrong, the client won't be dumped into the VBE and, when possible, provide the client guidance on how to recover. When not possible, it provides information needed by the developer to understand what went wrong.

Check Inputs and Requisites Block

I write my routines so they can be called interactively or from my testing application. For this reason, and others, in most of my functions I do not take for granted that parameters have been sent correctly or that needed tables or worksheets exist. I do make exceptions when routines are expected to be called repeatedly and rapidly. In such cases, I add a note that this routine expects calling routines to properly handle parameters and requisites.

Initialize Variables Block

Procedures often need variables set to a specific value before use. I do that in this block.

NOTE! Sometimes we need to initialize variables before Check Inputs and Requisites. Feel free to swap their places when needed.

Procedure Block

This is where the magic happens. I put the code required to achieve my purpose here.

Error Handling Block

Should something go wrong, this is where it will be handled. In most cases, DspErrMsg(), my central error processing routine, will be called.

Clean Up Block

Before the procedure ends, we may need to set objects to nothing (for memory reasons) or restore settings like screen updating or automated calculations. Do that here, and the clean up will be done no matter what happens elsewhere in the procedure.

Module Level Code

My standard requires every module top to contain some documentation and this template requires every module's top contain the private constant cModule so error handling can report where an error has occured. The template also requires a general purpose module (modGeneral in my library) contain the global constants show below.

Below is the module top template that I copy, paste, modify in each module I create. Below the code are explanations for each code block.

' Version: <Last Update Date goes here>

' Save As: modGeneral

' Description:General purpose library included in all projects

' Date Ini Modification

' 01/01/11 CWH Initial Development

' < date ><ini>Description of modification

'Options

Option Explicit

Option Private Module

Option Compare Text

'Private Constants

Private Const cModule As String = "modGeneral"

'Global Constants

Global Const Success = True 'Use Success to convey a function completed normally

Global Const Failure = False 'Use Failure to convey a function ended in error

Global Const NoError = 0 'No error exists at this time

Global Const DspError = 9999 'Instructs error handler to display the error in Msgbox()

Global Const RtnError = 9998 'Instructs error handler to return an error code. Use in UDFs.

Global Const LogError = 9997 'Instructs error handler to log error

'Global Variables

Public bLogOnly As Boolean 'See DspErrMsg

Public bDebug As Boolean 'See DspErrMsg

Module Documentation Block
I place a documentation block at the top of every module. It documents this module’s Version, Save As name, Description, and version history (Date, Initials, and Description). Whenever I change any code in the module I add a version history line. Before I save a module I change the version date to the current date.

Module Options Block
I follow the documentation block with module options. These are the options I prefer.

Option Explicit Forces explicit declaration of all variables in a file, or allows implicit declarations of variables.

Option Private Module Prevents a module's contents from being referenced outside its project.

Option Compare Text Sets the default comparison method to case insensitive text

Module Private Constants Block
To support error handling I provide a constant with the module’s name that only code in this module can see (Private). This constant is used by DspErrMsg() to display this name in the title so we know where the error occurred.

Module Global Constants Block
My general purpose module (modGeneral) declares constants to help better document code. Most are required by error handling. The ones shown should only be in the general purpose module.

Module Global Variables Block
My general purpose module (modGeneral) declares variables used by DspErrMsg() The ones shown should only be in the general purpose module.

Additional Resources
DspErrMsg()
Error Handling