Expert 8TB |
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: | | Share: Expert 100+ |
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 8TB |
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 100+ |
[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 | | Sign in to post your reply or Sign up for a free account.
Similar topics
4 posts
views
Thread by Olaf Meding |
last post: by
|
8 posts
views
Thread by deko |
last post: by
|
2 posts
views
Thread by Mystery Man |
last post: by
|
5 posts
views
Thread by Plat |
last post: by
|
2 posts
views
Thread by mark |
last post: by
|
21 posts
views
Thread by ManningFan |
last post: by
|
2 posts
views
Thread by kogrover |
last post: by
|
reply
views
Thread by RN1 |
last post: by
|
1 post
views
Thread by pedestrian via DotNetMonster.com |
last post: by
| | | | | | | | | | |