5.7. Automation Performance Tips

Because automation involves the remote control of a server application by a client application, performance is always an issue. What follows are some basic tips to help you optimize your application's performance when using automation.

Use early binding wherever possible

Because an early bound reference to an object can be resolved at compile time, by the VB or VBA IDE, rather than at design time, by the runtime module, the result of early binding is significantly better performance. It isn't always possible to use early binding, but you should always try to use it if you can.

Use as few "dots" as possible

Every dot that you place in your code represents at least one (and possibly many) procedure calls that have to be executed in the background. Both the For Each...Next loop and the With statement can be used to improve performance. But what can really help is locally caching object references. For example, you should store references to the upper levels of an object model in local object variables, then use these references to create other objects further down the hierarchy. For example, to reference a cell in an Excel spreadsheet, you could use this code:

Dim oExcel As Excel.Application
Set oExcel = New Excel.Application
For i = 1 to 10
    oExcel.Workbooks(1).Worksheets(1).Cells(1,i).Value _
           = "Something"
Next i

The following code, though, would be far more efficient, because the calls to obtain references to the Workbook and Worksheet object would only be made once, whereas above they are being made 10 times:

Dim oExcel As Excel.Application
Dim oWorkBk As Excel.WorkBook
Dim oWorkSht As Excel.WorkSheet

Set oExcel = New Excel.Application
Set oWorkBk = oExcel.Workbooks(1)
Set oWorkSht = oWorkBk.WorkSheets(1)

For i = 1 to 10
    oWorkSht.Cells(1,i).Value = "Something"
Next i

Improve your own performance: use the macro recorders

If the application you are programming against has a built-in macro recorder, you should try to use it whenever possible. (In the Office applications that support it, you can access the macro recorder by selecting the MacroRecord New Macro submenus from the Tools menu.) For example, the basic code for the sample Excel and Word applications used earlier in this chapter was written using their respective macro recorders. You can save yourself hours of valuable programming time by letting the application tell you what method and property calls you need to make to achieve a particular result. You can then focus on optimizing its code to achieve the best possible performance.