VBA Coding Best Practice

DEFINITION: Best practices are the agreed general set of guidelines that is believed to be more effective at delivering MS Excel based tools which are:

Ø User friendly

Ø Easy to maintain

Ø More reliable and robust

These are just general guidelines; a professional developer will always assess the options and make the appropriate choice in their specific situation. These suggestions are specific to Excel, VBA.


1.Easy to read and follow what’s happening

2.Efficient code

3.Flexible and easy to change

4.Robust and deals with errors

5.Uses existing Excel functionality where possible.

CONTENTS: The contents have divided it into sections as given below




ScopeThree levels of scope exist for each variable in VBA: Public, Private, and Local

Scope Meaning Example
<none> Local variable, procedure-level lifetime, declared with “Dim” intOrderValue
st Local variable, object lifetime, declared with “Static” stLastInvoiceID
m Private (module) variable, object lifetime, declared with “Private” mcurRunningSum
g Public (global) variable, object lifetime, declared with “Public” glngGrandTotal

Var_Type (for variables)

Var_Type Object Type Example
bln or b Boolean blnPaid or bPaid
byt Byte bytPrice
int or i Integer intStoreID
lng Long lngSales
obj Object objArc
dbl Double dblSales
str or s String strName or sName
var or v Variant varColor or vColor
dte Date dteBirthDate
dec Decimal decLongitude
cht Chart chtSales
chk Check box chkReadOnly
Command button cmd cmdCancel
Label lbl lblHelpMessage
Option button opt optFrench

SUFFIXES – Suffixes modify the base name of an object, indicating additional information about a variable. You’ll likely create your own suffixes that are specific to your development work. Below table lists some generic/commonly used VBA suffixes.

Suffix Object Type Example
Min The absolute first element in an array or other kind of list iastrNamesMin
First The first element to be used in an array or list during the current operation iaintFontSizesFirst
Last The last element to be used in an array or list during the current operation igphsGlyphCollectionLast
Max The absolutely last element in an array or other kind of list iastrNamesMax


Avoid duplicate-001

BP - GSR Excel, VBA [Compatibility Mode]-001

BP - GSR Excel, VBA [Compatibility Mode]-002

BP - GSR Excel, VBA [Compatibility Mode]-003

BP - GSR Excel, VBA [Compatibility Mode]-004

BP - GSR Excel, VBA [Compatibility Mode]-005

BP - GSR Excel, VBA [Compatibility Mode]-006

BP - GSR Excel, VBA [Compatibility Mode]-007

BP - GSR Excel, VBA [Compatibility Mode]-008

BP - GSR Excel, VBA [Compatibility Mode]-009