VB & VBA in a Nutshell: The Language

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