Peltier Technical Services, Inc.
 

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


Peltier Tech Charts for Excel 3.0

 

Line and Fill Effects in Excel Radar 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. Line and Fill Effects in Excel Charts Using VBA shows how to draw shapes on an XY 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, including transparent fills, allowing gridlines and series to show through the shape.

Drawing a Polygon on a Radar Chart

The table at right shows the data set used for this example.

  A B C D
1   AA BB CC
2 a 2 3 2
3 b 3 2 4
4 c 2 3 2
5 d 4 2 3
6 e 3 3 2

This is a normal radar chart made using the data above, with lines connecting markers.

The data is all visible, but the extent of each series is difficult to see. The chart would be more readable if the markers were replaced by filling of the interior of each series with a unique color.

This is another version of the radar chart, formatted with the interior of each series filled.

The spread of each series is obvious, except where the series in front obscure those in back. Transparent fills would make this a more useful chart, but transparent chart series fills are not supported by Excel versions prior to 2007.

This chart shows the functionality of the DrawTransparentShapesOnRadarChart procedure. The code creates a polygon shape with its nodes (corners) on the points of the radar chart series. Each series has a unique color scheme, and the fill is 50% transparent. The colors and percent transparency could be adjusted in the code.

This figure demonstrates the enhancements added by the DrawSmoothTransparentShapesOnRadarChart procedure. The nodes of the polygon have been changed from corner points (the default) to straight nodes.

The VBA Procedures

These procedures draw polygonal shapes, using each point in the series as a node. The procedures first draw a polygonal line from the first point to the last point, including the first point again at the end of the polygon, to produce a filled shape. The X and Y values of each point are converted from the spokes' axis scale to the chart's Left/Top coordinate system, so the polygon accurately traces the series.

The VBA procedures below do not have any error-checking built into them. If a chart is not selected, the procedures will crash. If the active chart is not a Radar chart, the shape will not be correctly drawn, and the procedure may crash before drawing any shape.

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 for 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 are transparent, but this is not as good as allowing it to be drawn behind other objects.

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

DrawTransparentShapesOnRadarChart: Draw Transparent Polygons that Trace Radar Series

    Sub DrawTransparentShapesOnRadarChart()
      Dim cht As Chart
      Dim srs As Series
      Dim iSrs As Long
      Dim Npts As Integer, Ipts As Integer
      Dim myShape As Shape
      Dim Xnode As Double, Ynode As Double
      Dim Rmax As Double, Rmin As Double
      Dim Xleft As Double, Ytop As Double
      Dim Xwidth As Double, Yheight As Double
      Dim dPI As Double
      Dim iFillColor As Long
      Dim iLineColor As Long
    
      Set cht = ActiveChart
      Xleft = cht.PlotArea.InsideLeft
      Xwidth = cht.PlotArea.InsideWidth
      Ytop = cht.PlotArea.InsideTop
      Yheight = cht.PlotArea.InsideHeight
      Rmax = cht.Axes(2).MaximumScale
      Rmin = cht.Axes(2).MinimumScale
      dPI = WorksheetFunction.Pi()
    
      For iSrs = 1 To cht.SeriesCollection.Count
    
        Set srs = cht.SeriesCollection(iSrs)
    
        Select Case srs.ChartType
          Case xlRadar, xlRadarFilled, xlRadarMarkers
    
            Npts = srs.Points.Count
    
            Xnode = Xleft + Xwidth / 2 * _
                (1 + (srs.Values(Npts) - Rmin) / (Rmax - Rmin) _
                * Sin(2 * dPI * (Npts - 1) / Npts))
    
            Ynode = Ytop + Yheight / 2 * _
                (1 - (srs.Values(Npts) - Rmin) / (Rmax - Rmin) _
                * Cos(2 * dPI * (Npts - 1) / Npts))
    
            With cht.Shapes.BuildFreeform _
                (msoEditingAuto, Xnode, Ynode)
              For Ipts = 1 To Npts
    
                Xnode = Xleft + Xwidth / 2 * _
                    (1 + (srs.Values(Ipts) - Rmin) / (Rmax - Rmin) _
                    * Sin(2 * dPI * (Ipts - 1) / Npts))
    
                Ynode = Ytop + Yheight / 2 * _
                    (1 - (srs.Values(Ipts) - Rmin) / (Rmax - Rmin) _
                    * Cos(2 * dPI * (Ipts - 1) / Npts))
    
                .AddNodes msoSegmentLine, msoEditingAuto, _
                    Xnode, Ynode
              Next
              Set myShape = .ConvertToShape
            End With
    
            Select Case iSrs
              Case 1
                iFillColor = 44
                iLineColor = 12
              Case 2
                iFillColor = 45
                iLineColor = 10
              Case 3
                iFillColor = 43
                iLineColor = 17
            End Select
    
            With myShape
              .Fill.ForeColor.SchemeColor = iFillColor
              .Line.ForeColor.SchemeColor = iLineColor
              .Line.Weight = 1.5
              .Fill.Transparency = 0.5
            End With
        End Select
      Next
    
    End Sub
