PowerPoint Archives - Peltier Tech

You can use VBA to streamline a lot of your day-to-day practices, eliminate boring tasks, and reduce tedium. A great example of a VBA project that would save a lot of time is report generation in Word or PowerPoint based on Excel content. Of course, such a project is more advanced than simply recording and replaying macros would be. I’ll use the small task of exporting an Excel chart to PowerPoint to illustrate such inter-application automation, and I’ll follow up soon with more intricate routines that accomplish more tasks. Export an Excel Chart to Word shows a similar example that exports an Excel chart to Word.

There are a number of things you need to understand:

Excel’s Object Model
PowerPoint’s (Word’s, etc.) Object Model
Early and Late Binding, References, Libraries

We can learn all about Excel’s object model by recording macros and examining the code, as well as using tools like IntelliSense and the Object Browser in the VB Editor. And there’s always Google at our disposal. Search on “Excel VBA” and the particular keyword that has you stumped, or type the exact error message into the search box.

We learn about another application’s object model through VB Editor tools and of course Google. PowerPoint has Charts, Shapes, and Tables like Excel does; PowerPoint’s charts and shapes behave much like Excel’s, but its tables are much different. And PowerPoint has Presentations and Slides instead of Workbooks and Worksheets.

I’ll cover Early and Late Binding briefly, so that you can get up and running.

Early and Late Binding

When you write code, you are making use of code libraries, which contain the objects, methods, properties, and other keywords for the language you are using. For example, if you are writing Excel VBA code, you are using the Visual Basic for Applications library, the Microsoft Excel Object Library, the Microsoft Office Object Library, and others. In the VBA Editor, these libraries enable IntelliSense, which are the smart tooltips and dropdown boxes that help you get the code written quickly.

Early Binding

Early Binding is when you ensure that all libraries used in your project are explicitly identified when you are writing the code (at “design time”), by setting a reference to the library in your project (“binding” it to the project).

Early Binding enables design tools (IntelliSense, the Object Browser, etc.) in the VBA Editor to access an external library, so you can write your code more quickly.

IntelliSense ToolTip Showing Relevant Syntax

IntelliSense Dropdown Listing Appropriate Options

Early Binding also saves time by telling the system what library to use, instead of making the system look up all the possible libraries and guess which one to use. In practice, I haven’t found this delay to be significant.

If I use Early Binding, and share my program with others, they must have all of the referenced libraries on their computer, or the language (i.e., VBA) has to be able to figure out which library to use instead. For example, if I program in Excel 2016, the project will automatically contain references to Excel 2016 and Office 2106. VBA is smart enough to use the relevant versions of the Excel and Office libraries if someone uses the project in an older or newer version of Excel. If I set a reference to PowerPoint 2016, however, VBA isn’t so smart. It will successfully use a newer version of the PowerPoint library, but if the user has an older version, he will get a compile error, and nothing will run.

Late Binding

Late Binding is when you don’t explicitly bind libraries to your project at design time, but instead let your program try to figure it out later (at “run time”).

Late Binding doesn’t help you find information via IntelliSense or the Object Browser, so development may be slower.

If a needed library is not present on the user’s computer, and you have used Late Binding, you still get an error if the system can’t find the library. However, it’s not a compile error; it’s a run-time error, and you can write code to handle it gracefully.

I believe there are some libraries which do not work unless they are specifically bound to the project that uses them, but everything I’ve needed for routine VBA automation has worked fine with Late Binding.

What Should I Use?

There are tradeoffs between Early and Late Binding. IntelliSense and other guidance that is made available by Early Binding is invaluable. On the other hand, the compile errors due to missing references are catastrophic. Many developers will use Early Binding while developing their code, setting the references they need; once the code is ready to ship, they switch to Late Binding, removing the references and making the other small changes to avoid compile errors in the code.

The Example Project

Here is a simple data set and chart in Excel.

Data and Chart in Excel

Here is a blank slide in PowerPoint.

Blank PowerPoint Slide

We’re going to export that chart from Excel onto that PowerPoint slide.

Early Binding – Setting References

To set a reference to an external library, go to the VB Editor and make sure the project you’re working on is the active project. Go to the Tools menu > References, and the References dialog appears.

This dialog lists each open workbook and add-in, as well as all libraries installed on your system. This includes many that are not appropriate for VBA development, and using these may result in spectacular system failures. But any of them may one day come in handy. For example, long ago I used the WinAmp library (remember that? probably not) to build a UserForm-driven music utility, in which I could set up playlists and play music files in Excel.

References Dialog

Several references are added by default: Visual Basic for Applications, Microsoft Excel 2016 Object Library, OLE Automation, and Microsoft Office 2016 Object Library; if you add a UserForm to your project, Microsoft Forms 2.0 Object Library is also added automatically. In this view you can see that the Microsoft PowerPoint 16.0 Object Library has also been checked. If it’s not checked and you haven’t used it recently, you’ll have to scroll down until you find it, then check it.

