Connect Two XY Series with Arrows

The Issue

If you have XY data from two different times, or under two different sets of conditions, you can connect points of one series (one time or condition) to another (another time or condition), to help show how the data evolves. For example, here are two series of data that I might want to connect together.

Series A and Series B to be connected by line segments

Several years ago I wrote Connect Two XY Series, which showed how to connect the points of one series to the corresponding points of another series. This approach used a third series with data from Series A and Series B, interwoven with blank cells.

Series A and Series B connected by line segments

I followed this up with Connect Two XY Series with Arrows (VBA), which showed how to connect the two series with arrows. In Excel 2003 and earlier, you had to use VBA to draw the arrows, and this article provided the VBA (and it’s reproduced below).

Series A and Series B connected by arrows, using VBA

This works nicely enough, but the arrows are not tied to the points, but only to the original positions of the points within the chart. So if you change the axes in the chart …

Series A and Series B no longer connected by arrows

… or change the size or shape of the chart …

Series A and Series B no longer connected by arrows

… the arrows no longer match up to the points.

You could always delete the misaligned arrows and rerun the VBA procedure, but what a pain.

I hinted in this second article that you could connect the points with arrows in Excel 2007, without using VBA but instead by formatting the third series that connected the first two series. But I never explained how. This article fills the gap.

Connect Two Series with Arrows, Without VBA

You need to keep the original Series A and B data in place, so you can still easily plot the original points. You need to get data into a new range, interweaving Series A with Series B.

Set Up The Data – Approach 1

To get the data into this alternating arrangement, start by pasting the data from Series A into a new range, then paste the data from Series B below it. Enter a set of index numbers into a column adjacent to this combined data, from 1 to the number of points in Series A, and from 1 to the number of points in Series B (which should be the same number of points). This is the first data block below.

Select the data, and sort by the column of digits you just entered. The data now has alternating Series A and B data (below right).

Sorted Data for Series A and B

Select the data and create a chart, or add it as a new series to the chart of Series A and Series B.

Series A and Series B connected with lines, but needing gaps at alternating segments

Hmmm, that’s almost what I want. But there are too many line segments. We need to eliminate half of them, shown as dashed lines below.

Series A and Series B connected with lines, showing where gaps are needed

We could format alternating line segments to be drawn with no line, but that’s mighty tedious after the first or second segment. Speaking from experience, because I had to format the dashed lines in the chart above, segment by segment.

As I’ve written about in Mind the Gap – Charting Empty Cells, the way we get a gap between points in a chart is to have a gap in the data, that is, blank cells between the points.

So let’s alter our protocol above, so that we start with the Series A data, then the Series B data below that, then an equal number of blank rows at the bottom, and include our index column (below left).

Sort by the index column, to get Series A and B data pairs, separated by blanks (below right).

Sorted Data for Series A and B and blanks

Now when we plot this data, we connect appropriate pairs of points, with gaps between points we do not want to connect.

Series A and Series B connected with lines, with gaps

Set Up The Data – Approach 2

Above we produced our data range by copying the data into a new range, and sorting the data appropriately. We could instead have stretched out the data first, then copied and pasted using the Skip Blanks option.

Here’s a copy of the Series A and B data, with two rows inserted between each point.

Series A and B data, expanded with blank rows

Copy the expanded Series A data, select the first cell of a new range, …

Series A data copied

… then paste.

Series A data pasted

Now copy the Series B data, select the cell below the first Series A point, …

Series B data copied

… and use Paste Special, and check the Skip Blanks option near the bottom of the dialog. Skip Blanks prevents blanks in the copied range from being pasted over data in the target range, so the Series A data is preserved.

Series B data pasted, skipping blanks to preserve Series A data

Now the data is ready to plot.

Make the Chart

Select the Series A data (shaded blue below), and create an XY Scatter chart. Then copy the Series B data (shaded orange), select the chart, and use Paste Special to add the data as a new series, with series in columns, Y values in the first column, series names in the first row. The resulting chart is shown below right.

