The 10 Commandments

This article is the result of many years of experience and (in some cases) frustration. It has been written by a professional developer who has made all of the mistakes there are but is here to tell the tale on how to avoid those same pitfalls...

Thou shall:

1. Always use Option Explicit

Automatically have Microsoft Office include the keywords Option Explicit at the top of all code modules. This ensures that you must explicitly declare any and all variables in your code. This means that an accidental mis-spelling of a variable name (eg Salse versus Sales) will be picked up and your code will not be using an erroneous value. In large code modules, finding such an error can take quite some time. You can set this option to be automatically enabled and inserted by Office products in the VBA Editor by selecting:

Tools -> Options -> Editor and checking the Require Variable Declaration option.

2. Use the With Keyword

When dealing with objects (either built-in or custom) or UDT's (User Defined Types - see Types below) your code becomes more readable and less convoluted if you use the With keyword when addressing members. Compare the below two code snippets which achieve the same net end result:

The following code:

Selection.Font.Bold = True

Selection.Font.Italic = True

Selection.Font.Underline = xlUnderlineStyleSingle

Selection.Font.Color = -16776961

Is better written as:

With Selection.Font 
   .Bold = True 
   .Italic = True 
   .Underline = xlUnderlineStyleSingle
   .Color = -16776961 
End With

3. Use Built In Functions

Unless there is a very good reason (ie different calculation methodology required) then it is definitely best to utilise Excel's built-in functions wherever possible. After all they are very fast (compared to VBA code) and are tried, tested, trusted and accurate. You can use Excel's built-in functions in your VBA code through the use of the WorksheetFunction method of the Application object. An example is shown below:

Application.WorksheetFunction.Average(3, 4, 5) 'Returns the value 4

OR

Application.Average(3, 4, 5) 'Can drop the 'WorksheetFunction specification

Naturally the numbers in the above example can be replaced by variables.

4. Use Ctrl-Space Combination

When entering code, use the Ctrl-SPACE key combination to bring up the editor's intellisense auto-complete window from which you can select (either by pressing the ENTER key or the SPACE key) the full term that you require. This can save a lot of typing and re-typing in of variable names for instance.

5. Use a Coding Convention

It is a good idea to use a coding convention in relation to variable names, particularly for variable scope. Whilst the famous Hungarian notation has been around for a while, it is quite difficult to get used to. At the very least use a scope convention of some description. For example:

• Use the 'm' prefix for module level scope: mVarName
• Use the 'g' prefix for global level scope: gVarName

6. Use Types

Use UDT's (User Defined Types) wherever possible in order to make code more readable, contained and efficient. Consider the following

Using the below::

 
Private Type Individual 
   Name As String 
   Address As String 
   PhoneNum As String 
   Salary As Currency 
End Type
 
Private Individuals() As Individual
 

Is a lot more efficient and contained than four separate arrays and having to re-dimension each for example.

7. Use Meaningful variable names

Excel VBA imposes a limit of 255 characters for variable names, so there is more than enough allowance to make variable names meaningful. For example:

Sales is a poor choice when compared to

Annual_Sales_Pre_Tax

8. Use ScreenUpdating

Where your code results in a number of changes (of whatever type) to the display in the Excel application (ie the worksheets), it is advisable to turn screen updating off prior to performing such code and back on again after the code has completed. This results in faster code execution and a more professional look (the screen isn't flickering many times during updating). This is achieved by the following:

Application.ScreenUpdating = False 'Turn screen updating off

Application.ScreenUpdating = True 'Turn screen updating back on!

9. Create Stand-Alone functions / subs

Wherever possible, try to create functions and subs which are self-contained / stand-alone. In other words, they can be readily copied into other projects. This supports one of the key principles of professional development - reuse.

10. Create / Buy a Code Library

As you create more and more code, it is strongly advisable to either build or buy a code library application such that you can manage all of your code snippets in one centralised location. Some key requirements for a good code library application would be:

• Add / Edit / Delete Code Snippets
• Permit Code Description functionality
• Search Code Snippets
• Copy Code to Clipboard