VBA Coding Standards

A Professional VBA developer has a tendency of writing code by following globally accepted standards. We can say that applying the name of procedure, titling every procedure and also short description of every module. 

It is recommended especially for points below listed:

Name of Module: Your every module should have a specific name, which will help you to know the function of that module. Normally when you are working on big projects, that helps you to identify the module.

Normally naming the module you should prefix as md/mod/m and for Class Module cls is the globally used prefix 

An example, I have highlighted in yellow in below example.

It becomes necessary that you should have a default line "Option Explicit" on every start of module. And start writing procedure. This helps you to minimize the error while executing the code. If you cannot see the default line in module,  you can select from Tool -> Options available on VB Editor ribbon as shown in below figure an Option window will appear. Just check "Require Variable Declaration" and OK button.

By selecting, you have to declare each variable explicitly. This helps optimizing execution of your procedure. Also you do not encounter any errors related to variable data types.

Once you are done, you can start writing procedure in module.

Keep in mind to specify title to every procedure may it be in a module or different modules

Procedure Name: The name of procedure should be meaning full and unique. 

                            Name should not be too length, this will be difficult when you 

                            are referencing or calling the procedure in some other module.
Description       :  Describe in short the purpose of procedure. for example

                            Performs auto emailing to receiver from list of email ID in sheet
Calls                  :  Mention the name of any other procedure or function that is 

                            used in this procedure
Arguments       :  Write the names of specific arguments or variable, which has 

                            impact in this procedure
Comment         :  Write clear comments if you have any

Date         |    Developer                |      Comments
 

Date of Creating Procedure or date of any changes done later

Name of Developer or modifier

Comments of any changes done, this should be in short.

Variable declaration

Based on Data types your variable should have prefix as listed below for instance

Normally scope of variables can be further divided in three types

• Variables declared locally
• Variables declared at Module/Procedure level
• Variables declared globally

Local Variables: These types of variables are declared locally and used for specific macro or snippet. These variables are not specifically declared and stores value used within the procedure or macro.

Variables declared at Module/Procedure level: Procedure level variables are not difference from local variable types, however these are declared explicitly in every procedure and also short info of that data variable is mentioned

for example:

s/str	String
b/bln	Boolean
n/int	Integer
v/vnt	Variant
rng	Range
wks	Worksheet
wbk	Workbook
cht	Chart
shp	Shape
dbl	Double
flt	float
byte	byte
obj	object

Finally Comes
Variables declared globally: These types of variables are globally declared to use in entire project, for example

Public gFirstName as string
Public gLastName as String
Public gBirthDate as Date 

Or even User Defined Data Types for example

Type E_Employee

     gID         As Long
     gName    As String

     gSalary   As Currency

End Type


Further you can use User Defined Variables in your procedure as shown below


Dim EmpDetail as E_Employee


EmpDetail.gID = 12345
EmpDetail.gName = "Aurther John"
EmpDetail.gSalary = $2333.54


These are now available to use globally and access globally. Global Variable are always declared before start of procedure or module.

Every Individual developer follows prefix as it is convenient, however you should follow the same standard pattern of naming the variables throughout your project.

Even if you are creating an Userform with multiple Activex Controls, always use prefix to every controls, few example listed below

cbo     Combo box
chk     Checkbox
cmd    Command button
dir      Directory list box
dlg     Common dialog control
drv     Drive list box
fil       File list box
fra     Frame
frm    Form
img    Image
lbl      Label
lst      List box
opt    Option button
out    Outline control
pic     Picture
sbr    Scroll bar
shp   Shape
spn   Spin
txt    Textbox

When you are dealing with objects

db/dbs     Database
rs/rst       Recordset
ds            Dynaset
tbl           Table
qry          Query
tdf           TableDef
qdf          QueryDef
rpt          Report
fld           Field
xl            Excel object
wrd         Word object




Hope you understand the concept of following the Coding Standards.