Late vs Early Binding

Post date: May 16, 2013 1:34:26 PM

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?

Creating objects like ADODB.Connection take so little time that Microsoft Windows' timer cannot measure a single iteration or even several hundred iterations on my machine. So as long as we are not creating and destroying bindings thousands of times, we will not be able to perceive any performance degradation. And we really shouldn't be creating bindings more than once. For example, we can create a connection object, open and close it many times, but keep the connection object in memory using static variables so we can reuse it.

See also: See; http://support.microsoft.com/kb/245115