IntroductionA 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
- Access the Visual Basic Environment (VBE)
- Click on the Tools menu and select References
Opening the References dialogue
- Scroll down the list and select the appropriate Object Library
Currently Selected Object Libraries
Unselected Object Libraries
- Click OK then save the file
- 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
|Develop Menu with Add-Ins Highlighted|
|Excel's Add-In Dialogue|
Using Early Bound Object LibrariesOnce 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.
- 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
- Set appExcel = New Excel.Application ' Works with Early binding but not late binding
- Set appWord = CreateObject("Word.Application") 'Compatible with Late Binding
- 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
- Set docAgreement = Documents.Add ' Works with Early binding but not late binding
- Set wkbkAnalysis = appExcel Workbooks.Add 'Compatible with Late Binding