Early Binding and Late Binding

When coding using VBA, as a developer, you have a choice of either using Early Binding or Late Binding.
Below I will attempt to explain in plain English what each technique consists of and go over their Pros and Cons.
Please keep in mind the advice provided below is all relative to Early and Late Binding, nothing more.

Early Binding

Early Binding consists in setting specific Library References within your VBA Project (Tools -> References).

Benefits

The benefits of using Early Binding include:

• The use of Intellisense
  • Say your add a reference to Excel, then you gain all the Intellisense relating to Excel (properties, Methods, …)
• The use of constants
  • Say your add a reference to Excel, then you gain the ability to directly use Excel’s application specific constants, things like: xlSolid, xlContinuous, xlCenter, xlByRows, …
• Use of the Object Browser
• Help (using F1)
  • You can use F1 to quickly access help relating to any property, method, …, but this will err with Late Binding.

Drawbacks

The MAJOR (or not depending on your user’s setup) drawback to Early Binding is that it can lead to versioning issues when your program is used on multiple versions of MS Office (or whatever program you are automating). Say you develop a 2013 accdb database and set the Microsoft Excel 15.0 Object Library because you perform some Excel automation. Now if you have a user try and open the accdb in 2010, they will be presented with errors at the startup
[Pic] , and the database itself will throws seemingly random errors with code that has nothing to do with Excel to the point of being unusable.
[Pic]?

On the other hand, if a user opens the same accdb in Access 2016, Access, if a newer library is registered, will automatically upgrade the reference and as such, the database will work seamlessly.

So keeping this in mind, this is why you should always perform any development using the oldest version of the program on which your solution will be run. So if you have users running Office 2013, 2016 and 2003, you need to perform your development using Office 2003 if you are using Early Binding!

Early Binding Example

'Function to open a specified Word file
'Req'd: Reference to Microsoft Word XX.X Object Library
Function OpenWordDoc1(sFileName As String)
    Dim oApp                  As Word.Application
    Dim oDoc                  As Word.Document

    On Error Resume Next
    Set oApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then    'Word isn't running so start it
        Set oApp = CreateObject("Word.Application")
    End If
    On Error GoTo Error_Handler

    Set oDoc = oApp.Documents.Open(sFileName)
    oApp.Visible = True

Error_Handler_Exit:
    On Error Resume Next
    Set oDoc = Nothing
    Set oApp = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: OpenWordDoc" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

The above can also be re-written as follows when using Early Binding (although I always use the above approach as there have been Bugs noted when using the New … coding)

'Function to open a specified Word file
'Req'd: Reference to Microsoft Word XX.X Object Library
Function OpenWordDoc2(sFileName As String)
    Dim oApp                  As Word.Application
    Dim oDoc                  As Word.Document

    On Error Resume Next
    Set oApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then    'Word isn't running so start it
        Set oApp = New Word.Application 'CreateObject("Word.Application")
    End If
    On Error GoTo Error_Handler

    Set oDoc = oApp.Documents.Open(sFileName)
    oApp.Visible = True

Error_Handler_Exit:
    On Error Resume Next
    Set oDoc = Nothing
    Set oApp = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: OpenWordDoc" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Late Binding (Dynamic Binding)

Late Binding on the other hand does not make use of pre-defined Libraries and hence it’s MAJOR benefit and thus does not suffer from versioning issues. Code written in Office 2016 will typically run just fine in Office 2013, 2010, …, 97 (always assuming the library is registered on the PC – You can’t perform say Excel automation if Excel isn’t installed!).

Late binding, or dynamic binding, is a computer programming mechanism in which the method being called upon an object is looked up by name at runtime.

Benefits

The key benefits of Late Binding include:

• Does not require declaring Reference Libraries
• Does not suffer from versioning issues

Drawbacks

The main drawbacks of Late Binding include:

• Loss of Intellisense during development
• No constants, so you have to declare the constants yourself when writing your code
• Since the process is all done in real-time (dynamically binding to libraries in real-time), technically speaking, Late Binding will be slower that Early Binding. That said, with today’s computer hardware, the difference is truly not noticeable. So to me, this point is moot.

Late Binding Example

'Function to open a specified Word file
'Req'd: None
Function OpenWordDoc(sFileName As String)
Dim oApp As Object 'Word.Application
Dim oDoc As Object 'Word.Document
 
On Error Resume Next
    Set oApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then 'Word isn't running so start it
        Set oApp = CreateObject("Word.Application")
    End If
On Error GoTo Error_Handler
 
    Set oDoc = oApp.Documents.Open(sFileName)
    oApp.Visible = True
 
Error_Handler_Exit:
    On Error Resume Next
    Set oDoc = Nothing
    Set oApp = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: OpenWordDoc" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

The Best of Both World

Now that you understand the basic concepts and see the benefit of Early Binding (Intellisense to aid in coding) and the benefit of Late Binding (no versioning issues, thus can be distributed with much greater ease) we can explorer how we can combine both techniques to gain the best of both worlds.

Although the coding examples provided above are very simple ones, they do illustrate just how simple it is, normally, to convert between Early and Late Binding. As such, it is very easy during development to work in Early Binding to ease the coding process and then when you are ready to distribute your solution into production you simply switch to Late Binding by changing a few declarations and hard coding a few constants.

You may also wish to review Late Binding in Microsoft Access in which Tony Toews demonstrates how you can use conditional compiling instruction to accommodate both techniques within the same code

Some thoughts about all of the above

As an experienced developer I have, over time, come to truly value Late Binding for the flexibility it provides and use it in all my more recent code.

That being said, if you are a corporate developer in an organization with a very controlled environment in which you can be guaranteed that everyone is running the same versions of Office (or whatever program you are automating) then you can utilize Early Binding without any issues.

The Story Continues

Be sure to check out part 2 of this article: VBA – Early Binding and Late Binding – Part 2 in which I delve more into how to implement The Best of Both Worlds.

Resources

Using early binding and late binding in Automation
Early vs. Late Binding
Early vs. Late Binding
VBA references and early binding vs late binding