Binding: Expanding a Project to Other Applications

Introduction

My start in VBA programming was opening Excel files, mooshing them around, and creating bigger Excel files.  Then I learned how to open and manipulate text files as inputs.  Life was good.

The next step, driven by need, was to learn how to send Excel files as attachments to Outlook email.  That was my introduction to "binding", exploiting the Object Library of a different  ("foreign") application.

After spending much time and many words in Late Binding: What they don't tell you! explaining how I discovered how to successfully use late binding to run macros in an Excel workbook's project from a VBA macro in an MS-Access project, I decided it would be a good idea to get to the crux of the matter in one pos.  I think the process I went through might be insightful to others, and a good reminder to myself, but it hides the real how-do-I-do-this-and-why message that should be evident in a good tip.

Definition of Binding

Binding is the addition of the object library from another application into the environment of the host.  Another way to say it is the hosted, or "foreign" application's object library is bound (as in "tied" or "glued") to the host environment.

Binding inserts into the host hosted application's object definitions, including the associated properties, events, and methods.

Without binding it would not be possible to open a file that belongs to another application.  Trying to invoked the  "Workbooks.Add" method is meaningless in the MS-Word object library and therefore cannot be executed until the Excel object library is bound to it.   The result of trying to execute an unbound foreign method is a 424 run-time error, "Object Required", if Option Explicit is not used or a Compile Error: Variable not Defined when Option Explicit is specified.

What is needed is a live reference to the other application's object library.

Why Bind?

Binding is used when developing an application in one application's environment, e.g. Excel, and there is a need to generate, use as input, or manipulate a file whose "home" is another environment, e.g. creating and send an email using Outlook.

An alternative to binding would be to have code hosted in each of the different applications then the user executes each macro in order.  By binding, the macros in one application (the host) can call the macros in another, can invoke methods from the hosted object library

Since my experience so far is only in MS-Office applications, all of the following examples are a "host" Office application (or Object Library) and one or more "guest" or "foreign" or "bound" or "hosted" Office applications (Object Libraries)..
/

Host Application	Need
MS-Access	Excel input file needs to be manipulated prior to being added as a linked table, e.g. unprintable characters removed.
MS-Excel	Use output of an Access query to control creation of a periodic report.
MS-Word	Send an Outlook email with a message and attachment.
MS-PowerPoint	Include in a presentation sections or graphics from a Word document, e.g. a mission statement that is standard for all organizations. Use an Excel table to control the content of a presentation, e.g. one type of organization would include certain slides that others do not have, such as a P&L slide for business units but not for staff departments.

When Binding Occurs

The concept of binding did not spring forth from object orientation.  Prior to VBA, Java and object oriented languages, mainframe programs (source code) were compiled then the object code was linked together and finally the program was bound to databases.  This is still the case.  Binding was and is a post compilation step.

What is different with VBA binding it it may occur prior to using the interpreter/compiler or during their usage.  Binding using the compiler is "early binding", at run-time it's called "late binding".

To Bind or not to Bind

Binding is not always the answer to making the the MS-Office applications act like a suite.  

Each office application has 2 environments, the User Interface (UI) and the Visual Basic Environment, Binding occurs in the VBE,  Many live links can be made between files in the UI that need no binding.  These are built "manually" in the UI. When they updated, or refreshed, from the UI.

• A section of a Word document embedded in a PowerPoint presentation.
• A Excel worksheet that has the results of an Access query.

These are built "manually" in the UI. When they need to be updated, or refreshed, that can be done from the UI or by triggering the Refresh method in the host environment.

The "rules" for when to bind are both positive and negative.

• Binding can be difficult to implement the first few times. There are tricks that it seems no one shares (or maybe it's that I don't see what everybody else sees as intuitively obvious). Stay within your bounds.
• There is no need to bind when links between files can be established in the UI and refreshing is sufficient for the updated information.  Establishing and refreshing links do not require opening the foreign file. It is a read only call.
• Binding is necessary for opening/saving/closing "foreign" files.
• Once an foreign application and its Object Library are bound to the host, and an associated file opened, all of it's previously written Subs are available to run, including those associated with events such as Worksheet_Activate, 

