How To: Convert from Early Binding to Late Binding

Why Convert from Early Binding to Late Binding 

I have found that developing using Early Binding is quicker than using Late Binding, mainly because I depend on Intellisense to prompt me..

I have also found that when I distribute with Early Binding  there can be problems because I have a client who was ahead of me in releases of MS-Office, i.e. they had 2013 while my company was still using 2010. Each copied is configured for the user: Directory structures for referencing  files and auxiliary programs are in a supporting worksheet.  That all worked well when I moved it up, but not when I moved it back to me.

Some reading indicates that Early Bound project files are larger than late bound.  I have not confirmed this, but I'll take them at their word.  They're more experienced than I.

Refreshers: Binding, Early and Late Binding

A VBA project is developed using a host Object Library, e.g. MS-Excel. Binding is tying to that host Object Library a "foreign" Object Library (e.g. for MS-Word) in order to use its features and manipulate files that are native to the foreign environment.

With Early Binding, the foreign, or "hosted", Object Library is selected using the Tools...References dialogue in the Virtual Basic Environment (VBE).  This makes the hosted Object Library available for Intellisense and at compile time.

When Late Binding is used the hosted Object Library is unchecked in the Tools...References dialogue.  The project is made aware of the foreign Object Library as the VBA code is executed, i.e. at run time.

See also:

• Binding: Expanding a Project to Other Applications
• How To: Early Bind
• How To: Late Bind

Conversion Steps

Converting from Early to Late Binding is more tedious than hard.

1. Remove the Reference to the hosted Object Library.
   That was the easy part.
   Now comes the tedious part.
2. Change data types in declarations for hosted Object Library objects to "as Object".  
3. Compile to identify any declarations that were not changed to "as Object".
4. If there were any compiler errors, return to Step 2.
5. Once compilation completes without errors, run the code.  This will 

Remove the Reference to the hosted Object Library

Basically, this reverses the How to Early Bind A Foreign Application section of  How To: Early Bind.

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

   

   Opening the References dialogue

3. Uncheck the Object Library to remove, in this case "Microsoft Word 14.0 Object Library"

   

   References List with Hosted Object Library

4. Click OK then save the file

Change Data Types to "as Object"

This is the part that can be tedious.

1. For each module, scan for Dim, Public, and Global declarations of object variables.
2. Identify whether each is a native or foreign Data Type, e.g. "as Document" in an Excel project would be foreign whereas "as Workbook" or "as Worksheet" would be native.
3. Change to "as Object"
   NOTE:  Be aware that some Object Type-names occur in multiple Object Libraries.  For example, "Range" is used in both the Word and Excel Object Libraries but they have different Methods and Properties.

How to make Early-to-Late Binding Easier

Use Find/Replace

In Step 2 of Change Data Types to "as Object", once a declaration of a foreign Object is found, 

1. Highlight the "as <object>" portion of the declaration 
2. Click Ctl-F to show the Find/Replace dialogue
   
3. Click the Replace button
4. Type "as object" in the Replace With: text box
   
5. Choose whether to perform the action in the Current Procedure. Current Module, or Current Project.
6. Click the "Replace" or "Replace All" button, depending on confidence that the phrase "As Documents" does not appear elsewhere and in a context where it should not be changed, e.g. in comments.

Module Design

With VBA I have found that I have a pretty wide latitude in what is included in "normal" Modules, i.e. non-Class Modules that are not linked to an object like a Workbook or Userform.   

One criteria I use is "Do these Subs and Function perform together?"  Therefore, I have modules specific to a portion of the users process.  

Another module scoping criteria I use is "Are these Subs and Functions common across the process?"  For example, Subs for opening files are in one module and  functions for validating files are grouped together.  

That leads to a criteria of "Do these Subs and Functions deal with a foreign Object Library?"  In short, put all Subs and Functions that would declare Early Bound objects from one foreign Object Library into the minimum number of modules that is feeasible.

Module Naming

As simple as this sounds, modules should have meaningful names that convey to the programmer was is in inside.  Projects with default names like  "Module1", "Module2" etc is something I've seen and the programmer comes off with an "Of course!  It's Module37...or something like that."

In short, tag modules affected by binding with something like "_WORD" at the end if their names. 

Incremental Transition

Contrary to what may be inferred above, Late Binding code does work while the foreign Object Library is bound to the host.  Therefore, if there is a module of the project that needs the bound foreign Object Library that has been completed, it is possible to 

1. Unbind the foreign Object Library using the Tools-References dialogue
2. Change to the "as Object" declarations.
3. Compile and run to test.
4. Rebind  the foreign Object Library using the Tools-References dialogue.
5. Continue development in Early Bound mode.