Office VBA: Reference downgrade problem

In the world of Office VBA programming, many a times there is a need to automate other Office applications from an Office application's VBA code. For example - You have Office 2016 installed on your machine and you would like to automate Excel from Word VBA. One of the ways to achieve this would be to go to the Word VBA project and add a reference to "Microsoft Excel 16.0 Object Library". This way of binding is called early binding. Also, there is another flavor of binding called "late binding".

A detailed explanation of the Early binding/Late binding is mentioned in the below article:

https://support.microsoft.com/en-us/kb/245115

You may face a problem related to the early binding in an environment where users use different versions of Office. Let's understand this with the help of an example. Suppose you have Office 2013 installed on your machine and you want to automate Excel from Word VBA code. To achieve this, you would add a reference of "Microsoft Excel 15.0 Object Library" in Word VBA project. Now if you use this VBA code on any machine where you have Office 2013 installed this will work fine. Also, if you try to run it on a machine with Office 2016, it will still run as the Office upgrades the references in your project to greater version. In this case if you check the references in the Word VBA project, you will find that the reference "Microsoft Excel 15.0 Object Library" has been upgraded to "Microsoft Excel 16.0 Object Library". If you save the Word file on a such a machine(with Office 2016) and then try to run the same file on a machine with lower version of Office (i.e. Office 2013, Office 2010 etc.), the code will fail to run due to missing reference since the VBA project cannot find the reference. This is by design. VBA references are upgraded but are not downgraded.

So if your VBA code is being used in such an environment where users have different versions of Office, then late binding is the way to go. Since late binding doesn't involve working with references, this problem does not occur.

Below is the example of code written using early and late binding:

Early binding

=========================================

Dim exApp As Excel.Application

Dim exBooks As Excel.Workbooks

Dim exBook As Excel.Workbook

Set exApp = New Excel.Application

exApp.Visible = True

Set exBooks = exApp.Workbooks

Set exBook = exBooks.Add()

 

Late binding

=========================================

Dim exApp As Object

Dim exBooks As Object

Dim exBook As Object

Set exApp = CreateObject("Excel.Application")

exApp.Visible = True

Set exBooks = exApp.Workbooks

Set exBook = exBooks.Add()