Series A and Series B, to be connected with arrows

Now copy the A-B Interwoven data (below left), select the chart, and again use Paste Special to add this data as a new series, with series in columns, Y values in the first column, series names in the first row, to produce the chart shown below right. (You may have to format the new series to show lines and no markers)

Series A and Series B connected with line segments

Now you need to format the line segments of the new series. Select the series and press Ctrl+1 (numeral one) to open the Format Data Series task pane or dialog. Click the End Arrow Type dropdown, and select the desired arrow type. The options include three arrowheads, a circle, a diamond, and a plain line.

Format Data Series Lines - End Arrow Type

Now click the End Arrow Size dropdown, and choose an appropriate size for the arrowhead. There are combinations of three arrowhead lengths and widths.

Format Data Series Lines - End Arrow Size

Now the line segments have been transformed into arrows.

Series A and Series B connected with arrows

Unlike the VBA approach of the old tutorial, the arrows are tied to the points in Series A and Series B. So if you change the axes in the chart …

Series A and Series B still connected by arrows

… or change the size or shape of the chart …

Series A and Series B still connected by arrows

… the arrows still match up to the points.

The VBA

You don’t need to use VBA to connect points with arrows, but it is a good sample application of VBA to your charts. Just so you don’t have to go back to the earlier post, here is the VBA Procedure that connects the points with line segments with arrowheads.

Sub ConnectTwoXYSeriesWithArrows()
  Dim myCht As Chart
  Dim mySrs1 As Series
  Dim mySrs2 As Series
  Dim nPts As Long, iPts As Long
  Dim myBuilder As FreeformBuilder
  Dim myShape As Shape
  Dim iShp As Long
  Dim Xnode1 As Double, Ynode1 As Double
  Dim Xnode2 As Double, Ynode2 As Double
  Dim Xmin As Double, Xmax As Double
  Dim Ymin As Double, Ymax As Double
  Dim Xleft As Double, Ytop As Double
  Dim Xwidth As Double, Yheight As Double

  ' a chart with at least two series must be selected
  If ActiveChart Is Nothing Then
    GoTo ExitSub
  End If
  If ActiveChart.SeriesCollection.Count < 2 Then
    GoTo ExitSub
  End If

  Set myCht = ActiveChart
  Set mySrs1 = myCht.SeriesCollection(1)
  Set mySrs2 = myCht.SeriesCollection(2)
  nPts = mySrs1.Points.Count

  ' two series must have matching numbers of points
  If mySrs2.Points.Count <> nPts Then
    GoTo ExitSub
  End If

  ' remove any old connecting arrows
  For iShp = myCht.Shapes.Count To 1 Step -1
    If Left(myCht.Shapes(iShp).Name, 12) = "ArrowSegment" Then
      myCht.Shapes(iShp).Delete
    End If
  Next

  Xleft = myCht.PlotArea.InsideLeft
  Xwidth = myCht.PlotArea.InsideWidth
  Ytop = myCht.PlotArea.InsideTop
  Yheight = myCht.PlotArea.InsideHeight
  Xmin = myCht.Axes(xlCategory).MinimumScale
  Xmax = myCht.Axes(xlCategory).MaximumScale
  Ymin = myCht.Axes(xlValue).MinimumScale
  Ymax = myCht.Axes(xlValue).MaximumScale

  For iPts = 1 To nPts
    ' first point
    Xnode1 = Xleft + (mySrs1.XValues(iPts) - Xmin) * Xwidth / (Xmax - Xmin)
    Ynode1 = Ytop + (Ymax - mySrs1.Values(iPts)) * Yheight / (Ymax - Ymin)

    ' second point
    Xnode2 = Xleft + (mySrs2.XValues(iPts) - Xmin) * Xwidth / (Xmax - Xmin)
    Ynode2 = Ytop + (Ymax - mySrs2.Values(iPts)) * Yheight / (Ymax - Ymin)

    ' draw connecting line
    Set myShape = myCht.Shapes.AddLine(Xnode1, Ynode1, Xnode2, Ynode2)

    ' name and format shape as arrowhead
    With myShape
      .Name = "ArrowSegment" & CStr(iPts)
      With .Line
        ' USE YOUR FAVORITE FORMATS HERE
        .ForeColor.ObjectThemeColor = msoThemeAccent3
        .ForeColor.Brightness = -0.25
        .Weight = 1.5
        .EndArrowheadLength = msoArrowheadLong
        .EndArrowheadWidth = msoArrowheadWidthMedium
        .EndArrowheadStyle = msoArrowheadTriangle
      End With
    End With

  Next

