By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,706 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Early vs Late Binding

ADezii
Expert 5K+
P: 8,607
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
Aug 5 '07 #1
Share this Article
Share on Google+
3 Comments


dima69
Expert 100+
P: 181
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.
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.
Aug 6 '07 #2

ADezii
Expert 5K+
P: 8,607
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
Expert 100+
P: 181
[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