These library references are only set for the active VB project. Other projects may have different libraries referenced. For example, I used two workbooks while working on this tutorial: one with the PowerPoint library checked for Early Binding, the other without for Late Binding.

Outline of Our Procedure

Here is the entire VBA procedure that will export the active Excel chart to PowerPoint. The code is organized like this:

We have declared several object variables at the top of the procedure.
- In Early Binding, these objects are declared as PowerPoint objects.
- In Late Binding, these objects are declared as generic objects.
We prevent an error if the user has not selected a chart.
We figure out where to paste the chart.
- We look first for a running instance of PowerPoint.
  - If PowerPoint is not running, we start it up.
- We look for the active presentation.
  - If there is no active presentation, we create one and insert a slide with the desired blank layout.
    - In Early Binding, we use the PowerPoint named constant ppLayoutBlank.
    - In Late Binding, we use its numerical equivalent (12).
- We look for the active slide.
  - If we can’t find the active slide, we insert one.
  - (In older versions of PowerPoint, a new presentation started with no slides.)
We copy the active chart.
We paste the chart as a shape object in PowerPoint (it’s still a real chart).
We align the chart/shape on the slide.

Early Binding – The VBA Code

Option Explicit

Sub SimpleActiveChartToPowerPoint_EarlyBinding()
  Dim pptApp As PowerPoint.Application
  Dim pptPres As PowerPoint.Presentation
  Dim pptSlide As PowerPoint.Slide
  Dim pptShape As PowerPoint.Shape
  Dim pptShpRng As PowerPoint.ShapeRange
  Dim lActiveSlideNo As Long
  
  ' bail out if no active chart
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again!", vbExclamation, "Export Chart To PowerPoint"
    Exit Sub
  End If
  
  ' figure out what slide to paste on
  On Error Resume Next
  Set pptApp = GetObject(, "PowerPoint.Application")
  On Error Resume Next
  
  If pptApp Is Nothing Then
    Set pptApp = CreateObject("PowerPoint.Application")
    Set pptPres = pptApp.Presentations.Add
    Set pptSlide = pptPres.Slides.Add(1, ppLayoutBlank)
  Else
    If pptApp.Presentations.Count > 0 Then
      Set pptPres = pptApp.ActivePresentation
      If pptPres.Slides.Count > 0 Then
        lActiveSlideNo = pptApp.ActiveWindow.View.Slide.SlideIndex
        Set pptSlide = pptPres.Slides(lActiveSlideNo)
      Else
        Set pptSlide = pptPres.Slides.Add(1, ppLayoutBlank)
      End If
    Else
      Set pptPres = pptApp.Presentations.Add
      Set pptSlide = pptPres.Slides.Add(1, ppLayoutBlank)
    End If
  End If
  
  ' copy chart
  ActiveChart.ChartArea.Copy
  
  ' paste chart
  With pptSlide
    .Shapes.Paste
    Set pptShape = .Shapes(.Shapes.Count)
    Set pptShpRng = .Shapes.Range(pptShape.Name)
  End With
  
  ' align shape on slide
  With pptShpRng
    .Align msoAlignCenters, True ' left-right
    .Align msoAlignMiddles, True ' top-bottom
  End With
End Sub

Late Binding – The VBA Code

Option Explicit

Sub SimpleActiveChartToPowerPoint_LateBinding()
  Dim pptApp As Object  ' PowerPoint.Application
  Dim pptPres As Object  ' PowerPoint.Presentation
  Dim pptSlide As Object  ' PowerPoint.Slide
  Dim pptShape As Object  ' PowerPoint.Shape
  Dim pptShpRng As Object  ' PowerPoint.ShapeRange
  Dim lActiveSlideNo As Long

  ' bail out if no active chart
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again!", vbExclamation, "Export Chart To PowerPoint"
    Exit Sub
  End If

  ' figure out what slide to paste on
  On Error Resume Next
  Set pptApp = GetObject(, "PowerPoint.Application")
  On Error Resume Next

  If pptApp Is Nothing Then
    Set pptApp = CreateObject("PowerPoint.Application")
    Set pptPres = pptApp.Presentations.Add
    Set pptSlide = pptPres.Slides.Add(1, 12)  ' 12=ppLayoutBlank
  Else
    If pptApp.Presentations.Count > 0 Then
      Set pptPres = pptApp.ActivePresentation
      If pptPres.Slides.Count > 0 Then
        lActiveSlideNo = pptApp.ActiveWindow.View.Slide.SlideIndex
        Set pptSlide = pptPres.Slides(lActiveSlideNo)
      Else
        Set pptSlide = pptPres.Slides.Add(1, 12)  ' 12=ppLayoutBlank
      End If
    Else
      Set pptPres = pptApp.Presentations.Add
      Set pptSlide = pptPres.Slides.Add(1, 12)  ' 12=ppLayoutBlank
    End If
  End If

  ' copy chart
  ActiveChart.ChartArea.Copy

  ' paste chart
  With pptSlide
    .Shapes.Paste
    Set pptShape = .Shapes(.Shapes.Count)
    Set pptShpRng = .Shapes.Range(pptShape.Name)
  End With

  ' align shape on slide
  With pptShpRng
    .Align msoAlignCenters, True  ' left-right
    .Align msoAlignMiddles, True  ' top-bottom
  End With