ExitSub:
 
End Sub

 

Peltier Tech Charts for Excel

Export an Excel Chart to Word

You can use VBA to streamline a lot of your day-to-day practices, eliminate boring tasks, and reduce tedium. On this blog I recently showed how to Export an Excel Chart to PowerPoint. In this article I will show how to Export an Excel Chart to Word. I’ll follow up soon with more intricate routines that accomplish more chart exporting tasks, for both PowerPoint and Word.

There are a number of things you need to understand:

  • Excel’s Object Model
  • Word’s Object Model
  • Early and Late Binding, References, Libraries

We can learn all about Excel’s and Word’s object model by recording macros and examining the code (unfortunately PowerPoint has no macro recorder), as well as using tools like IntelliSense and the Object Browser in the VB Editor. And we have Google at our fingertips. Search on “Excel VBA” and the particular keyword that has you stumped, or type the exact error message into the search box.

We can learn about another application’s object model through VB Editor tools. Word has Charts, Shapes, Ranges, and Tables like Excel does; Word’s charts and shapes behave much like Excel’s, but its ranges and tables are much different. And Word has Documents instead of Workbooks and Worksheets.

I’ve already discussed Early and Late Binding in Export an Excel Chart to PowerPoint: Early and Late Binding. Please refer to that post for more information.

The Example Project

Here is a simple data set and chart in Excel.

Data and Chart in Excel

Here is a blank document in Word.

Blank Word Document

We’re going to export that chart from Excel into the cursor position in that Word document.

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 (you youngsters probably don’t remember WinAmp) 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 already been checked, and just before taking this screenshot I checked the Microsoft Word 16.0 Object Library. If a library is 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 and Word libraries 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 Word; it’s a little easier than the corresponding code for PowerPoint. The code is organized like this:

  • Several object variables have been declared at the top of the procedure.
    • In Early Binding, these objects are declared as Word 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 Word.
      • If Word is not running, we start it up.
    • We look for the active document.
      • If there is no active document, we create a new blank document.
    • We define the Word range at the cursor.
  • We copy the active chart.
  • We paste the chart as a shape object in Word (it’s still a real chart).

Early Binding – The VBA Code

Option Explicit

Sub SimpleActiveChartToWord_EarlyBinding()
  Dim wdApp As Word.Application
  Dim wdDoc As Word.Document
  Dim wdRng As Word.Range
  
  ' bail out if no active chart
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again!", vbExclamation, "Export Chart To Word"
    Exit Sub
  End If
  
  ' get Word application if it's running
  On Error Resume Next
  Set wdApp = GetObject(, "Word.Application")
  On Error Resume Next
  
  If wdApp Is Nothing Then
    ' word not running so start it and create document
    Set wdApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.Documents.Add
  Else
    If wdApp.Documents.Count > 0 Then
      ' get active document
      Set wdDoc = wdApp.ActiveDocument
    Else
      ' no active document so create one
      Set wdDoc = wdApp.Documents.Add
    End If
  End If
  
  ' get cursor location
  Set wdRng = wdDoc.ActiveWindow.Selection.Range
  
  ' copy chart
  ActiveChart.ChartArea.Copy
  
  ' paste chart
  wdRng.Paste
  
End Sub

Late Binding – The VBA Code

Option Explicit

