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

31 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

  13. Neil Goundar says:

    Hi Rory, Across the years I have seen this issue crop up and have a question around the use of late binding.

    If we use Late Binding and have User Interfaces which are for example referencing and using controls from Windows Common Controls library how would we late bind the UI? Or does the errors you mention only come up with the underlying code and the UI is adverse to dll issues?

    I am thinking that the UI is not adverse as well as a few years back I saw an issue where the controls would show a blue box and not work well.

  14. Live2DCodeCode2Live says:

    What a gem of a find! This is by far the most succinct constructed explanation of Early / Late binding that nails it down and what’s more, makes for a better way to code for portability. Hope you don’t mind, but when I find good examples of code, I keep it. 😉

  15. Pingback: VBA Late Binding - For userforms based on user computers that do not have Word References - QuestionFocus

  16. Josh Devayya says:

    This fixed a super infuriating issue for me. Being able to learn about early/late binding before applying the code was very helpful. Smushed the code into what I was already using and it now seems to work perfectly after 3 tests.

    Thank you very much!

  17. Brian Mathys says:

    This seems to be working for me, but I’m still having an issue.. I created a macro in Excel to count the number of pages in a given Section in OneNote. The macro was created in 2010 (the version we are on at work), but when I try to run the macros in Excel 2016, they no longer work. I believe the issue is Excel 2010 uses Ref Lib OneNote 14.0 and Excel 2016 only gives the option of OneNote 15.0. Therefore, I am trying “late binding”.

    Everything seems to be working except the line, “OneNote.GetHierarchy “”, hsNotebooks, notebookXml, xs2010″ which gives the error below.

    Error: Run-time error ‘-2147319779 (8002801d)’
    Automation error
    Library not registered.

    Any thoughts??

      • Brian says:

        That seems to have done the trick! I won’t know until tonight when I can try it at home, but the late binding macro is now successfully working on Excel 2010 in the same way that the early binding is working! Thanks for the help!

      • Brian Mathys says:

        I just tried it on Excel 2016 and it doesn’t work.. Does late binding work when going forward in versions?

        Here is my code:
        Private Function GetFIrstOneNoteNotebookNodes(OneNote as Object) As MSXML2..IXMLDOMNodeList

        Dim notebookXML as String
        Const hsNotebooks as Integer = 2
        Const xs2010 As Integer = 1

        OneNote.GetHierarchy “”, hsNotebooks, notebookXML, xs2010

        The last line is what gives me the error:

        “Run-time error ‘-2147319779 (8002801d)’:

        Automation error
        Library not registered.”

        Any thoughts?

          • Brian Mathys says:

            It seems to be. I can use the code in Office 2016 without late binding by updating the references (e.g., OneNote.application rather than OneNote14.application).
            In this case, getHierarchy function works. But if I late bind it in Office 2016, the function no longer works.. However, late binding it in Office 2010 does work.

          • Brian Mathys says:

            This is my Late Binding code from Office 2010.

            Sub CollectNotebookNicknames()
            ‘ Connect to OneNote 2010.
            ‘ OneNote will be started if it’s not running.
            Dim OneNote As Object
            Set OneNote = CreateObject(“OneNote.Application”)

            ‘ Get all of the Notebook nodes.
            Dim nodes As MSXML2.IXMLDOMNodeList
            Set nodes = GetFirstOneNoteNotebookNodes(OneNote)
            ‘^^ This calls the below function and passes the Object “OneNote”) to the Private Function below:

            Private Function GetFirstOneNoteNotebookNodes(OneNote As Object) As MSXML2.IXMLDOMNodeList
            ‘ Get the XML that represents the OneNote notebooks available.
            Dim notebookXML As Object
            Const hsNotebooks As Integer = 2 ‘These constants must be defined for Late Binding
            Const xs2010 As Integer = 1 ‘These constants must be defined for Late Binding

            ‘ OneNote fills notebookXml with an XML document providing information
            ‘ about what OneNote notebooks are available.
            ‘ You want all the data and thus are providing an empty string
            ‘ for the bstrStartNodeID parameter.

            OneNote.GetHierarchy “”, hsNotebooks, notebookXML, xs2010

  18. Gergely says:

    It is the best discussion of this topic I could find 🙂 The explanation is crystal clear even for someone how is an amateur. You should give others some how to write short essays on programming 🙂
    Thanks a lot.

Leave a Reply to Live2DCodeCode2Live Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.