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 |