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.
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:
instead of:
or, worst of all:

Aug 5 '07 #1

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.

Expand|Select|Wrap|Line Numbers

1. Dim rst As ADODB.Recordset, strName As String
2.  
3. 'Early Binding. Declaring fld as ADODB.Field results in
4. 'times that are around 7% of those measured declaring fld
5. 'as Object. In the case of several hundred Records, the difference
6. 'would not be significant, but in the case of several hundred
7. 'thousand Records, the time difference would be enormous.
8. Dim fld As ADODB.Field
9.  
10. 'Late Binding
11. 'Dim fld As Object
12.  
13. Set rst = New ADODB.Recordset
14. Set rst.ActiveConnection = CurrentProject.Connection
15. rst.Source = "tblEmployee"
16. rst.CursorType = adOpenStatic
17.  
18. rst.Open
19.  
20. rst.MoveFirst
21.  
22. Set fld = rst.Fields(0)
23. Do While Not rst.EOF
24.   strName = fld.Name
25.     rst.MoveNext
26. Loop
27.  
28. rst.Close
29. Set fld = Nothing
30. 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:

Expand|Select|Wrap|Line Numbers

1. Dim cmd As CommandButton

instead of:

Expand|Select|Wrap|Line Numbers

1. Dim cmd As Control

or, worst of all:

Expand|Select|Wrap|Line Numbers

1. Dim cmd As Object

Some drawbacks from early binding.
In order to use early binding to the objects like Excel, i.e.
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.

Aug 6 '07 #2

ADezii

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

Expand|Select|Wrap|Line Numbers

1. 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.
   
   Expand|Select|Wrap|Line Numbers

   1. Public Function fExcelExists() As Boolean
   2. Dim ref As Reference
   3.  
   4. For Each ref In Application.References
   5.   If ref.Name = "Excel" Then
   6.     fExcelExists = True
   7.       Exit Function
   8.   End If
   9. Next
   10.     fExcelExists = False
   11. End Function
   Expand|Select|Wrap|Line Numbers

   1. If fExcelExists() Then
   2.   'continue normal processing here
   3. 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.

Aug 6 '07 #3

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.

Expand|Select|Wrap|Line Numbers

1. Public Function fExcelExists() As Boolean
2. Dim ref As Reference
3.  
4. For Each ref In Application.References
5.   If ref.Name = "Excel" Then
6.     fExcelExists = True
7.       Exit Function
8.   End If
9. Next
10.     fExcelExists = False
11. End Function

Expand|Select|Wrap|Line Numbers

1. If fExcelExists() Then
2.   'continue normal processing here
3. 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

Aug 6 '07 #4