Early vs Late Binding  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
# 1
Aug 5 '07
| |
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: - Dim appAccess As Access.Application
- Dim appExcel As Excel.Application
- Dim winExcel As Excel.Window
- Dim winProject As Project.Window
- Dim chkBox As CheckBox
- Dim cboFinance As ComboBox
- Dim chtMain As Chart
- Dim lstLookup As ListBox
- 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: instead of: or, worst of all: |  | Expert | | Join Date: Sep 2006 Location: Israel
Posts: 181
# 2
Aug 6 '07
| | | re: Early vs Late Binding
Quote:
Originally Posted by 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: - Dim appAccess As Access.Application
- Dim appExcel As Excel.Application
- Dim winExcel As Excel.Window
- Dim winProject As Project.Window
- Dim chkBox As CheckBox
- Dim cboFinance As ComboBox
- Dim chtMain As Chart
- Dim lstLookup As ListBox
- 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: instead of: or, worst of all: 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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
# 3
Aug 6 '07
| | | re: Early vs Late Binding
Quote:
Originally Posted by dima69 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. - 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
- 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.
| | Expert | | Join Date: Sep 2006 Location: Israel
Posts: 181
# 4
Aug 6 '07
| | | re: Early vs Late Binding
Quote:
Originally Posted by ADezii [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 |  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,295 network members.
|