Sub SimpleActiveChartToWord_EarlyBinding()
  Dim wdApp As Object  ' Word.Application
  Dim wdDoc As Object  ' Word.Document
  Dim wdRng As Object  ' Word.Range
  
  ' bail out if no active chart
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again!", vbExclamation, "Export Chart To Word"
    Exit Sub
  End If
  
  ' get Word application if it's running
  On Error Resume Next
  Set wdApp = GetObject(, "Word.Application")
  On Error Resume Next
  
  If wdApp Is Nothing Then
    ' word not running so start it and create document
    Set wdApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.Documents.Add
  Else
    If wdApp.Documents.Count > 0 Then
      ' get active document
      Set wdDoc = wdApp.ActiveDocument
    Else
      ' no active document so create one
      Set wdDoc = wdApp.Documents.Add
    End If
  End If
  
  ' get cursor location
  Set wdRng = wdDoc.ActiveWindow.Selection.Range
  
  ' copy chart
  ActiveChart.ChartArea.Copy
  
  ' paste chart
  wdRng.Paste
  
End Sub

Bits and Pieces

The Objects

We need to use the Word Application (we use the Excel application all the time in Excel VBA, but usually get away with ignoring it), plus a Word document, and a Word range. The chart is pasted as a Shape. I’ve shown the Early Binding Word object types below; in Late Binding, these are all plain old Objects. Notice that I keep the Early Binding definitions of these objects in comments, to help when I have to figure out the code later; notice also that the variable names are fairly self-explanatory: wdApp = Word Application, etc.

  Dim wdApp As Word.Application
  Dim wdDoc As Word.Document
  Dim wdRng As Word.Range

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, then try again.

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

Select a Chart, Dummy!

Alternatively, we could look for charts on the active worksheet. If there’s only one chart, we can use that one. Or if there are several charts, we could pick the first one, or the one that is closest to the top left corner of the sheet.

In an upcoming post I’ll show how to export all the charts on the active sheet, or just all charts the user has selected.

Get the Word Application

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

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

Word supports multiple running instances at a time. If we were constructing an entire report from Word templates and Excel content, we could ignore any running instance of Word, and create a new instance just for our report. But it’s often more useful to paste content in an active document, for example, if you are writing a report in Word side by side with your data in Excel.

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

Get the Active Document in Word

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

wdDoc.ActiveWindow.Selection.Range: locations in Word are ranges. A range can be a single point, like the cursor insertion point, or a range can be a longer bit of text, like a word, a sentence, or a book chapter. The selection in the active window is like the selection in Excel. It can be a single point (a single cell) or an extended amount of text (collection of cells). This code defines the selection as the place where we will paste out chart.

Again, we should check that we did successfully end up with a Word document and a Word range, 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 wdRng.Paste to paste the chart as a shape (a chart’s ChartObject is the Excel Shape that contains the chart), at the location of the range in Word.

The Result

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

Word Document Containing Our Chart

Summary

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

The approaches for exporting an Excel chart to PowerPoint are similar, and were spelled out in Export an Excel Chart to PowerPoint.