End Sub

Bits and Pieces

The Objects

We need to use the PowerPoint Application (we use the Excel one all the time in Excel VBA, but usually get away with ignoring it), plus a presentation, and a slide. The chart is pasted as a Shape, and we make it a member of a ShapeRange to align it within the slide. I’ve shown the Early Binding PowerPoint object types below; in Late Binding, these are all plain old Objects.

  Dim pptApp As PowerPoint.Application
  Dim pptPres As PowerPoint.Presentation
  Dim pptSlide As PowerPoint.Slide
  Dim pptShape As PowerPoint.Shape
  Dim pptShpRng As PowerPoint.ShapeRange

User-Proof the Code

Here we test that the user has selected the chart. If he has not, we pop up a message reminding him to select one next time.

  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again!", vbExclamation, "Export Chart To PowerPoint"
    Exit Sub
  End If

Select a Chart, Dummy!

Get the PowerPoint Application

We use GetObject to find the running instance of PowerPoint. We wrap it in On Error statements so we don’t get an error if it’s not running. If it’s not running, pptApp is in fact Nothing, so we use CreateObject to start it up.

  On Error Resume Next
  Set pptApp = GetObject(, "PowerPoint.Application")
  On Error Resume Next
  
  If pptApp Is Nothing Then
    Set pptApp = CreateObject("PowerPoint.Application")

PowerPoint is a bit funny here, since it only supports one running instance at a time. We could be lazy and simply use CreateObject, and it will fetch the running instance or fire up a new one as needed. But what I have used is generally the proper way to do it, and it doesn’t hurt if we go through all the motions even if we don’t always need to.

At this point, it would be wise to test that we were able to start up PowerPoint. If we couldn’t start PowerPoint, for example, if PowerPoint were not installed on the computer, we would have to abandon the procedure without trying to use the various PowerPoint objects later in the code. We should also alert the user so he could rectify the situation.

Get the PowerPoint Presentation and Slide

ActivePresentation is probably obvious enough. If there is one, continue; otherwise create one first.

ActiveSlide is a tough one, because even though such an object would be useful, there is no ActiveSlide object in PowerPoint. The first thing you must check is whether the active presentation has any slides. Sounds dumb, but in prior versions of PowerPoint, when you created a new presentation, it started out with no slides, and you had to click in the window to insert the first slide. Presentations.Add also inserts a new presentation with no slides. So we need to add a slide to a new presentation and to an existing presentation that had no slides yet.

If the active presentation has at least one slide, we find the active slide with a convoluted line of code:

pptApp.ActiveWindow.View.Slide.SlideIndex

I think this line of code assumes certain views, like Normal view (with Slide pane, Slide Sorter pane, and Notes pane) and Slide view (Slide pane only). A more rigorous procedure would check for this to make sure to avoid the associated error.

Again, we should check that we did successfully end up with a presentation and a slide, to avoid errors later in the code.

Copy, Paste, and Align

Copying is straightforward: we copy the chart’s chart area. We could instead copy the chart’s parent ChartObject, which works exactly the same for an embedded chart, but would crash for a chart sheet.

We use pptSlide.Shapes.Paste to paste the chart as a shape (a chart’s ChartObject is the Excel Shape that contains the chart), as a member of the slides collection of Shapes.

To align the Shape, we first have to define the shape as the latest Shape added to the Slide, and then define a ShapeRange, which is a funny way to say a bunch of Shapes which is different than a collection of Shapes. Finally we align this shape range in the center middle of the slide.

    Set pptShape = pptSlide.Shapes(.Shapes.Count)
    Set pptShpRng = pptSlide.Shapes.Range(pptShape.Name)
    pptShpRng.Align msoAlignCenters, True  ' left-right
    pptShpRng.Align msoAlignMiddles, True  ' top-bottom

We could get into trouble here if the chart was not successfully copied or pasted.

The Result

Whether we use Early or Late Binding, the result is the same. Below is our formerly blank PowerPoint slide, containing our Excel chart.

PowerPoint Slide Containing Our Chart

Summary

This tutorial showed how to use VBA to export an Excel chart to PowerPoint. Both Early and Late Binding were demonstrated, and reasons were provided for preferring Late Binding for most cases.

Export an Excel Chart to Word shows a similar approaches for exporting our Excel chart to Word.

Follow-up articles will cover exporting other content (i.e., worksheet ranges (“tables”)) to PowerPoint or Word, and exporting multiple charts in one procedure, which will really reduce your daily tedium. These articles will also describe some of the code errors you may encounter and suggest workarounds, many gleaned through hours of mind-numbing trial-and-error.

PowerPoint

Export an Excel Chart to PowerPoint