Late vs Early Binding

posted May 16, 2013, 6:34 AM by Craig Hatmaker   [ updated Apr 21, 2014, 8:45 AM ]
An unfortunate quote:
"Late binding is slower than early binding because the binding takes place during run time. When you do the work in design time, the code will run faster"
Such quotes would be more meaningful with measures so readers can understand what "slower" really means and in what situations.

What is "Late Binding" and "Early Binding"?
These terms refer to when object variables are created which can make the difference in your project working or not. 

Early Binding creates an object at compile time. To create an object at compile time requires a loaded reference to the object so VBA knows how to compile the object variable. References can be found in the VBE's menu Tools > References (see figure above). Checked references are loaded. Unchecked references are not. Loaded references bring new data types into VBA allowing us to declare variables like so: 

Dim oCN As ADODB.Connection

ADODB.Connection is not a native VBA data type. That data type is supplied by the reference "Microsoft ActiveX Data Objects #.# Library".  The reference also supplies VBA what it needs for "intellisense" (See http://en.wikipedia.org/wiki/Intelligent_code_completion)


Late Binding "creates" an object at run time. To create a variable at run time we use VBA's CreateObject() function.
  
Dim oCN as Object

Set oCN = CreateObject("ADODB.Connection")

As we can see, there is a bit more setup for Early Bound variables versus more coding for Late Bound variables.  Because late bound variables are created only after the program is running, VBA cannot offer intellisense for late bound variables


Recommendations
For those who create workbooks for themselves and only work on one PC, use early binding.  For all else, use late binding.


Why use Late Binding?
Late binding makes apps more robust (less likely to break).  Here is why.  

When we look at the reference list, we will likely see several "Microsoft ActiveX Data Objects #.# Library". The PC I'm using now has 8. If I select "Microsoft ActiveX Data Objects 6.1 Library" to create an app and then give that app to someone whose PC only has "Microsoft ActiveX Data Objects 2.0 Library", my app will crash on their PC. My App won't crash if I use late binding because late binding doesn't require references. VBA automatically finds the best available reference for late bound variables.  So I can create an ADODB Connection and not worry which reference is on PCs I might distribute my app to.


But what About Speed?
Some think late binding is slower because late bound objects are created at run time whereas early bound objects are created at compile time. There is no real difference between the two because VBA is not a truly compiled language. Each line of VBA is compiled when it is first encountered after a workbook is opened. So it doesn't matter if an object is created at compile time or run time.  The time to create objects either way must be endured with every workbook open and the time to create objects at either compile time or run time is nearly identical.  In my test creating ADO objects is a hair faster when late bound.  As long as we are careful to create objects once and reuse them instead of constantly creating objects and setting them to nothing after each use, we will find late bound ADO is every bit as fast as early bound ADO.


Comments