Early vs Late Binding

[ADezii]

The process of verifying that an Object exists and that a specified Property or Method is valid is called Binding. There are two times when this verification process can take place: during compile time (Early Binding) or run time (Late Binding). When you declare an Object Variable as a specific Data Type, you are using Early Binding so the verification can take place during compile time. When you declare a Variable of the generic Object Data Type, you are using Late Binding. In this case, VBA must find and verify the Object information during any execution of VBA statement that includes a Reference to the Object or one of its Properties or Methods. The time difference between Early and Late Binding can be quite significant. Some examples of Early Binding are listed below:

1. Dim appAccess As Access.Application
2. Dim appExcel As Excel.Application
3. Dim winExcel As Excel.Window
4. Dim winProject As Project.Window
5. Dim chkBox As CheckBox
6. Dim cboFinance As ComboBox
7. Dim chtMain As Chart
8. Dim lstLookup As ListBox
9. Dim pvtNew As PivotTable

As an example, I'll refer to an ADO Field as an ADODB.Field and also as an Object (commented out). Benchmark Test Procedures using the code below report a hugh difference in the speed of accessing the ADO Field's Properties, naturally, in favor of Early Binding. The code is listed below.

Dim rst As ADODB.Recordset, strName As String
 
'Early Binding. Declaring fld as ADODB.Field results in
'times that are around 7% of those measured declaring fld
'as Object. In the case of several hundred Records, the difference
'would not be significant, but in the case of several hundred
'thousand Records, the time difference would be enormous.
Dim fld As ADODB.Field
 
'Late Binding
'Dim fld As Object
 
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.Source = "tblEmployee"
rst.CursorType = adOpenStatic
 
rst.Open
 
rst.MoveFirst
 
Set fld = rst.Fields(0)
Do While Not rst.EOF
  strName = fld.Name
    rst.MoveNext
Loop
 
rst.Close
Set fld = Nothing
Set rst = Nothing

In every case, if at all possible, declare a Variable using the most specific Object Type that you can. For Access Controls, that means using, for example:

Dim cmd As CommandButton

instead of:

Dim cmd As Control

or, worst of all:

Dim cmd As Object

[dima69]

The process of verifying that an Object exists and that a specified Property or Method is valid is called Binding. There are two times when this verification process can take place: during compile time (Early Binding) or run time (Late Binding). When you declare an Object Variable as a specific Data Type, you are using Early Binding so the verification can take place during compile time. When you declare a Variable of the generic Object Data Type, you are using Late Binding. In this case, VBA must find and verify the Object information during any execution of VBA statement that includes a Reference to the Object or one of its Properties or Methods. The time difference between Early and Late Binding can be quite significant. Some examples of Early Binding are listed below:

1. Dim appAccess As Access.Application
2. Dim appExcel As Excel.Application
3. Dim winExcel As Excel.Window
4. Dim winProject As Project.Window
5. Dim chkBox As CheckBox
6. Dim cboFinance As ComboBox
7. Dim chtMain As Chart
8. Dim lstLookup As ListBox
9. Dim pvtNew As PivotTable

As an example, I'll refer to an ADO Field as an ADODB.Field and also as an Object (commented out). Benchmark Test Procedures using the code below report a hugh difference in the speed of accessing the ADO Field's Properties, naturally, in favor of Early Binding. The code is listed below.

Dim rst As ADODB.Recordset, strName As String
 
'Early Binding. Declaring fld as ADODB.Field results in
'times that are around 7% of those measured declaring fld
'as Object. In the case of several hundred Records, the difference
'would not be significant, but in the case of several hundred
'thousand Records, the time difference would be enormous.
Dim fld As ADODB.Field
 
'Late Binding
'Dim fld As Object
 
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.Source = "tblEmployee"
rst.CursorType = adOpenStatic
 
rst.Open
 
rst.MoveFirst
 
Set fld = rst.Fields(0)
Do While Not rst.EOF
  strName = fld.Name
    rst.MoveNext
Loop
 
rst.Close
Set fld = Nothing
Set rst = Nothing

In every case, if at all possible, declare a Variable using the most specific Object Type that you can. For Access Controls, that means using, for example:

Dim cmd As CommandButton

instead of:

Dim cmd As Control

or, worst of all:

Dim cmd As Object

Some drawbacks from early binding.
In order to use early binding to the objects like Excel, i.e.

Dim appExcel As Excel.Application

you must explicitly set the reference to that object library. This can cause your code to stop running on the user machine, (If, for example, he doesn't have Excel installed) - without any chance of error handling.
On the other hand, although late binding is much slower, you usually don't need to set it more than once, so the difference can hardly be noticed in overall application performance.

[ADezii]

Some drawbacks from early binding.
In order to use early binding to the objects like Excel, i.e.

Dim appExcel As Excel.Application

you must explicitly set the reference to that object library. This can cause your code to stop running on the user machine, (If, for example, he doesn't have Excel installed) - without any chance of error handling.
On the other hand, although late binding is much slower, you usually don't need to set it more than once, so the difference can hardly be noticed in overall application performance.

1. I would still much rather explicitly check for the Reference, and if it exists, use the much faster Early Binding. You could always, as a last resort, resort to Late Binding - something similar to Conditional Compilation.
   

   Public Function fExcelExists() As Boolean
   Dim ref As Reference
    
   For Each ref In Application.References
     If ref.Name = "Excel" Then
       fExcelExists = True
         Exit Function
     End If
   Next
       fExcelExists = False
   End Function

   If fExcelExists() Then
     'continue normal processing here
   End If

2. In the illustrated code demo demonstrating Early vs Late Binding, (ADODB.Field vs Object), the difference was staggering. ADODB.Field process time was only 7% of the parallel Object approach.

[dima69]

[list=1][*] I would still much rather explicitly check for the Reference, and if it exists, use the much faster Early Binding. You could always, as a last resort, resort to Late Binding - something similar to Conditional Compilation.

Public Function fExcelExists() As Boolean
Dim ref As Reference
 
For Each ref In Application.References
  If ref.Name = "Excel" Then
    fExcelExists = True
      Exit Function
  End If
Next
    fExcelExists = False
End Function

If fExcelExists() Then
  'continue normal processing here
End If

The only problem with this code is that it will not work if the reference is actually missing. Although checking for reference validity at runtime is possible, it is much more complicated, and still the solution is not 100% robust. The most complete explanation I saw on this is here: http://www.trigeminal.com/usenet/usenet026.asp