Good VBA code should be clean, simple and easy to understand first of all. The simpler and cleaner it is, the less the chance of bugs slipping in. As Saint-Exupery coined, "Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away."
Moreover, elegant code is usually the result of careful analysis of the problem and finding an algorithm or design, which simplifies the code greatly (and often speeds it up too).
The VBA procedure presented below, as a case study, was posted in a LinkedIn group with a solicitation for review. We examine the code for compliance with VBA development best practices.
Moreover, elegant code is usually the result of careful analysis of the problem and finding an algorithm or design, which simplifies the code greatly (and often speeds it up too).
The VBA procedure presented below, as a case study, was posted in a LinkedIn group with a solicitation for review. We examine the code for compliance with VBA development best practices.
The tips listed below are not right or wrong, just a lot of personal experience that we each evolve into a best practise.
1) Module Directives
1) Module Directives
- Module directives are statements at the top of a code module that instruct VBA how to treat the code within the code module.
- Option Explicit is missing from the above module. Without Option Explicit any typographical error in variable names results in a new variant-type variable being created by the compiler. This behavior may not cause an immediate runtime error, but it will almost certainly cause the Excel application to return incorrect results.
2) Naming Convention
4) Turning Off ScreenUpdating and Automatic Calculation
5) Ranges should be fully qualified
6) Explicitly call the default property of an Object
7) Error handling
9) Use "" (2 double quotes) instead of vbNullString
- Naming convention is the system developers use to name the various parts of the application. A good variable naming convention conveys the type, scope and purpose of the variable with a simple visual inspection of its name. This allows the developer to concentrate on what the code is doing rather than having to figure out how the code is structured.
- The procedure under review has very poor naming convention, if any. To add to the confusion, some variables are named after VBA properties e.g. Source, Names & Count. A Replace All (Ctrl-H in VBE) operation using such variables would most likely replace VBA properties as well.
- Procedures or subroutines should be given a name that describes the task they perform. "Summary" seems like a very generic description.
- Code comments should provide a clear and complete description of how the code is organized, how each object and procedure should be used and what the code is trying to accomplish.
- The "more lovely code ..." comment fails to meet any of these objectives, plus you will realize how misleading it is, if you keep reading our review.
4) Turning Off ScreenUpdating and Automatic Calculation
- There is a significant performance gain during Excel automation, if Screen Updating is off (Excel doesn't refresh the display continually) and if Calculation is set to manual (Excel doesn't recalculate everything, if data is written to a range).
- Although, this sample workbook doesn't have lots of formulas, it is best practice to identify excel's original calculation mode at the start of the procedure stack (eg Dim lOriginalCalcMode as Long... lOriginalCalcMode = Application.Calculation, then set Application.Calculation = xlManual at the top of the procedure stack, run the code and then reset the calculation mode to the original setting by using Application.Calculation = lOriginalCalcMode right before the program execution ends.
- Whenever code depends on Excel formulas, the range containing the formulas should be recalculated using the 'Calculate' method.
- Please set .Calculation = xlAutomatic, before saving a workbook. Excel sets the initial calculation mode from the first workbook opened or created. The user may get unexpected results, if this not the expected behavior. Please read our article on how Excel determines calculation mode.
- In general, turning off events (if fired-up events are not being used) will speed up VBA code performance, but not much incrementally. Use DoEvents whenever required, if events are turned off.
- Please use Application.DisplayAlerts = False with caution and only if Excel alerts should be suppressed during VBA execution.
5) Ranges should be fully qualified
- Since two workbooks are used, it is best practice to fully qualify the ranges.
e.g. ThisWorkbook.Range("strSourceFile"), ActiveWorkbook.Range("strNames")
6) Explicitly call the default property of an Object
- By avoiding the implicit use of default properties, the code is much mode readable and protected from errors, if the default behavior of the object changes in some future version of Excel / VBA.
- For example: Source = Thisworkbook.Range("strSourceFile").value2
- .Value2 is faster than .Value with numbers (no significant difference with text) as explained by Excel MVP Charles Williams in his blog.
- The importance of this best practice rule becomes more obvious when used with less popular objects, for example like Names, where the default member of the Names property is .RefersTo (neither .Name nor .Value).
7) Error handling
- Error handling is one of the most commonly omitted features in Excel applications. This is not acceptable, as the last thing the users should see is a distressing runtime VBA error. A good error handler helps the developer to diagnose and correct errors.
- The above procedure lacks an error handler. An error is quite likely to occur when attempting to open a workbook (workbook moved/renamed, locked with password, corrupted).
- In addition, the "On Error Resume Next" statement is used in the calling procedure to ignore errors. This is a bad practice. "On Error Resume Next" is not a substitute for writing code correctly.
- Please read our article in Excel error handling.
- Code layout makes not a bit of difference to the computer. Good visual layout of VBA code helps the developer to infer a significant amount of information about the logical structure of the program.
- For example, there is no code indentation in the For...Next loop, so it is not immediately evident which elements go together.
9) Use "" (2 double quotes) instead of vbNullString
- vbNullString is slightly slower than double quotes ""
- For example: strComments = "" is indeed better strComments = vbNullString
- In addition, typing "" is much faster than vbNullString, even with Intellisense assistance.
- Macro performance slow when page breaks are visible in Excel: Microsoft article 199505
- The Integer, Long, and Byte Data Types: MSDN Article
Protect VBA With Unviewable+
Click Chris' testimonial to learn more about Unviewable+ VBA
