ThisWorkbook.Range('strSourceFile'), ActiveWorkbook.Range('strNames')Ħ) Explicitly call the default property of an Object Since two workbooks are used, it is best practice to fully qualify the ranges.Į.g.Please use Application.DisplayAlerts = False with caution and only if Excel alerts should be suppressed during VBA execution.Use DoEvents whenever required, if events are turned off. In general, turning off events (if fired-up events are not being used) will speed up VBA code performance, but not much incrementally.

Please read our article on how Excel determines calculation mode. The user may get unexpected results, if this not the expected behavior. Excel sets the initial calculation mode from the first workbook opened or created. Calculation = xlAutomatic, before saving a workbook.

Whenever code depends on Excel formulas, the range containing the formulas should be recalculated using the 'Calculate' method.

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.

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.

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).

' comment fails to meet any of these objectives, plus you will realize how misleading it is, if you keep reading our review.Ĥ) Turning Off ScreenUpdating and Automatic Calculation

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.

'Summary' seems like a very generic description.

Procedures or subroutines should be given a name that describes the task they perform.

A Replace All (Ctrl-H in VBE) operation using such variables would most likely replace VBA properties as well. To add to the confusion, some variables are named after VBA properties e.g.

The procedure under review has very poor naming convention, if any.

This allows the developer to concentrate on what the code is doing rather than having to figure out how the code is structured. A good variable naming convention conveys the type, scope and purpose of the variable with a simple visual inspection of its name.

Naming convention is the system developers use to name the various parts of the application.

Excel vba examples programming