Excel & VBA Best Practices

Here is our list of Excel and VBA best practices. This is only a suggestion, not a definite set of rules. We believe using these tips can make Excel and VBA much easier.

Use the "Option Explicit" clause on the top of every module. This has been explained in another post and makes the programmer debugging much easier

Don't use the type of data as the prefix of a variable name (hungarian notation). This also has been said in this blog. Once you use the Option Explicit clause and have declared your variables, there is no need to use hungarian notation.

Handle Errors. Using the "On Error GoTo error_label" clause is not only useful, but fundamental. It makes sure your code will not have unexpected behavior. The "On Erro Resume Next" should be avoided though.

Raise Errors. Once the exception happened and the error handling code is executed, if the function is not the main sub, the error must be raised, otherwise the parent function will not know about the error and this may affect the rest of the execution.

Log. Having a log module is crucial. We provided the LOG_MODULE that is perfect for the task and logging should be made with no restriction (performance issues are easily addressed by turning the log off).

Be explicit in every possible way when programming. Another subject extensively explained in this blog, being explicit is very important for the sake of the quality of the code. Using full addresses of ranges, declaring if the routines are public or private and using the "ByVal" clauses are just some examples of explicit information that should be in your code.

Program with simple organization and indentation.

Avoid data manipulation in cells - use arrays whenever is possible.This also has been explained in this blog. Data manipulation in cells is slow and should be avoided.

Use modules. Modules are great to organize your code and to make it portable. Actually this is the core of this blog.

Use the "Private" and "Public" clauses. If the function or sub will be used only in its module, it should be private, otherwise it will be listed outside its module for no reason.

Be aware of the performance. VBA programs are single-threaded. This means you can not make anything in parallel. So keep in mind that small improvements in program performance add up and make your programs much faster.

Log the error description. Everytime an exception occurs and is handled, the description of the exception should be printed to the log file so this information is not lost. This is particularly useful for connections with backends. Err.Description is the attribute to be used.

Use full addresses of objects. It is important to uset, for example, "Thisworkbook.Worksheets("Sheet1").Range("A1")" instead of simply "Range("A1")". Implicit information is one of the most problematic things in VBA.

Use "Long" data type instead of "Integer"

Use "vbNullString" instead of "". Not only because it is more elegant, but more efficient in some cases.

Give every workbook object that you use a name. This means that every worksheet that you use, every table and every cell that needs to be refereced in VBA should receive an unique and descriptive name. This way it will be much easier to reference those objects in VBA instead of using, for example, the "A1" or "E3" address references - which can can change when we add or remove rows and columns.

Use hungarian notation with workbook objects names (objects that are not VBA variables). In the other direction of VBA variables, the objects spreaded around the workbook should be referenced with names that contain a tip about its nature. For example, a worksheet could be named "wks_main" or "wks_something" where "wks" is the prefix that categorize it as a worksheet. A table could be "tbl_something", a cell "cell_something" and so on. This, believe us, can make programming much easier .

Use tables. For sure one of the most useful Excel object, the tables as also known as dynamic ranges. They should be used everytime you have a list of related objects - a table! Once you have transformed the range into table (and, of course, named it), any update on it is reflected immediately on related objects such as pivot tables and graphics. Not only that, adding, for example, a new row changes the table size automatically, so you will always get the correct size of the table in VBA using the ".rows.count" range method. This is one of the best tips we have to give.