Early vs. Late Binding

There are two ways to connect Excel with another application, Early Binding and Late Binding. Early Binding sets the connections between Excel and the other application early in the process, i.e., during design time. In Late Binding, the connection isn't made until later, during run time. In general, Early Binding is the preferred method.

Early Binding has several advantages. Because the Object Model of the other application is known to Excel during development, the developer can make use of Intellisense and the Object Browser to help write his code. Semi-descriptive constants, such as ppLayoutTitle and xlXYScatter, can be used. The developer can also compile the code to assure there are no syntax errors. And finally, Early Bound procedures run a little faster.

Late Binding does not allow the developer to use many programming and debugging tools, such as Intellisense, the Object Browser, and compiling. If Late Binding must be used for other reasons, though, the developer could set all the proper references, write and debug the project, then remove the references and change object variable declarations. Late Bound code also runs more slowly than Early Bound code. The advantage to Late Binding is that the developer does not have to worry about the wrong version of an object library being installed on the user's computer. For example, if I know my client will be automating Word, but I don't know which version, I can use Late Binding, and not worry that my reference to Word XP will break on an Office 2000 machine. I could set a reference to the earliest version that the client might use, say, Excel 97, or I can simply use Late Binding.

This is an incomplete description of early and late binding. For more details please refer to links like the following:

    support.microsoft.com/default.aspx?scid=kb;EN-US;245115
    http://msdn.microsoft.com/en-us/library/0tcf61s1(vs.71).aspx
    word.mvps.org/FAQs/InterDev/EarlyvsLateBinding.htm
    www.dicks-clicks.com/excel/olBinding.htm

Early Binding

The first important step to enable Excel to use Early Binding to interact with other applications is to set a reference to the other application's object library. In the Visual Basic Editor (VBE), select References... from the Tools menu and the following dialog box appears:

Scroll down the list of available references until you encounter the required object library. Notice in this dialog box, I have already checked references to Word and PowerPoint. The version number 8.0 refers to Office 97 applications; 9.0 through 11.0 refer to Office 2000, Office XP (2002), and Office 2003.

At the top of your procedure you need to declare some object variables specific to the application being automated. To automate Microsoft Other, you would code the following lines:

Dim otherApp As Other.Application
Dim otherDoc As Other.DocType
Dim otherSpecificObjects As Other.SpecificObjects

Then to open a new instance of Other:

Set otherApp = CreateObject("Other.Application")

or to use an existing instance of Other:

Set otherApp = GetObject(, "Other.Application")

For example, to open a new PowerPoint instance, create a new file, do some stuff, save and close the file, and quit PowerPoint, your code would look something like this:

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

Set PPApp = CreateObject("Powerpoint.Application")
Set PPPres = PPApp.Presentations.Add
Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)

'' Do Some Stuff Here

With PPPres
    .SaveAs "C:\My Documents\MyPreso.ppt"
    .Close
End With

PPApp.Quit

Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

Late Binding

In Late Binding, there is no object model linked to the project, so all object variables pointing to the other application must be declared as simple Objects. Any intrinsic constants must be converted to their numerical equivalents.

Dim otherApp As Object
Dim otherDoc As Object
Dim otherSpecificObjects As Object

The code used to open a new instance of Other is unchanged from Early Binding:

Set otherApp = CreateObject("Other.Application")

The code to use an existing instance of Other is also unchanged:

Set otherApp = GetObject(, "Other.Application")

To open a new PowerPoint instance, create a new file, do some stuff, save and close the file, and quit PowerPoint, your code would look something like this:

Dim PPApp As Object
Dim PPPres As Object
Dim PPSlide As Object

Set PPApp = CreateObject("Powerpoint.Application")
Set PPPres = PPApp.Presentations.Add
Set PPSlide = PPPres.Slides.Add(1, 11)  '' ppLayoutTitleOnly = 11

'' Do Some Stuff Here

With PPPres
    .SaveAs "C:\My Documents\MyPreso.ppt"
    .Close
End With

PPApp.Quit

Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

Notice that the PowerPoint variable ppLayoutTitleOnly has been replaced by its numerical value (11) above (see the green comment). The easiest way to determine the values of these constants is, before removing the reference to the other application's object model, finding it in the Object Browser, or in the Immediate Window, typing a question mark followed by the constant name, then pressing Enter:

?ppLayoutTitleOnly
 11

The desired value is returned in the Immediate Window, ready to be placed into the code. I kept the constant name in the comment as partial documentation of this section of code.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | PTS Blog | Training | Charts and Tutorials

Peltier Technical Services, Inc., Copyright © 2016. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile