Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog

Line and Fill Effects in Excel Charts Using VBA.

Excel charts offer a wide variety of formats, but you can use Excel's drawing tools to enable even more formatting choices. It's possible to draw shapes on the chart to produce these formats, using the polygon drawing tool. This allows more line formats, by enabling more choices of line thickness and by making it easier to read dashed lines. More fill possibilities are made possible than merely filling below a series, as in an area chart: the fill can go below or to the side of the series, and in fact, an enclosed region in the chart can be filled. The fill can be made transparent too, allowing gridlines to show through the shape.

Click on an image to go to the relevant description.

This article presents VBA procedures that automate the polygon drawing tool, and gives hints about the kinds of formatting which may be achieved.

Enhancing the Lines in a Scatter Chart

The table below shows the sample data used for several examples, and a simple XY Scatter chart which will be used in the first example.

X	Y
1.308	8.880
4.710	6.923
5.238	10.963
5.671	5.238
5.679	7.708
6.447	8.917
6.923	1.308
7.612	5.671
7.708	7.612
8.880	5.679
8.917	6.447
10.963	4.710

Select the chart and run the TraceXY procedure below. The drawn line (red) and plotted series (blue) are both shown for comparison. Hide the XY series: before running the macro, double click on the series, and on the Patterns tab, select None wherever possible.

The line shape can be double clicked to open the formatting dialog. In the following chart, the line has been made thicker (1.5 pt) and given a dashed pattern.

In this version of the chart, a more intricate dashed line patter was selected, and arrowheads were defined for the endpoints of the line. Notice how the pattern continues around nodes. In the lines in a scatter chart, the pattern resets at each point, so closely spaced points may not display any of the dashed pattern at all.

Modified Procedure to Connect XY Series with Arrows

A modified version of this procedure that connects corresponding points of two XY series with arrows has been posted in my blog at Connect Two XY Series with Arrows (VBA).

Connect XY Series with Arrows

Fill the Region Enclosed by a Scatter Chart

Below are sample data and a simple XY Scatter chart used for the next example. To fill in the polygon, the procedure uses all of the points in the XY series, repeating one of the endpoints so it starts and stops at the same location.

X	Y
1	11
6	11
8	9
10	11
11	1
6	2
1	1

Select the chart and run the DrawFilledPolygon procedure below. The drawn polygon (green) and plotted series (blue) are both shown for comparison. Hide the XY series: before running the macro, double click on the series, and on the Patterns tab, select None wherever possible.

The shape is formatted by double-clicking on it. You can choose any colors, including fancy gradients like this. You can use textures or pictures to fill the shape. You can also use transparency to let hidden features show through the shape.

Imagine the possibilities....

Fill Below a Scatter Chart

This chart uses the same data as the first example.

Select the chart and run the ShadeBelow procedure below. The drawn polygon (yellow) and plotted series (blue) are both shown for comparison. Hide the XY series: before running the macro, double click on the series, and on the Patterns tab, select None wherever possible.

This chart has been embellished by using a transparent fill, and by running the TraceXY procedure to create a heavy red line for the series (using the border of the polygon would result in a line around the entire periphery of the shape, not just where the data is plotted).

Fill to the Left of a Scatter Chart

This chart uses the same data as the first example, except X and Y have been switched.

Select the chart and run the ShadeLeft procedure below. The drawn polygon (cyan) and plotted series (blue) are both shown for comparison. Hide the XY series: before running the macro, double click on the series, and on the Patterns tab, select None wherever possible.

This chart has been embellished by using a transparent fill, and by running the TraceXY procedure to create a heavy dashed blue line for the series (using the border of the polygon would result in a line around the entire periphery of the shape, not just where the data is plotted).

Other Chart Types

Line and Fill Effects in Excel Radar Charts Using VBA shows how to apply these techniques to Excel's radar charts.

Related Non-VBA Chart Fill Techniques