Follow-up articles will cover exporting other content (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 common code errors you may encounter and suggest workarounds.

 

Peltier Tech Charts for Excel

Export an Excel Chart to PowerPoint

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 SyntaxIntelliSense ToolTip Showing Relevant Syntax

IntelliSense DropDown Listing Appropriate OptionsIntelliSense 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.

 

Peltier Tech Charts for Excel

Chart a Wide Range of Values

How do you chart a wide range of values? There are numerous solutions to this, each with pros and cons. I’m showing these in Excel charts, of course, but they apply no matter what charting package you may be using.

It’s easy enough to think of an example. The following two charts show monthly sales data. One month had very high sales, while the rest of the months had low sales. (I’ll be illustrating the concepts in this article using both line and column charts, because each has special considerations.

Chart A Wide Range Of Values

In both charts, the January sales value stands proud, while the other months are hiding in the weeds along the bottom of the chart.

Use a Logarithmic Scale

The first approach to chart a wide range of values was suggested in Logarithmic Scale In An Excel Chart, a tutorial on the MyExcelOnline Excel Blog. The My Excel Online web site is run by my colleague John Michaloudis, and it features lots of great tutorials, podcasts, free training, and paid courses. John’s point was well taken, but it was incomplete. It was a short and sweet review of the technique, but it left out the thought process that should accompany any charting effort.

It’s easy enough to apply a logarithmic scale to a numerical axis in an Excel chart. Select the axis, then press Ctrl+1 to open the Format Axis task pane (or dialog in Excel 2010 and earlier). In the same view that allows formatting of scale parameters like minimum and maximum, you should find a checkbox that says Logarithmic Scale. Check the box, and the scale is adjusted. You can also change the base of the log scale, but it’s usually best to keep it at 10.

Here are my two charts with log scales. The data I’m using here is very similar to the data in the MyExcelOnline post cited above.

Logarithmic Scales

Your first thought might be satisfaction that the data are now all shown with a similar magnitude. That’s wrong, of course, because they aren’t of similar magnitude. But that’s secondary, because we are really only comparing extreme values with very little in between.

The other point about the charts above is the scale limits. Excel likes to use zero as one endpoint of an axis, and 1 corresponds to zero on a log scale. However, this choice of scale minimum means that the bottom two-thirds of the chart is blank, all of the data occurs in the top.

We can reduce the white space by entering a better value for axis minimum.

Adjusted Logarithmic Scales

There is still a problem, especially with the bar chart. In a bar chart, our precognitive sense of the values is based on the lengths of the bars, and it’s hard to overcome that. When I look at the bar chart above, the short bars all look about half as tall as the long ones, so I get the mistaken impression that the smaller values are around half of the large value. (The first logarithmic column chart is even worse, since the shorter bars are 80% as tall as the long one.) The actual ratio of values is less than one-tenth.

The log scale also tends to wash out the variability in the shorter values. In the charts below, I show the previous log scale charts, and beside them a linear scale chart with the scale blown up so the mean of the linear scale and log scale are at about the same height. Of course the large value is way off scale, which I’ve tried to indicate by fading the top of the visible indication of the large value.

The variability in the small values is shown best using the linear scale.

Linear vs. Logarithmic Scales

I should mention a couple more points about logarithmic scales.

First, as should be clear above, there is no place for log scales in bar or column charts. Bar and column charts need to include zero in the axis scale, but you can’t include zero on a log scale, since log(0) is negative infinity.

Second, most audiences will not be able to properly appreciate a logarithmic scale. Sure, engineers, scientists, and quants may have a strong enough numerical sense to interpret them accurately. But general audiences, and even smart managers and executives, are likely to be misled by such numerical transformations. It’s best to stick to the linear scale that most people are comfortable with.

Show Part of the Data

The charts used above aren’t bad, showing that linear scale charts preserve variation in the data better than logarithmic charts. Maybe we don’t need to actually show the extreme values on the axis scale, if we just indicate that they’re way off scale.

I can adjust the scale a bit more, to center the smaller values in the chart, keeping the scale minimum at zero, and fading the top of the largest value. The large value is so much bigger anyway, maybe we don’t need to actually show it to indicate that it is much larger than the others. This fading is a bit tricky, but it’s important to indicate that the data point isn’t just at the top of the chart, but instead extends far beyond.

Linear Scales Better Than Logarithmic Scales

 

Break the Axis Scale

A common approach to chart a wide range of values is to break the axis, plotting small numbers below the break and large numbers above the break. An advantage here is that it generally uses a linear scale. A disadvantage is that it distorts data, and doesn’t really give a sense for the differences in value on either side of the break.

Here are the two original charts, with a break in the vertical axis scale.

Broken Axis Scales

Bravo, you got in all the data points. But despite the visual cues that the axis has broken, there is still a strong tendency to mentally interpolate the values: those short bars still look half as tall as the large one, and the faded center and gap in the axis tell my conscious mind but not my precognitive mind that my first impression is wrong.

Sure, it’s easy enough to read the value off the axis scale, corresponding to the data point. But if you have to do that much work, what’s the point of a chart? You might as well just read the data from the cells in the worksheet.

Another drawback to breaking an axis is that it’s hard. You need to hide the real axis, construct two parts of a fake axis with a combination chart and data labels, and change at least some of the data you’re plotting. Nobody understands how to make these charts, and nobody understands the output anyway. So why bother.

The charts in the Show Part of the Data section are more effective.

Use Multiple Charts, or Make a Panel Chart

A lot of people are obsessed with getting all of their data into exactly one chart. Sometimes this is fine, but as we’ve seen above, sometimes charts with all of the data are not very easy to interpret without distorting the relationships within the data.

What’s wrong if we use two charts? We can show the whole range of data while highlighting the larger values (see the first chart of this article), then add the chart from the Show Part of the Data section which highlights the smaller values.

Two Charts to Show The Data

There are some redundant chart elements, so let’s hide the category labels in the top charts and the titles in the bottom charts.

Two Charts Beat as One

From here it’s not a big stretch to combine both charts. Plot the original data twice, once each on the primary and secondary axes, do some heavy axis formatting, and voila. Here are panel charts, where one panel shows the full extent of the data, and the other zooms in on the smaller data.

Panel Charts

In my tutorial Broken Y Axis in an Excel Chart, I explain some of the shortcomings of a broken axis scale, and I give step-by-step instructions for creating exactly this kind of panel chart. Panel charts are a little more complicated to build and maintain, but using a single chart helps with alignment and other formatting.

Panel charts are among my favorite ways to show this kind of data. But we aren’t done yet with alternatives.

Pareto Charts

A Pareto chart is a combination chart that combines a column chart (sorted from largest value to smallest), like the one that led off this article, with a line chart showing the cumulative total.

Here are two versions of a Pareto chart for this data. The Pareto one on the left is sorted by value. The one on the right is sorted by month, so the cumulative line doubles as the cumulative YTD sales.

Pareto Charts

Below is a combination of Pareto chart and waterfall chart, which I call a “Floating Pareto” chart. The bars show the incremental values and the increasing additive value. The one on the left is sorted by value, while the one on the right is sorted by month, again showing YTD sales.

Floating Pareto Charts

First and only shameless plug of the entire article: the Advanced Edition of Peltier Tech Charts for Excel 3.0 includes Pareto and Floating Pareto charts. Check it out.

Think Further Out of the Box

You always have to keep in mind why you need to plot this data. If you want to say that our sales team kicked ass in January, then the sales director left for a competitor and took with him his account book and his entire staff, you can even get away with a pie chart.

A Pie Chart?

Sure, you’re thinking, “A PIE chart? Wut? Has he lost his mind? Pie charts suck.” They’re not good at showing numerical data, and blah blah, yada yada, etc etc etc.

Well, I’ve softened a bit in my stance on pie charts, in part because of some actual research into their effectiveness by Robert Kosara of EagarEyes and Tableau (go read An Illustrated Tour of the Pie Chart Study Results, and read the papers that post links to). Seriously, the chart above practically shouts, “What the hell changed between January 31 and February 1?” There really seems little point in examining the month to month variation from February on.

If you want something slightly more quantitative, you can make yourself a nice little stacked column chart. The chart below clearly says that January sales were enormous, more than the rest of the year combined.

A Pie Chart?

Why do you want to chart a wide range of values?

As with any charting exercise, you need to ask some questions. What am I trying to learn from this chart? Who am I making this chart for? What message am I trying to give them? What’s on Netflix tonight?

If your purpose is a quick overview, then something like the Pie Chart or Stacked Column Chart might be all you need. If you’re trying to show something in more detail, the Pareto Chart, the Panel Chart, or a chart from the Show Part of the Data section may be more appropriate. If it’s detailed engineering data or scientific model predictions and your audience is highly literate in mathematics, then consider Logarithmic Scale charts.

 

Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0