VBA references and early binding vs late binding

At some point, many VBA programmers will find themselves dealing with a references problem. This usually manifests itself as an error message along the lines of “Can’t find project or library” with the debugger highlighting what appears to be a fairly innocuous line of code – often involving a native VBA function like Date, Left or Format.

The cause of the problem is usually a reference to a non-default object library – for example, if you are working in Excel but need to automate Word or Outlook, you will typically set a reference to the required library and then happily program against it. The issue arises when you come to distribute the project and it is run by someone who has a different version (usually an earlier one) of the programs in question. So for example, you may have developed using Office 2010 and then distributed the project to someone using 2007. When they try to run it, the phone calls begin…

Unfortunately, although the non-default project references will usually update themselves to match a later version of software, that does not often happen with earlier versions, and that is what causes code errors. Using the example of the Date function, unless you have explicitly written VBA.Date in the code, the compiler doesn’t know where the Date function is located and has to look through the referenced libraries to try and find it – as soon as it comes across a broken reference, the error is raised.

 

So – what to do? The most obvious options are:

  • Write code to update the references on the target machine. This is difficult to implement well and, even if you can, it will fail if the security restrictions on the target machine are not set as you need them.
  • Develop in the lowest version of the software that you need to support. This is a simple ‘fix’ and will work in many cases but by no means all. For instance, if someone saves your project in a later version of the software that will update the references and if it is then distributed to someone with an earlier version, you are back to square one.
  • Late bind your code. This often the most effective solution and what I will cover now.

 

In code terms, the difference between early and late binding is simple:

If you declare a variable as Object, you are late binding it.

There is no more to it than that in the strict definition of the term.

 

However, the real benefit of late binding – i.e. fixing the references issues – is that it allows you to not set a reference to the other library at all. If there’s no reference, there’s nothing to break – problem solved.

In order to do this in practice, we require three basic steps, rather than the one simple one mentioned above:

  1. Declare your object variables as Object, rather than as, say, Word.Document or Outlook.Application
  2. Instead of using the New operator to instantiate the objects, you must use CreateObject with the relevant class name.
  3. Either declare any constants you use from the target library, or use their literal values instead (the former I believe is better practice).

 

Here is an early bound and a late bound example of some simple code to manipulate Outlook from Excel – I have highlighted the differences with comments in the code:

Early bound version

Sub SendOLMail_EarlyBound()
    ' declare variables for the Application and Mailitem objects
   Dim oAPP                       As Outlook.Application
    Dim oItem                      As Outlook.MailItem

    ' instantiate the Application
   Set oAPP = New Outlook.Application

    ' create a new email
   Set oItem = oAPP.CreateItem(olMailItem)

    ' set basic properties and display the email
   With oItem
        .To = "foo@bar.com"
        .Subject = "this is a test"
        .Body = "nothing to see here"
        .Display
    End With

End Sub

Late bound example

Sub SendOLMail_LateBound()
    Dim oAPP                       As Object
    Dim oItem                      As Object
    ' need to declare this constant as it has no meaning without
   ' the reference set to the Outlook library
   Const olMailItem               As Long = 0

    ' instantiate the Application - cannot use New without a reference
   ' so we must use CreateObject
   Set oAPP = CreateObject("Outlook.Application")

    ' #######################################
   ' NOTE: THE REST OF THE CODE IS IDENTICAL
   ' #######################################

    ' create a new email
   Set oItem = oAPP.CreateItem(olMailItem)

    ' set basic properties and display the email
   With oItem
        .To = "foo@bar.com"
        .Subject = "this is a test"
        .Body = "nothing to see here"
        .Display
    End With

End Sub

15 thoughts on “VBA references and early binding vs late binding

  1. Pingback: Reference Library Problem

  2. GeneO says:

    There appears to be security restrictions using Excel 2010 and Outlook 360. I have not found a way to make this work. I’ve been searching all over. Any insights appreciated? If Outlook is not already open it throws errors.

  3. Pingback: Question on References in VBA

  4. Pingback: VBA problem with define

  5. Pingback: Need to Add Outlook Object Library

  6. Pingback: Code to install Word Object Libray

  7. Pingback: Macro to open Powerpoint and paste ranges from Excel - Troubleshoot error in code - Page 4

  8. Pingback: Automatic add reference - Not working

  9. Pingback: VBA - Early Binding and Late Binding | DEVelopers HUT

  10. Michael Reddan says:

    That’s by far the best explanation of Early/Late binding I’ve ever read. I actually get it now. Thanks so much.

  11. Pingback: Userform listbox not registering selection after upgrade to MS 2016

  12. Pingback: Email from Excel Headache - Page 2

Leave a Reply

Your email address will not be published. Required fields are marked *