To fill the area below or between two XY series, you can also use the following techniques. They have advantages over the VBA techniques, since they don't use VBA, and the filled regions are behind any data in the XY series, but they also require more work in setting up additional series and secondary axes.

To fill the area between two line series, you can use the following technique.

The VBA Procedures

These procedures draw polygonal shapes, using each point in the series as a node. The first procedure merely draws a polygonal line from the first point to the last point, while the other procedures add additional points, closing the polygon to produce a filled shape. The X and Y coordinates of each point are converted from the axis scale to the chart's coordinate system, so the polygon accurately traces the series.

These procedures are designed to work on the first series in the active chart. The TraceXYMulti shows how to expand the TraceXY example to all series in the chart).

The VBA procedures below do not have any error-checking built into them. If a chart is not selected, the procedure will crash. If the active chart is not a Scatter chart, the shape will not be correctly drawn, and the procedure may crash before drawing any shape. If the axes display the values in descending order, the shape will be a mirror image of what it should be.

The procedures draw shapes with predetermined colors. The VBA code could be adjusted to produce shapes of any desired color scheme, but it may be just as easy in most cases to run the macro, then double click on the shape to open the formatting dialog.

Shapes on a chart are drawn in front of other chart features. Plotted points, labels, and other elements will be obscured by the shapes. The shapes can be made transparent, but this is not as good as allowing it to be drawn behind other objects.

If the axis scales of the chart change, or if the chart is resized, the shapes will no longer match the XY series properly. In this case, the existing shapes must be deleted and redrawn.

TraceXY: Draw Polygon that Traces XY Series


    Sub TraceXY()
      Dim myCht As Chart
      Dim mySrs As Series
      Dim Npts As Integer, Ipts As Integer
      Dim myBuilder As FreeformBuilder
      Dim myShape As Shape
      Dim Xnode As Double, Ynode 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
    
      Set myCht = ActiveChart
      Xleft = myCht.PlotArea.InsideLeft
      Xwidth = myCht.PlotArea.InsideWidth
      Ytop = myCht.PlotArea.InsideTop
      Yheight = myCht.PlotArea.InsideHeight
      Xmin = myCht.Axes(1).MinimumScale
      Xmax = myCht.Axes(1).MaximumScale
      Ymin = myCht.Axes(2).MinimumScale
      Ymax = myCht.Axes(2).MaximumScale
    
      Set mySrs = myCht.SeriesCollection(1)
      Npts = mySrs.Points.Count
    
      ' first point
      Xnode = Xleft + (mySrs.XValues(1) - Xmin) * Xwidth / (Xmax - Xmin)
      Ynode = Ytop + (Ymax - mySrs.Values(1)) * Yheight / (Ymax - Ymin)
      Set myBuilder = myCht.Shapes.BuildFreeform(msoEditingAuto, Xnode, Ynode)
    
      ' remaining points
      For Ipts = 2 To Npts
        Xnode = Xleft + (mySrs.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin)
        Ynode = Ytop + (Ymax - mySrs.Values(Ipts)) * Yheight / (Ymax - Ymin)
        myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
      Next
    
      Set myShape = myBuilder.ConvertToShape
    
      With myShape
        ' USE YOUR FAVORITE COLORS HERE
        .Line.ForeColor.SchemeColor = 10  ' RED
      End With
    
    End Sub

TraceXYMulti: Draw Polygons that Trace Each XY Series in Chart

The TraceXY procedure above traces the first series in the chart, following this line of code:

    Set mySrs = myCht.SeriesCollection(1)

To trace a different series in the chart, change the 1 in this line. To trace every series in the chart, run the following TraceXYMulti procedure, which loops through all series in the chart.

The same looping technique can be used like this in the other procedures in this tutorial.


    Sub TraceXYMulti()
      Dim myCht As Chart
      Dim mySrs As Series, Isrs As Integer
      Dim Npts As Integer, Ipts As Integer
      Dim myBuilder As FreeformBuilder
      Dim myShape As Shape
      Dim Xnode As Double, Ynode 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

      Set myCht = ActiveChart
      Xleft = myCht.PlotArea.InsideLeft
      Xwidth = myCht.PlotArea.InsideWidth
      Ytop = myCht.PlotArea.InsideTop
      Yheight = myCht.PlotArea.InsideHeight
      Xmin = myCht.Axes(1).MinimumScale
      Xmax = myCht.Axes(1).MaximumScale
      Ymin = myCht.Axes(2).MinimumScale
      Ymax = myCht.Axes(2).MaximumScale

      For Isrs = 1 To myCht.SeriesCollection.Count

        Set mySrs = myCht.SeriesCollection(Isrs)
        Npts = mySrs.Points.Count

        ' first point
        Xnode = Xleft + (mySrs.XValues(1) - Xmin) * Xwidth / (Xmax - Xmin)
        Ynode = Ytop + (Ymax - mySrs.Values(1)) * Yheight / (Ymax - Ymin)
        Set myBuilder = myCht.Shapes.BuildFreeform(msoEditingAuto, Xnode, Ynode)

        ' remaining points
        For Ipts = 2 To Npts
          Xnode = Xleft + (mySrs.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin)
          Ynode = Ytop + (Ymax - mySrs.Values(Ipts)) * Yheight / (Ymax - Ymin)
          myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
        Next

        Set myShape = myBuilder.ConvertToShape

        With myShape
          ' USE YOUR FAVORITE COLORS HERE
          .Line.ForeColor.SchemeColor = 1 + Isrs Mod 6
        End With

      Next
    End Sub

DrawFilledPolygon: Draw Filled Polygon that Traces XY Series


    Sub DrawFilledPolygon()
      Dim myCht As Chart
      Dim mySrs As Series
      Dim Npts As Integer, Ipts As Integer
      Dim myBuilder As FreeformBuilder
      Dim myShape As Shape
      Dim Xnode As Double, Ynode 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
    
      Set myCht = ActiveChart
      Xleft = myCht.PlotArea.InsideLeft
      Xwidth = myCht.PlotArea.InsideWidth
      Ytop = myCht.PlotArea.InsideTop
      Yheight = myCht.PlotArea.InsideHeight
      Xmin = myCht.Axes(1).MinimumScale
      Xmax = myCht.Axes(1).MaximumScale
      Ymin = myCht.Axes(2).MinimumScale
      Ymax = myCht.Axes(2).MaximumScale
    
      Set mySrs = myCht.SeriesCollection(1)
      Npts = mySrs.Points.Count
    
      ' first point
      Xnode = Xleft + (mySrs.XValues(Npts) - Xmin) * Xwidth / (Xmax - Xmin)
      Ynode = Ytop + (Ymax - mySrs.Values(Npts)) * Yheight / (Ymax - Ymin)
      Set myBuilder = myCht.Shapes.BuildFreeform(msoEditingAuto, Xnode, Ynode)
    
      ' remaining points
      For Ipts = 1 To Npts
        Xnode = Xleft + (mySrs.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin)
        Ynode = Ytop + (Ymax - mySrs.Values(Ipts)) * Yheight / (Ymax - Ymin)
        myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
      Next
    
      Set myShape = myBuilder.ConvertToShape
    
      With myShape
        ' USE YOUR FAVORITE COLORS HERE
        .Fill.ForeColor.SchemeColor = 11  ' BRIGHT GREEN
        .Line.ForeColor.SchemeColor = 17  ' DARK GREEN
        .Line.Weight = 1.5
      End With
    
    End Sub

ShadeBelow: Draw Polygon that Traces XY Series and Fills Area Below Series


    Sub ShadeBelow()
      Dim myCht As Chart
      Dim mySrs As Series
      Dim Npts As Integer, Ipts As Integer
      Dim myBuilder As FreeformBuilder
      Dim myShape As Shape
      Dim Xnode As Double, Ynode 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
    
      Set myCht = ActiveChart
      Xleft = myCht.PlotArea.InsideLeft
      Xwidth = myCht.PlotArea.InsideWidth
      Ytop = myCht.PlotArea.InsideTop
      Yheight = myCht.PlotArea.InsideHeight
      Xmin = myCht.Axes(1).MinimumScale
      Xmax = myCht.Axes(1).MaximumScale
      Ymin = myCht.Axes(2).MinimumScale
      Ymax = myCht.Axes(2).MaximumScale
    
      Set mySrs = myCht.SeriesCollection(1)
      Npts = mySrs.Points.Count
    
      ' first point
      Xnode = Xleft + (mySrs.XValues(1) - Xmin) * Xwidth / (Xmax - Xmin)
      Ynode = Ytop + Yheight
      Set myBuilder = myCht.Shapes.BuildFreeform(msoEditingAuto, Xnode, Ynode)
    
      ' remaining points
      For Ipts = 1 To Npts
        Xnode = Xleft + (mySrs.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin)
        Ynode = Ytop + (Ymax - mySrs.Values(Ipts)) * Yheight / (Ymax - Ymin)
        myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
      Next
    
      Xnode = Xleft + (mySrs.XValues(Npts) - Xmin) * Xwidth / (Xmax - Xmin)
      Ynode = Ytop + Yheight
      myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
    
      Xnode = Xleft + (mySrs.XValues(1) - Xmin) * Xwidth / (Xmax - Xmin)
      Ynode = Ytop + Yheight
      myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
    
      Set myShape = myBuilder.ConvertToShape
    
      With myShape
        ' USE YOUR FAVORITE COLORS HERE
        .Fill.ForeColor.SchemeColor = 13  ' YELLOW
        .Line.Visible = False
      End With
    
    End Sub

ShadeLeft: Draw Polygon that Traces XY Series and Fills Area to Left of Series


    Sub ShadeLeft()
      Dim myCht As Chart
      Dim mySrs As Series
      Dim Npts As Integer, Ipts As Integer
      Dim myBuilder As FreeformBuilder
      Dim myShape As Shape
      Dim Xnode As Double, Ynode 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
    
      Set myCht = ActiveChart
      Xleft = myCht.PlotArea.InsideLeft
      Xwidth = myCht.PlotArea.InsideWidth
      Ytop = myCht.PlotArea.InsideTop
      Yheight = myCht.PlotArea.InsideHeight
      Xmin = myCht.Axes(1).MinimumScale
      Xmax = myCht.Axes(1).MaximumScale
      Ymin = myCht.Axes(2).MinimumScale
      Ymax = myCht.Axes(2).MaximumScale
    
      Set mySrs = myCht.SeriesCollection(1)
      Npts = mySrs.Points.Count
    
      ' first point
      Xnode = Xleft
      Ynode = Ytop + (Ymax - mySrs.Values(1)) * Yheight / (Ymax - Ymin)
      Set myBuilder = myCht.Shapes.BuildFreeform(msoEditingAuto, Xnode, Ynode)
    
      ' remaining points
      For Ipts = 1 To Npts
        Xnode = Xleft + (mySrs.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin)
        Ynode = Ytop + (Ymax - mySrs.Values(Ipts)) * Yheight / (Ymax - Ymin)
        myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
      Next
    
      Xnode = Xleft
      Ynode = Ytop + (Ymax - mySrs.Values(Npts)) * Yheight / (Ymax - Ymin)
      myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
    
      Xnode = Xleft
      Ynode = Ytop + (Ymax - mySrs.Values(1)) * Yheight / (Ymax - Ymin)
      myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
    
      Set myShape = myBuilder.ConvertToShape
    
      With myShape
        ' USE YOUR FAVORITE COLORS HERE
        .Fill.ForeColor.SchemeColor = 15  ' CYAN
        .Line.Visible = False
      End With
    
    End Sub

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile