How To: Early Bind

Introduction

A number of posts in this blog mention early binding.  Binding: Accessing Other Office Apps summarizes provides a description/definition of "binding", discusses the two types of binding (Early and Late), and contrasts the two.  The purpose of this post is to give a succinct description of how to Early Bind.

In short, "binding" is the act of including a "foreign" object in a "host", conceptually related to #include.  Late binding is initiated during compile/translation.  Early binding is created during before compile/translation.

How to Early Bind A Foreign Application

1. Access the Visual Basic Environment (VBE) 
2. Click on the Tools menu and select References

   

   Opening the References dialogue

3. Scroll down the list and select the appropriate Object Library
   

   

   Currently Selected Object Libraries
   

   

   Unselected Object Libraries

4. Click OK then save the file

Binding Priorities

Sometimes more than one Object Library uses the same name for a component.  For example, both the Excel and Word Object Libraries have an object called "Range".  These, however, have different properties.

If this occurs

• Use the up/down arrows to put the default above the secondary.
  NOTE:  The host Object Library is, by default, the default object library.
• Be specific when making declarations, e.g. instead of "as Range" use "as Excel.Range" or "as Word.Range".  While this is a generally good practice for bound projects, it is absolutely necessary when declaring a variable of an object type of a subordinate Object Library and an Object Library with a higher priority has an object of the same name/

How to "Early Bind" A File

This post concentrates on Early Binding of foreign MS-Office applications.  The steps above can also be used to add a reference to another file compatible with the host environment, e.g. another workbook project with functions that are needed for this Excel project.  I'm not sure that this is really "Early Binding", but since I was in the neighborhood it seemed like an opportune time to include this information/

For AddIn files (e.g. Excel files with "xla" and "xlam" extensions), the same effect can be accomplished in the User Interface via the Add-Ins dialogue, opened from the Developer menu.

Develop Menu with Add-Ins Highlighted

Excel's Add-In Dialogue

Using Early Bound Object Libraries

Once the Reference to the Object Library is established it is possible to create and otherwise manipulate objects in the hosted (or "foreign") application.  It is not quite like writing VBA in the native (or host) Object Library, but there are a few things to keep in mind if this may be changed to Late Binding later.

1. The hosted application must be declared as a variable so that it can be instantiated (created) in the host.  With Early Binding it is acceptable to use an <application_name>/Application object type 
• Dim appExcel as Excel.Application 'Runs faster
• Dim appWord as Object 'Slower.  Compatible with Late Binding
2. The hosted applications are instantiated with either a New or CreateObject statement
• Set appExcel = New Excel.Application ' Works with Early binding but not late binding
• Set appWord = CreateObject("Word.Application") 'Compatible with Late Binding
3. The objects to be manipulated must also be declared.  Since they are in the hosted Object Library, they may be declared as an object type from that library, with the object library specified or assumed, or as an Object
• Dim docAgreement As Document ' Works with Early binding but not late binding
• Dim docAgreement As Word.Document ' More precise, Works with Early binding but not late binding
• Dim docAgreement As Object 'Slower.  Compatible with Late Binding
4. Use of foreign methods may explicitly identify the foreign application 
• Set docAgreement  = Documents.Add  ' Works with Early binding but not late binding
• Set wkbkAnalysis  = appExcel Workbooks.Add 'Compatible with Late Binding

Beyond that, writing code with early bound applications is like working in the "foreign" VBE since Intellisense prompting for property and method names is added to the host VBE, leveraging its awareness of the "foreign" Object Library.