Early Binding and Late Binding

As mentioned above, if binding is performed during compilation that is "early binding; at run-time is "late binding".

Early Binding Explained

Early Binding is done by explicitly including the object library of the foreign application into the host VBE during development.  Details of this process are in  How To: Early Bind. 

In short, early binding can be set-up before any code is written.  Think of it as a form of c/c++ #include.  

Once the early binding is set-up it is possible to write code as if you are in the foreign VBE because when that Object Library is within the VBE.

Late Binding Explained

Late binding is a done by code, only.  The detailed description is in How To: Late Binding.

The two Object Libraries are unbound until the VBA code creates the binding at run time by using a statement such as Set appWord =  CreateNew("Word.Application").

Although the two Object Libraries are now bound, there is still one coding disfferebceOnce bound, late binding and early binding code is nearly identical.

Early Binding or Late Binding

The table below lays out the differences between early and late binding.  The green highlight indicates which is advantageous for each factor.

Factor	Early Binding	Late Binding	Explanation
Programming ease	Easier	Harder	Because in Early Binding  the foreign Object Library is available to the VBE, the properties and methods are provided as type-ahead prompts. Because in Late Binding the foreign Object Library is not available until run-time, the programmer needs better knowledge of each objects components (properties and methods).  Because of this, some errors in coding are not found until run-time.
File Size	Larger	Smaller	Per what I have read from reliable sources, Including the foreign Object Libraries in Early Binding increases the host file size. Because the binding to the foreign Object Libraries occurs during code execution, file size remains the same. NOTE: My early tests show no increase in file size when adding one or more foreign Object Libraries via the VBE's Tools...References dialogue.  My assumption at this point is this is because these workbooks are devoid of any usage of the foreign Object Libraries and the increase occurs during compilation.
Execution Speed	Faster	Slower	Forcing the VBE to interpret what type of objects are needed for "Set..." commands at run time  slows  down these steps when using Late Binding.
Portability	Lower	Higher	Early binding is forward-compatible, i.e. code developed in Office 2010 can run in Office 2013.  MS changes the reference to the newer Object Library. Early binding is not backwards compatible, i.e. code developed in Office 2013 cannot run in Office 2010.  MS does not change the reference to the  older Object Library. Sometimes this can be fixed manually. Late-binding, because it does not identify the foreign Object Library until run-time, is both forward and backward compatible

From this table you can decide whether to use Early or Late Binding based on the importance of each factor.

Another way of making the decision as to whether to use early or late binding is to make Early Binding the default decision then choose Late Binding only if the circumstances are significantly different from this ideal.

• Multiple instantiations of foreign objects, e.g. looping through files in a folder then using
  Set <foreign object> = <foreign application>.Open <filename>
• Open timeframe for development/delivery
• Developer not experienced in LateBinding
• Delivery will be to one user
• Developer and user(s) are on sync'ed release upgrade cycles.

Early Binding then Late Binding

As the table above shows, Early Binding and Late Binding are "the same" when measured by the number of factors that favors each.  Unfortunately, one cannot use Late Binding with Early Binding to push everything to green.  Each foreign Object Library is either Early or Late Bound at any point in time.

However, it is possible (and recommended) to develop using Early Binding then convert to Late Binding.  This improves development speed then gets the improvement in portability.

Conversion from Early Binding to Late Binding, is straight-forward but can be tedious.  See  How To: Convert from Early Binding to Late Binding for more details.

Post Conversion Improvement

One expected (unconfirmed by test) issue with this approach is that the file size increase established by using Early Binding development will remain until orphaned OpCodes are purged.  Initial research into removing orphaned OpCodes indicates this can be done either by a utility such as Code Cleaner or taking the following steps.  (Note:  From my understanding, this would be a good process for all projects where file size may be an issue)

1. Export all modules to text files
2. Delete all modules
3. Import the text files that were exported in step 1.