Whilst this site is primarily about using Excel, there are times when Excel is not the best tool for the job. Each Office application is designed for different tasks. Tables and charts may be easily prepared in Excel, but they are often exported to PowerPoint, Word or Outlook for the purposes of presentation and distribution. Whilst these are all different applications they all incorporate the VBA programming language. This means that VBA in Excel can be used to control these other applications, and these other applications can also be used to control Excel.
This series of posts is all about how we can use VBA control other Office applications.
To be able to control other applications, Excel needs to know how to refer to the different objects within that application. Excel doesn’t know this automatically, so we have to tell it to use the application’s object library. For example, if we wanted Excel to create a new slide in a PowerPoint presentation, we first need to tell Excel to use the PowerPoint Object library. This is process is known as binding. Once Excel knows the PowerPoint Object Library, it is able to control PowerPoint through PowerPoint’s Object Model. This is not just limited to PowerPoint; Word, Outlook, Adobe Acrobat and many other applications have Object Libraries, all of which Excel can use to speak their language.
When creating this link between Excel and the other application, there are two binding options (1) early binding (2) late binding. This is what we will be considering for the rest of this post.
Early Binding
Within the VB editor it is possible to reference the Object Library for the Office applications. This reference to the object library is saved within the Excel file itself. Therefore, any time the file is opened the reference to the Object Library is still intact and is already available.
In the Visual Basic Editor select Tools -> References.
Select the reference for the application and click OK.
To control the application we can create a variable to hold the instance of the application. The code to do this would be:
Dim pptApp As PowerPoint.Application Set pptApp = New PowerPoint.Application
If the reference to the Object Library has not been created the following error message will appear.
Late Binding
When using Late Binding, it is not necessary to create a reference to the Object Library in advance. By creating an object in VBA we can bind to the necessary Object Library at the time the code runs.
Dim pptApp As Object Set pptApp = CreateObject("PowerPoint.Application")
Example – Controlling Powerpoint from Excel via VBA
This example code will open a new PowerPoint Presentation and write text onto the first slide. This example users Late Binding
Sub ConnectToPowerpoint() 'Set the App as a variable using Late Binding Dim pptApp As Object Set pptApp = CreateObject("PowerPoint.Application") 'To use Early Binding use these two lines, rather than the lines above 'Dim pptApp As PowerPoint.Application 'Set pptApp = New PowerPoint.Application 'Create the Presentation as an Object Dim pptPresentation As Object Set pptPresentation = pptApp.Presentations.Add 'Create the Slide as an Object (1 slide, view type 2) Dim pptSlide As Object Set pptSlide = pptPresentation.Slides.Add(1, 2) ' 'Make Powerpoint Visible pptApp.Visible = True 'Select the first slide pptSlide.Select 'Change the text on the first Slide pptSlide.Shapes(1).TextFrame.TextRange.Text = "Hi PowerPoint" pptSlide.Shapes(2).TextFrame.TextRange.Text = "Hi Excel, nice to meet you" 'Activate the Powerpoint Application pptApp.Activate 'Release the memory Set pptSlide = Nothing Set pptPresentation = Nothing Set pptApp = Nothing End Sub
Advantages & Disadvantages of each type of Binding
It is not possible to pick one binding method and stick to it for the rest of your VBA career. Each type of Binding has certain advantages, which make it better than the other in certain circumstances. As there are only two options the advantages of one are generally the disadvantages of the other.
Early Binding – disadvantages
The Object Libraries are updated and changed each time Office is updated. When this happens, the Object Libraries are given a new version number. If the version of Excel on which the Object Library reference was created is different to the version on it is executed the code will not run correctly. Excel, for example, might have been bound to version 12 of the object library, but the code might need to be run on a PC with version 14 installed.
Late Binding – disadvantages
Generally, the code runs slower and requires more processing power. Excel does not know about the Object Library of the other application, so it does not bind until the code is executed.
It is harder to write the code in the VB Editor as it will not include the Intellisense options. (Intellisense is the name for the list of methods and properties which pop up in VB Editor when writing VBA code).
The VBA names for options and settings are not available. For example, in PowerPoint VBA it is possible to paste as a Bitmap image using code ppPasteBitmap, however with Late Binding you must use the enumeration number, which is 1. As a result, the code is less readable.
Which binding method should you choose?
Microsoft recommends using Early Binding where possible.
For me, the answer depends on whether I can guarantee which version of Object Library is available. For example, where the macro will only be run on a single PC, or where there is consistent IT infrastructure and software, then Early Binding is a good option. However, if the code is to be distributed and used on unknown IT software and infrastructure then Late Binding is the best option.
You need to be able to use both options to select the right version for your circumstances.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
Hi Mark,
You can have the best of both worlds, with a bit of prep. In general, DEVELOP using early-binding, in order to take advantage of Intellisense and compilation error-trapping. DISTRIBUTE using late-binding, to prevent unsightly error messages from appearing on clients’ machines.
There’s some stuff you can do with ON ERROR RESUME NEXT to check for Object Libraries (e.g., try to set an object reference – if it fails, do not allow your macros to process any code that depends on that reference.)
Ideally, you’ll be writing to a known machine, and it’s just a matter of ensuring version compatibility.
Cheers,
Mitch
Hi Mitch – thank you for commenting.
That’s a good tip about switching binding when moving from development to distribution.
I must admit, I’ve not tried error trapping for Object Libraries, but I like the idea. I will definitely give it a go.