DrawSmoothTransparentShapesOnRadarChart: Draw Transparent Smooth Polygons on Radar Chart

    Sub DrawSmoothTransparentShapesOnRadarChart()
      Dim cht As Chart
      Dim srs As Series
      Dim iSrs As Long
      Dim Npts As Integer, Ipts As Integer
      Dim myShape As Shape
      Dim Xnode As Double, Ynode As Double
      Dim Rmax As Double, Rmin As Double
      Dim Xleft As Double, Ytop As Double
      Dim Xwidth As Double, Yheight As Double
      Dim dPI As Double
      Dim iFillColor As Long
      Dim iLineColor As Long
    
      Set cht = ActiveChart
      Xleft = cht.PlotArea.InsideLeft
      Xwidth = cht.PlotArea.InsideWidth
      Ytop = cht.PlotArea.InsideTop
      Yheight = cht.PlotArea.InsideHeight
      Rmax = cht.Axes(2).MaximumScale
      Rmin = cht.Axes(2).MinimumScale
      dPI = WorksheetFunction.Pi()
    
      For iSrs = 1 To cht.SeriesCollection.Count
    
        Set srs = cht.SeriesCollection(iSrs)
    
        Select Case srs.ChartType
          Case xlRadar, xlRadarFilled, xlRadarMarkers
    
            Npts = srs.Points.Count
    
            Xnode = Xleft + Xwidth / 2 * _
                (1 + (srs.Values(Npts) - Rmin) / (Rmax - Rmin) _
                * Sin(2 * dPI * (Npts - 1) / Npts))
    
            Ynode = Ytop + Yheight / 2 * _
                (1 - (srs.Values(Npts) - Rmin) / (Rmax - Rmin) _
                * Cos(2 * dPI * (Npts - 1) / Npts))
    
            With cht.Shapes.BuildFreeform _
                (msoEditingAuto, Xnode, Ynode)
              For Ipts = 1 To Npts
    
                Xnode = Xleft + Xwidth / 2 * _
                    (1 + (srs.Values(Ipts) - Rmin) / (Rmax - Rmin) _
                    * Sin(2 * dPI * (Ipts - 1) / Npts))
    
                Ynode = Ytop + Yheight / 2 * _
                    (1 - (srs.Values(Ipts) - Rmin) / (Rmax - Rmin) _
                    * Cos(2 * dPI * (Ipts - 1) / Npts))
    
                .AddNodes msoSegmentLine, msoEditingAuto, _
                    Xnode, Ynode
              Next
              Set myShape = .ConvertToShape
            End With
    
            For Ipts = 1 To Npts
              myShape.Nodes.SetEditingType 3 * Ipts - 2, msoEditingSmooth
            Next
    
            Select Case iSrs
              Case 1
                iFillColor = 44
                iLineColor = 12
              Case 2
                iFillColor = 45
                iLineColor = 10
              Case 3
                iFillColor = 43
                iLineColor = 17
            End Select
    
            With myShape
              .Fill.ForeColor.SchemeColor = iFillColor
              .Line.ForeColor.SchemeColor = iLineColor
              .Line.Weight = 1.5
              .Fill.Transparency = 0.5
            End With
        End Select
      Next
    
    End Sub

 

Peltier Tech Charts for Excel 3.0


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

Microsoft Most Valuable Professional

My MVP Profile