Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Using VBA to control other Office applications

Using Excel VBA to Control Office

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.

VBA Control Office Applications - Object Library

Select the reference for the application and click OK.

VBA Control Office Applications - Object Library Powerpoint

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.

VBA Control Office Application - Error Message No Object Library

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.


Headshot Round

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:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. 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.
  4. 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:

2 thoughts on “Using VBA to control other Office applications

  1. Mitchell Allen says:

    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

    • Excel Off The Grid says:

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *