Another approach to plotting gaps in Excel charts

Constable Henry: Just a moment, sir. I know everyone in this neighborhood, but I’ve never seen your face before. Can you account for yourself?

Freddy: Yes, I am Dr. Frederick Fronkonsteen, newly arrived from America.

by The FrankensTeam

This guest post is from the E90E50 FrankensTeam, i.e., Roberto Mensa, Kris Szabó, and Gábor Madács. The team has done a great deal of work with Excel and its graphics, and they’ve shown how many types of “advanced” graphics can be accomplished right in Excel. Such FrankenCharts include heatmaps, treemaps, network diagrams, and a number of other creative displays. Oh, and they’re as crazy about the movie Young Frankenstein as they are about Excel.

While I was reading Jon’s work using VBA Approaches to Plotting Gaps in Excel Charts I started to think, because I felt there had to be another solution which was waiting to be discovered…

Jon provided two different VBA routines as a solution for the problem: one changes the chart series formatting, the other changes the underlying data. As he clarifies at the very beginning, the disadvantage of using VBA routine is that it must be re-run every time the data changes.

Some time ago I realized that it is possible to use union of ranges in chart series. You can do it by using the union reference operator in the SERIES formula of the chart or via named formula. (The union reference operator is the same as the formula separator character, which is a comma in US-English versions of Excel.) In the project where it came up it was not particularly interesting, so I just stored this fact in my mind.

Now, a few months later, thinking about the gap problem… Eureka!

The union of separate ranges could be a solution, especially if we can build it up using the UDF within a named formula!

I was very excited by this possibility, so I quickly put together some code and shared the idea with Jon, who suggested some modifications in the UDF. Then we did a lot of tests with Kris and Gábor and corrected the problems showed up while we tested the solution in different Excel versions.

Let us explain how it could work.

Below is a simple line chart plotting data from column B: the #N/A error value is not plotted (no marker) but the line is continuous, while the string is plotted as zero.

The series formula of the chart looks like:

=SERIES("ref to rng",,line_chart!$B$3:$B$13,1)

Where y values come from a continuous range: $B$3:$B$13

The idea

To plot gaps on the chart, you will need empty cells in the range used for y values. The union of ranges idea is very simple: we use the cells containing the number values from the original range (column B) but use empty cells from another column (column C) instead of the error or text values.

You can easily select the non-continuous range by mouse while holding the Ctrl key down.

On the below picture you can see how it looks from the Edit Series dialogue box.

And this is how the SERIES formula shows the union:

=SERIES("ref to rng",,

Note the y range is now surrounded by brackets, since it is composed of multiple areas.

You can easily define a named formula for the above range:

Name: y_rng_union
Refers to: =$B$3:$B$5,$C$6,$B$7:$B$9,$C$10,$B$11:$B$13

And use it as Series values:

Formula solution

To make a solution a bit more dynamic, instead of using static ranges, you can put together a dynamic solution with IF formulas. You have to check the cells one by one, and if the cell has a value to be plotted, then use the reference of the cell, else use an empty cell from another column. Then you separate the IF formulas by comma (or semicolon) to create the union of unique cells.

This way the chart will automatically be updated if the data changes, but if you have more data to be plotted, you will have to update the named formula manually.

For example you can put a similar formula to the name, supposing that column C is empty, so use the cells of this column:

Name: y_rng_union
Refers to: =IF(ISNUMBER($B$3),$B$3,$C$3), IF(ISNUMBER($B$4),$B$4,$C$4), IF(ISNUMBER($B$5),$B$5,$C$5), ..., IF(ISNUMBER($B$13),$B$13,$C$13)

Or you can use only one empty cell (for example C3) instead of the cells of column C:

Name: y_rng_union
Refers to: =IF(ISNUMBER($B$3),$B$3,$C$3), IF(ISNUMBER($B$4),$B$4,$C$3), IF(ISNUMBER($B$5),$B$5,$C$3), ..., IF(ISNUMBER($B$13),$B$13,$C$3)

More generally, you can use a name (for example x) referring to an empty cell:

Name: y_rng_union
Refers to: =IF(ISNUMBER($B$3),$B$3,x), IF(ISNUMBER($B$4),$B$4,x), IF(ISNUMBER($B$5),$B$5,x), ..., IF(ISNUMBER($B$13),$B$13,x)

If you are interested in the above formula solution, you may also find useful a help formula in an Excel range to build up the above (very long!) formula.

The logic is simple: A copy-down formula will concatenate together all the necessary IF(ISNUMBER(...) parts.

(Please note, if you use non-english versions of Excel, you will need to replace IF and ISNUMBER with your local language formula names. Also, you will have to replace the separator string (comma) if your version uses semicolons.)

For example, supposing that the header of your data is in B2, and data starts in B3, write this formula in cell M3 and copy down:


In our example we have to copy down this formula to cell M13 (since data ends in row 13). The string we get in M13 is exactly the formula we need to use in y_rng_union, so we copy M13 cell and paste as value. Now the formula could be copy-pasted as a reference into the name:

For better understanding, you can check these solutions in the without-udf example file.

Obviously the formula solution is not really effective and has limitations, but it could be an alternative for those who can not use VBA.

General solution with UDF-based named formula

For a more convenient resolution we will need some VBA code: a user defined function will build up the union-range and provide it to the chart, nested in a named formula. So if your data changes, the union range will change accordingly and if your data range grows, the chart will also capture new data.

Important to highlight that it will happen automatically, together with calculation, without any user interaction!

Here is the UDF code:

Function serie_with_empty(rDataIn As Range, _
        Optional cOffset As Long = 1)

    Dim rCellIn As Range
    Dim rCellOut As Range
    Dim rDataOut As Range


    For Each rCellIn In rDataIn.Cells
        If Not IsNumeric(rCellIn.Value) Then
            Set rCellOut = rCellIn.Offset(, cOffset)
            Set rCellOut = rCellIn
        End If

        If rDataOut Is Nothing Then
            Set rDataOut = rCellOut
            Set rDataOut = Union(rDataOut, rCellOut)
        End If

    Set serie_with_empty = rDataOut

End Function

The procedure loops through the data range (first argument) and checks for non-numeric values. If the value is numeric (so to be plotted) then the cell itself is added to the union-range. If the value is not numeric, then another cell determined by offset will be added to the union. At the end, the function returns a range object.

This range object could not be used directly on the chart, it must be wrapped in a named formula.

To avoid errors when UDF could not be run (because macros are disabled) we suggest using an error check in the named formula:

Name: y_rng_union
Refers to: =IF(TYPE(serie_with_empty(rng,0))=16+NOW()*0 ,rng, serie_with_empty(rng,1) )

Where rng is the original data range, serie_with_empty(rng,1) is the UDF-calculated union-range.

The second argument of the UDF is the column offset parameter. Above we use 1 supposing that the column next to the original data is empty. You have to ensure a sufficient number of cells in this column. The offset parameter is optional with 1 as default value.

You can find more details about the logical test part of the IF formula under section Further details.

Using the above defined name on the chart, the result is correct with gaps, and refreshes as your data or underlying range changes.

The method could be used for xy scatter charts too. For this chart type only the y values need to have empty cells for gaps, so no need to change the original x values range. As an example here is a possible SERIES formula of an xy chart:

=SERIES("ref to y_rng_union" , line_chart!$D$3:$D$13 ,
   Serie_with_empty_udf_xy.xlsm!y_rng_union , 1)

with the arguments:

x values (normal range): line_chart!$D$3:$D$13

y values (UDF calculations): Serie_with_empty_udf_xy.xlsm!y_rng_union

where line_chart is the name of the worksheet containing the x values, and Serie_with_empty_udf_xy.xlsm is the name of the workbook.

You need to reference the name y_rng_union to the worksheet or workbook. I usually start my chart with a regular range in the formula, so I leave the worksheet name and exclamation point intact, and replace the address with the named formula, like line_chart!y_rng_union. If necessary, Excel will change the worksheet name to the workbook name.

Below you can see what our example xy chart looks like, illustrated with ranges as plotted.

We created two example files, one for line chart, another for xy chart. We used random data, so after pushing F9 you can see how data, ranges and chart changes.

Further details

We need to test if the UDF was run or not.

In practice, macros are usually disabled in the Trust center under Excel Options. When you open the file, Excel will try to refresh the chart, but the UDF code will be unable to run, so the range will not be correctly built. The chart will raise an “Invalid reference” error message. Unfortunately, after enabling the macros, the chart will not be refreshed, because it remembers that the reference is invalid.

If the UDF can not run it results a #NAME error, which is number 16 in the TYPE formula. In this case the IF formula will give the original range, so the chart will get a valid range – although the line will not have gaps. To see the correct gaps, you need to enable macros and trigger calculation (so push F9). We use a volatile formula in the name ( NOW()*0 ) this is necessary for the chart to be updated after calculation. (The chart ignores the Application.Volatile in the UDF code!)

Problems with Cell.Text property

The base concept of gaps on the chart is to plot only numbers. In the UDF,

If Not IsNumeric(rCellIn.Value)

tests the cell’s .Value property. We were intending to provide another possibility for the users to mark unplottable data using formatting. For example they could set a custom formatting to hide 0 values if these should not be plotted. In VBA Cell.Text property could be used to get cell’s text as displayed on the screen, but using .Text property caused Excel crashes in case of xy charts. In case of line charts we have not observed crashes, but the SERIES formula was not visible on the formula bar, so we stopped using the .Text property.

About the authors:

Roberto Mensa, Kris Szabó, and Gábor Madács are three Excel-enthusiastics from Europe, thrilled to push the boundaries of Excel and do what seems to be impossible. You can find more about them on their team-site.

Peltier Tech Chart Utility

Guest Post: The ‘Fiscal Cliff’ Explained in Charts: A Critical Review

This is a guest post from Jon Schwabish, an economist and data visualization creator. You can reach him at or by following him on Twitter @jschwabish.

The ‘Fiscal Cliff’ Gallery

For a few weeks now, The Washington Post has posted a gallery of charts to help explain what is meant by the ‘Fiscal Cliff’, a series of tax increases and spending cuts that is scheduled to occur at the beginning of next year. The full slide show can be found at The ‘fiscal cliff’ explained in charts.

There are 7 charts in the collection, and although most of them are simple bar charts, from a data visualization perspective, they fail in various dimensions.

So I decided to issue myself a personal challenge: Redesign all 7 charts following 5 basic rules.

Fiscal Cliff Chart Redesign Rules

  1. Use the actual data. For all but one of the charts the data were available on the chart itself or from the listed source. For the sixth slide—“Sequester cuts to the FEMA budget”—the data were not readily available, so I eyeballed the values.
  2. Do not overly modify text or labels. Even though some of the labels were shorthand or incomplete, the objective of the challenge was to improve the visualizations, not correct syntax.
  3. Use a single color scheme and font. The gallery is a collection of charts from multiple sources—the Tax Policy Center, the Committee for a Responsible Federal Budget, and Post staff—so the color schemes and fonts vary. I chose a single, blue color scheme and used the ‘Corbel’ font throughout.
  4. Only use Microsoft Excel. Because many people use Excel extensively, I wanted to show that creating quality data visualization does not require complex software or knowledge of programming languages.
  5. Apply good data visualization principles. My redesigns incorporate simple strategies:
    1. keep data and labels close together;
    2. deemphasize tick marks, gridlines, axes (i.e., ‘chartjunk’); and where possible, include data directly on the chart.

The Charts: Before and After

Chart 1: Fiscal Cliff Components (Tax Policy Center or TPC).

Fiscal Cliff Components

Fiscal Cliff Components

This was probably the most difficult chart to redesign. On the one hand, you might want to focus on the increase in the average federal tax rate by income quintile (the height of the bars). On the other hand, you might want to focus on the components of the fiscal cliff for each group (the portions of each bar). Combining those, however, clutters the attempt to visualize both goals. For my redesign, I chose to focus on the distributional analysis and sacrifice the imagery of the rising average federal tax rate as incomes rise. The second chart in this set—and the first in the original TPC document—tells me that basic story more clearly anyways. An alternative approach would be to use several small charts (‘small multiples’) and stack them together in some way, but I chose to stick to the spirit of the challenge and only redesign the single chart. Plus, had TPC made a series of small charts, it’s not clear to me that The Washington Post would have picked it up.

As for creating this in Excel, it’s a simple stacked column chart where the intervening series are set to an empty fill. The values of the empty fill series are set so that the heights of each section are equal. The labels on the left side are inserted using separate series added as scatterplots.

Chart 2: Average Federal Tax Rate by Cash Income Percentile, 2013 (Tax Policy Center).

Average Federal Tax Rate by Cash Income Percentile

Average Federal Tax Rate by Cash Income Percentile

I didn’t like the yellow-blue combination here because it emphasized the effect of the Fiscal Cliff on the baseline, which may have been the intention, but I thought it was too much. I also moved the labels right onto the chart and lightened and reduced the number of gridlines.

Chart 3: Size Components of the “Fiscal Cliff” (The Washington Post, based on data from the Congressional Budget Office).

Size Components of the Fiscal Cliff

Size Components of the Fiscal Cliff

This simple redesign including lightening the gridlines, left-justifying the title, and adding the data to the chart. The original chart also appeared to have some sort of vertical gradient, which I found unnecessary.

Chart 4: Marginal Tax Rate Increase under Fiscal Cliff by Income (The Washington Post, based on data from the TPC).

Marginal Tax Rate Increase by Income

Marginal Tax Rate Increase by Income

Similar to chart #3, I lightened the gridlines, deleted the gradient, and left-justified the title. I also simplified the y-axis labels by getting rid of the dollar signs and the thousands units and deleted the hundreds decimal points in the x-axis labels.

Chart 5: Economic Impact of Fiscal Cliff, Zandi Multipliers (Committee for a Responsible Federal Budget).

Economic Impact of Fiscal Cliff, Zandi Multipliers

Economic Impact of Fiscal Cliff, Zandi Multipliers

I lightened the gridlines and the heavy axis lines, moved the legend onto the graph, and deleted the outline of each column. (The outlines add three redundant elements to show the height of each column.) I also used a single blue color scheme; in the context of the Fiscal Cliff, which is inherently a policy issue, using blue and red invokes images of the two political parties, which is not really an issue in this graph. Importantly, I also sorted the data by the Budget Impact, which gives the graph both a more appealing look and a quantitative basis for ordering the groups.

Chart 6: Sequester Cuts to the FEMA Budget (in millions) (The Washington Post).

Sequester Cuts to the FEMA Budget

Sequester Cuts to the FEMA Budget

Simple changes: deleted the dollar signs; left-aligned the title; reduced size of “(in millions)” in the title; added data labels; and reduced the size of the x-axis labels.

Chart 7: Effect on Fiscal Cliff Components on Deficit and GDP (The Washington Post).

Effect on Fiscal Cliff Components on Deficit and GDP

Effect on Fiscal Cliff Components on Deficit and GDP

This chart probably resulted in the largest number of changes, but all of the decisions were so easy to make: sorted the data from smallest to largest; used shades of a single color scheme instead of the menagerie of original colors; moved the legend to the area between the columns; deleted the gridlines; left-justified the title; and put the Totals to the outside of the two columns instead of at the top, which looked terribly cluttered.

Peltier Tech Chart Utility

Excel, Free Rice and MMA

Today I’m happy to present a guest post by Dr. AnnMaria DeMars. AnnMaria is the intelligent and entertaining author of AnnMaria’s Blog about statistics, technology, and work and also of AnnMaria’s Blog on Judo, Business, and Life, which is about “achieving success in business, sports and academics [and parenting] without ever actually having grown up.”

“If all you have is a hammer, everything looks like a nail.”

Hard to believe Bernard Baruch died before computer graphics, because he hit the – um, nail – right on the head. Some days, it seems the world is comprised of people who fit into one of two categories …

  1. “Find Excel not sufficiently sophisticated for real statistics, don’t you agree?” (Said in the same tone as Thurston Howell III – look it up, youngsters!)
  2. Believe every problem can be solved by Excel.

What’s really interesting is that I fall into both of those categories, not just some of the time but almost every day. Let’s start with last week.  My darling daughter fought for the 135 lb world title on Saturday, and a few weeks ago, she started a free rice group. You can read a bit of the story at RondaMMA Free Rice.  In a nutshell (or should I say grain of rice), this is a wonderful site where you can answer questions and for every one correctly answered, 10 grains of rice are devoted to the world food program. Ronda sent free t-shirts, autographed pictures and other swag to fans who were part of her free rice group. Below is one of three Excel charts I did to track the progress at different points in the competition.

Her fans have donated over 20,000,000 grains of rice so far, and God love them for that, but it is safe to conjecture that the average mixed martial arts fan is not a doctoral student in statistics so my challenge was to come up with easy to follow graphics for tracking the results. Even though I am usually using SAS or SPSS all day, I selected Excel for this chart for a couple of reasons. First, the free rice site allows me to download the group data in a .csv file each day, making it easy for me to open in Excel. Second, it is blissfully easy to insert a picture in an Excel chart.

Rice grains in a rice grains chart

Should you have your own Excel free rice group you want to chart (or anything similar) here are the steps.

1. Create your data. In my case this meant having one column with the dates and a second column with the number donated as of that day.

A.  Each day, I downloaded the csv file that had three columns, a userid (A), a username (B) and number of grains donated (C).  I computed the number for that day by entering into a cell =SUM(C2:CN) where N was however many group members had donated as of that day. This gives me the data for one day.

B. In my master file, type the date, copy the sum from the day’s file and use Paste Special to paste the value only.

2. Format the cells. I went to Format, then Cells, then Number and formatted the cells to have zero decimal places and a comma to separate 1,000.

3. Make the chart: Select the Date and Rice Grains columns. Click Charts and select the first option, which is an area chart.

4. Double-click on your chart and the FORMAT DATA SERIES window shows up. It has an option I have never used because I am a “Serious Academic” (Thurston Howell III accent again) and I don’t work for USA Today and insert pictures in my chart. Until now, when I click on the PICTURE tab and choose insert picture.  I also click on the option to TILE PICTURE AS TEXTURE.

Format Data Series Dialog

Because it looks awfully plain with white rice and a white grid, I double-click on it again and choose FILL this time, changing the background color to a pale orange.

Format Chart Area Dialog

I click on the legend and delete it, because it really is superfluous, add a title, and I have the chart to post to track the contest.

One person asked, “It takes 3,500 grains of rice to fill one bowl. If you raise 1,000,000 grains it’s only fed less than 300 people. How much difference does that make?”

Ronda’s answer was, “If you’re one of those people, it makes a lot of difference to you.”

The Champ

As of now, the group has raised enough rice to give a meal to over 5,500 people who otherwise would have gone hungry.

You can see the fight at Ronda Rousey vs. Miesha Tate – caution: graphic violence.

Peltier Tech Chart Utility

Programming Excel 2007 and Excel 2010 AutoShapes with VBA (Guest Post)

Today I am pleased to present a guest post written by my colleague Nicholas Hebb. Nick appears online frequently, commenting on Excel-related blogs and corresponding in the various forums.

Programming Excel 2007 and Excel 2010 AutoShapes with VBA

By Nicholas Hebb

With the release of Office 2007, Microsoft rewrote the drawing tools from the ground up. On the plus side, this meant new shapes, new styles, and the addition of SmartArt. On the downside, they didn’t have time to incorporate AutoShape operations in their macro recorder prior to release. Thankfully, Excel 2010 has added macro support back, but like all macros the code generated is often bloated and relies heavily on the Selection object, which tends to hide the core objects in use. For the most part, the online help is good, but there is some information that doesn’t get explained in detail. This article attempts to provide a basic overview of working with AutoShapes using VBA and touch on some areas that are not covered extensively in the help documentation.


Two properties of the Shape object will be used in the code samples below – Shape.Type and Shape.AutoShapeType. Excel has a broad range of shape Types consisting not only of AutoShapes, but also connectors, lines, pictures, charts, comments, and many other graphical items. For AutoShapes, the AutoShapeType property lets you get/set the type of shape as shown in the gallery image below.

Excel 2010 Autoshapes Gallery

Knowing when to check the Shape.Type property versus the Shape.AutoShapeType is very useful. For example, if the AutoShapeType value is -2, then for all practical purposes the shape is not an AutoShape. If the value is greater than 1, then the shape is one of the types display in the Shapes gallery. The tricky part comes when the AutoShapeType is 1, which equals the AutoShape constant msoShapeRectangle. It could be a Rectangle AutoShape, but it could also be anything shaped like a rectangle, such as a text box, a comment, or even a picture. So if the AutoShapeType evaluates to 1, then you also need to check the Type property.

Callouts are another special type of shape that can cause confusion. They are discussed more in the Miscellaneous Issues section below.

Accessing a Shape Object

Each worksheet contains a Shapes collection consisting of Shape objects. Like other collections in VBA, the Shape object is accessed either via its name or index number, as in:




Or, using the For…Each syntax:

Dim shp as Shape
For Each shp in ActiveSheet.Shapes
  MsgBox shp.Name

Adding an AutoShape

The syntax for adding a shape is:

Worksheet.Shapes.AddShape(AutoShapeType, Left, Top, Width, Height)

The AutoShapeType is a constant that ranges from 1 to 137 for Excel 2003 and earlier versions. Excel 2007 added shapes 139 through 183. AutoShapeTypes 125-136 are special AutoShapes. The online help file states that they support mouse over and click events, but that only applies when they are used in PowerPoint presentations. You can use them in Excel but they don’t have any special properties.

To see what the AutoShapeType constant is for each AutoShape, you can copy and paste the following code into the Excel Visual Basic Editor and run it (or download the sample file and run the macro). Not all the AutoShapes are available in the Shapes gallery, so this will also give you a look at some of the hidden ones.

Sub CreateAutoshapes()
  Dim i As Integer
  Dim t As Integer
  Dim shp As Shape

  t = 10
  For i = 1 To 137
    Set shp = ActiveSheet.Shapes.AddShape(i, 100, t, 60, 60)
    shp.TextFrame.Characters.Text = i
    t = t + 70
  ' skip 138 - not supported
  If CInt(Application.Version) >= 12 Then
    For i = 139 To 183
      Set shp = ActiveSheet.Shapes.AddShape(i, 100, t, 60, 60)
      shp.TextFrame.Characters.Text = i
      t = t + 70
  End If
End Sub

The Left, Top, Width, and Height parameters of AddShape() are specified in points. The origin is the top left corner of the worksheet, with the Left and Top values increasing to the right and down, respectively. Dealing with points on a worksheet isn’t intuitive, so if you prefer you can add a shape to a given range address by using code like this:

Function AddShapeToRange(ShapeType As MsoAutoShapeType, sAddress As String) As Shape
  With ActiveSheet.Range(sAddress)
    Set AddShapeToRange = ActiveSheet.Shapes.AddShape(ShapeType, _
        .Left, .Top, .Width, .Height)
  End With
End Function

Adding Text to an AutoShape

The Shape object has both a TextFrame and TextFrame2 members. The TextFrame2 member was added in Excel 2007 and gives better control over the formatting of the text. Because it is not backward compatible, I would recommend using the TextFrame object, as shown in the following code.

Sub AddFormattedTextToShape(oShape As Shape, sText As String)
  If Len(sText) > 0 Then
    With oShape.TextFrame
      .Characters.Text = sText
      .Characters.Font.Name = "Garamond"
      .Characters.Font.Size = 12
      .HorizontalAlignment = xlHAlignCenter
      .VerticalAlignment = xlVAlignCenter
    End With
  End If
End Sub

Setting Border and Fill Styles

If you take advantage of the built-in styles for Excel 2007 and Excel 2010, setting the AutoShape formatting is ridiculously easy compared to Excel 2003 and previous versions. Excel 2007 introduced the ShapeStyle property with the 42 preset styles shown below.

The style numbers can be set using a simple line of code:

Shape.ShapeStyle = msoShapeStylePresetXX

Where Shape is the shape object and XX is the style number. The style numbers are shown in the image gallery in order from left to right, top to bottom. For example, the red button in the second row msoShapeStylePreset10).

Adding Connectors and Lines

Connectors and lines are different objects in Excel. Connectors are special lines  that “connect” to shapes, and if the shape is moved the connector stays connected and reroutes accordingly. Connectors cannot connect to other connectors, but they can connect to the end point of a line.

The syntax for adding a line is straightforward:

Worksheet.Shapes.AddLine(BeginX, BeginY, EndX, EndY)

…with all coordinates as Singles. Adding a connector is a bit more complex, since you typically want it to connect two shapes. The code below calculates the begin and end points, creates the connector, attaches the connector to the two shapes, then finally does a reroute to ensure the shortest path.

Function AddConnectorBetweenShapes(ConnectorType As MsoConnectorType, _
    oBeginShape As Shape, oEndShape As Shape) As Shape

  Const TOP_SIDE As Integer = 1
  Const BOTTOM_SIDE As Integer = 3
  Dim oConnector As Shape
  Dim x1 As Single
  Dim x2 As Single
  Dim y1 As Single
  Dim y2 As Single

  With  oBeginShape
    x1 = .Left + .Width / 2
    y1 = .Top + .Height
  End With
  With oEndShape
    x2 = .Left + .Width / 2
    y2 = .Top
  End With
  If CInt(Application.Version) < 12 Then
    x2 = x2 - x1
    y2 = y2 - y1
  End If

  Set oConnector = ActiveSheet.Shapes.AddConnector(ConnectorType, x1, y1, x2, y2)
  oConnector.ConnectorFormat.BeginConnect oBeginShape, BOTTOM_SIDE
  oConnector.ConnectorFormat.EndConnect oEndShape, TOP_SIDE

  Set AddConnectorBetweenShapes = oConnector

  Set oConnector = Nothing 
End Function

Several points worth mentioning are:

  • The ConnectorType can be one of three constants – msoConnectorCurve, msoConnectorElbow, or msoConnectorStraight.
  • The calculations for the beginning and ending points are not normally needed. You could put any values in for the AddConnector() function because once you call BeginConnect and EndConnect, the connector is attached to the shapes and the begin and end points get set automatically.
  • How the end coordinates are specified is not consistent between Excel versions. Prior to Excel 2007, the end coordinates were relative to the begin coordinates. Starting in Excel 2007, the function now uses absolute coordinates.
  • When you route a Connector to an AutoShape, you need to specify the side using a connection site constant. The constants are different for each AutoShape type, but generally they start with the top side = 1 and go counter-clockwise. For example, most rectangular shapes have connection site constants where top = 1, left = 2, bottom = 3, and right = 4.
  • When you call the RerouteConnections() function, it sets the connection sides automatically in order to create the shortest path between the two shapes. So, unless you want a specific routing, you can get usually just guess at the connection site values then call RerouteConnections().

Formatting Connectors and Lines

Like AutoShapes, formatting Connectors and Lines is fairly straightforward in Excel 2007 and 2010. Here is a comparison of two formatting routines for older versions of Excel versus the newer versions:

Sub FormatConnector2003(oConnector As Shape)
  With oConnector
    If .Connector Or .Type = msoLine Then
      ' rough approximation of the Excel 2007 preset line style #17
      .Line.EndArrowheadStyle = msoArrowheadTriangle
      .Line.Weight = 2
      .Line.ForeColor.RGB = RGB(192, 80, 77)
      .Shadow.Type = msoShadow6
      .Shadow.IncrementOffsetX -4.5
      .Shadow.IncrementOffsetY -4.5
      .Shadow.ForeColor.RGB = RGB(192, 192, 192)
      .Shadow.Transparency = 0.5
      .Visible = msoTrue
    End If
  End With
End Sub

Sub FormatConnector2007(oConnector As Shape)
  With oConnector
    If .Connector Or .Type = msoLine Then
      .Line.EndArrowheadStyle = msoArrowheadTriangle
      .ShapeStyle = msoLineStylePreset17
    End If
  End With
End Sub

The Connector property, used above, returns a Boolean indicating whether the shape is a connector. The Type=msoLine statement checks if the shape is a line. In this case the code will format both connectors and lines the same way, but at times you may want handle them separately. (NB: The Insert Shapes gallery of Excel 2007 only lets you add Connectors, not Lines. So unless you are dealing with legacy files or add Lines via code, testing Type=msoLine may never be an issue for you.)

Like the shape styles, you can format the line style by setting the ShapeStyle to one of the msoLineStylePresetXX values, where XX matches the order they appear in the style gallery (below) from left to right, top to bottom.

The Line object has several other members worth mentioning. In addition to the EndArrowheadStyle shown above, there is a corresponding BeginArrowheadStyle property, a DashStyle property, and also a Style property that lets you create double lines.

Miscellaneous Issues

Here are a few Excel 2007 issues with AutoShapes that are good to be aware of :

  • If you copy a set of shapes, older versions of Office gave the option to Paste Special as editable AutoShapes in other Office applications. This option no longer exists in Office 2007. I haven’t tested this in Office 2010 beta yet.
  • In Excel 2007 and the 2010 beta, changing the AutoShape type will disconnect any incoming or outgoing Connectors to a shape.
  • Some print drivers (including PDF export handlers) do not handle printing thick Connectors well, e.g., elbow connectors may straighten. If this happens, either change the line thickness or try grouping all the shapes together prior to printing.
  • The Arc is an AutoShape but needs to be treated as a line when setting the ShapeStyle property.
  • Most of the styles are backward compatible except for styles 37-42 (the glossy button look in the bottom row of the style gallery). Styles 31-36 (the second row from bottom) do not render very well in Excel 2000.
  • You can add Callouts using the AddShape() or AddCallout() methods, but the AddCallout() method will only let you add four types, two of which are actually the same. Callouts have AutoShapeType values in the range of 105-124. Even though they have AutoShapeType values, the Shape.Type property will return msoCallout – not msoAutoShape. Confused? Wait, there’s more. The callouts with AutoShapeTypes from 105-108 actually return a Shape.Type = msoAutoShape – not msoCallout.

Sample File

The sample file includes three demo sheets. The ShapeDemo sheet contains a macro to add two shapes, format them, then add a connector and format it. The Animation sheet has a simple macro showing how to move a shape around the sheet. The CreateAutoShapes sheet has a macro to create all AutoShapes available in your version of Excel.

The ShapeDemo routine has two function calls that are commented out – IlluminateShapeText2003() and IlluminateShapeText2007(). These subs add some gaudy formatting to the first letter of each text block, but they serve to highlight some of the differences between Excel 2007 and previous versions. Two parts of the code worth looking at are the HasText property and the formatting properties of TextFrame2. With the old TextFrame object, you would have to try accessing the TextFrame.Characters.Count property, which throws an error if no text exists. As for the formatting, the font colors in Excel 2003 and previous were limited to the colors in the pallet. In Excel 2007 and 2010, you can add reflections, drop shadows, and glow as well as set the color to whatever RGB value your heart desires.

Lastly, there is a module MCommon containing a sub that deletes all the AutoShapes on a sheet. In order not to delete the command buttons on the sheet (which are shapes too), it creates a ShapeRange of the AutoShapes and deletes that. The online help file shows the syntax for creating a ShapeRange when you know the names of the shapes at design time, but the syntax is a bit tricky when creating one dynamically. The DeleteAllAutoShapes() sub in the sample file shows you how to do this.

Click Here to Download the Sample File

About the Author

FlowBreeze Flowchart SoftwareNicholas Hebb is the founder of BreezeTree Software and author of FlowBreeze Flowchart Software, an add-in that automates flowcharting in Excel.

Peltier Tech Chart Utility

Peltier Tech Chart Utility


Create Excel dashboards quickly with Plug-N-Play reports.