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.

BEST PRACTICE PRINCIPLES

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

Scope – Three 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