CHAPTER 15
Creating charts

Two new chart types have been introduced since Excel 2016. The filled map chart and the funnel chart join the six chart types that were added to Excel 2016.

More importantly, the macro bug that prevented Excel 2016 from creating the new charts has been fixed. Whether you are creating a new Ivy chart or a legacy chart, you can use this code:

Click here to view code image

Dim CH As Chart

Set CH = ActiveSheet.Shapes _

.AddChart2(-1, xlRegionMap).Chart

CH.SetSourceData Source:=Range("D1:E7")

' Settings specific to xlRegionMap:

With CH.FullSeriesCollection(1)

.GeoMappingLevel = xlGeoMappingLevelDataOnly

.RegionLabelOption = xlRegionLabelOptionsBestFitOnly

End With

Traditionally, the goal of VBA is to never select anything in the worksheet. Thus, you first create a chart by using the .AddChart2 method, and then you assign the data to the chart by using the .SetSourceData method. If you have co-workers who are still using the Perpetual version of Excel 2016, you will have to create the new charts using this code instead:

Click here to view code image

.Range("A1:B7").Select

ActiveSheet.Shapes.AddChart2(-1, xlWaterfall).Select

The alternative code would be needed for any of the Ivy chart types:

• xlBoxWhisker

• xlFunnel

• xlHistogram

• xlPareto

• xlRegionMap

• xlSunburst

• xlTreeMap

• xlWaterfall

Using .AddChart2 to create a chart

Excel 2013 introduced a streamlined .AddChart2 method. With this method, you can specify a chart style, type, size, and location, as well as a property introduced in Excel 2013: NewLayout:=True. When you choose NewLayout, you can avoid having a legend in a single-series chart.

The .AddChart2 method enables you to specify the chart style, chart type, left, top, width, height, and new layout. This code takes the data from A3:G6 and creates a chart to fill B8:G20:

Click here to view code image

Sub CreateChartUsingAddChart2()

'Create a Clustered Column Chart in B8:G15 from data in A3:G6

Dim CH As Chart

Range("A3:G6").Select

Set CH = ActiveSheet.Shapes.AddChart2( _

Style:=201, _

 XlChartType:=xlColumnClustered, _

 Left:=Range("B8").Left, _

 Top:=Range("B8").Top, _

 Width:=Range("B8:G20").Width, _

 Height:=Range("B8:G20").Height, _

 NewLayout:=True).Chart

End Sub

The values for Left, Top, Width, and Height are in pixels. Here you don’t have to try to guess that column B is 27.34 pixels from the left edge of the worksheet because the preceding code finds the .Left property of cell B8 and uses that as the Left of the chart.

Figure 15-1 shows the resulting chart.

Understanding chart styles

Excel 2013 introduced professionally designed chart styles that are shown in the Chart Styles gallery on the Design tab of the ribbon. These innovative designs use combinations of properties that have been in Excel for years, but they allow you to apply a group of properties in a single command. The AddChart2 method enables you to specify the style number to use when creating the chart. Unfortunately, the style numbering system is fairly complex.

Figure 15-2 shows the Chart Styles gallery for a clustered column chart.

In Figure 15-2, the chart styles are numbered 201 through 215. However, if you switch to a bar chart, the similar chart styles are numbered 216 to 230.

The styles for the old chart types run from 201 to 353. Styles 354 to 497 are for the eight new chart types.

Follow these steps to learn the style number associated with your favorite style:

1. Create a chart in the Excel user interface.

2. Open the Chart Styles gallery on the Design tab and choose the chart style you want to use. Keep the chart selected before moving to Step 3.

   

   Caution You might have a tendency to click away from the chart to admire the newly selected style. If you do unselect the chart, be certain to re-select the chart before continuing with the following steps.

3. Switch to VBA by pressing Alt+F11.

4. Open the Immediate window by pressing Ctrl+G.

5. Type ? ActiveChart.ChartStyle in the Immediate window and press Enter. The resulting number shows you the value to use for the .Style argument in the .AddChart2 method.

6. If you don’t care what chart style you will get, specify -1 as the .Style argument. This gives you the default style for that chart type.

It is strange that the .AddChart2 method uses an argument called Style:=201, but if you want to change the chart style later, you have to use the .ChartStyle property. Both Style and ChartStyle refer to the chart styles introduced in Excel 2013.

Table 15-1 lists the ChartType argument values.

Excel supports a few other chart types that misrepresent your data, such as the cone and pyramid charts. For backward compatibility, these are still in VBA, but they are omitted from Table 15-1. If your manager forces you to create those old chart types, you can find them by searching for xlChartType enumeration in your favorite search engine.

Formatting a chart

After creating a chart, you will often want to add or move elements of the chart. The following sections describe code to control the myriad chart elements.

Referring to a specific chart

The macro recorder has an unsatisfactory way of writing code for chart creation. The macro recorder uses the .AddChart2 method and adds a .Select to the end of the line to select the chart. The rest of the chart settings then apply to the ActiveChart object. This approach is a bit frustrating because you are required to do all the chart formatting before you select anything else in the worksheet. The macro recorder does this because chart names are unpredictable. The first time you run a macro, the chart might be called Chart 1. But if you run the macro on another day or on a different worksheet, the chart might be called Chart 3 or Chart 5.

For the most flexibility, you should assign each new chart to a Chart object. Since Excel 2007, the Chart object has existed inside a Shape object.

Ignoring the specifics of the AddChart2 method for a moment, you could use this coding approach, which captures the Shape object in the SH object variable and then assigns SH.Chart to the CH object variable:

Click here to view code image

Dim WS as Worksheet

Dim SH as Shape

Dim CH as Chart

Set WS = ActiveSheet

Set SH = WS.Shapes.AddChart2(...)

Set CH = SH.Chart

You can simplify the preceding code by appending .Chart to the end of the AddChart2 method. The following code has one object variable fewer:

Click here to view code image

Dim WS as Worksheet

Dim CH as Chart

Set WS = ActiveSheet

Set CH = WS.Shapes.AddChart2(...).Chart

If you need to modify a preexisting chart—such as a chart that you did not create—and there is only one shape on the worksheet, you can use this line of code:

Click here to view code image

WS.Shapes(1).Chart.Interior.Color = RGB(0,0,255)

If there are many charts, and you need to find the one with the upper-left corner located in cell A4, you can loop through all the Shape objects until you find one in the correct location, like this:

Click here to view code image

For each Sh in ActiveSheet.Shapes

If Sh.TopLeftCell.Address = "$A$4" then

Sh.Chart.Interior.Color = RGB(0,255,0)

End If

Next Sh

Specifying a chart title

Every chart created with NewLayout:=True has a chart title. When the chart has two or more series, that title is “Chart Title.” You should plan on changing the chart title to something useful.

To specify a chart title in VBA, use this code:

Click here to view code image

ActiveChart.ChartTitle.Caption = "Sales by Region"

If you are changing the chart title of a newly created chart that is assigned to the CH object variable, you can use this:

Click here to view code image

CH.ChartTitle.Caption = "Sales by Region"

This code works if your chart already has a title. If you are not sure that the selected chart style has a title, you can ensure that the title is present first with this:

Click here to view code image

CH.SetElement msoElementChartTitleAboveChart

Although it is relatively easy to add a chart title and specify the words in the title, it becomes increasingly complex to change the formatting of the chart title. The following code changes the font, size, and color of the title:

Click here to view code image

With CH.ChartTitle.Format.TextFrame2.TextRange.Font

.Name = "Rockwell"

.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2

.Size = 14

End With

The two axis titles operate the same as the chart title. To change the words, use the .Caption property. To format the words, use the Format property. Similarly, you can specify the axis titles by using the Caption property. The following code changes the axis title along the category axis:

Click here to view code image

CH.SetElement msoElementPrimaryCategoryAxisTitleHorizontal

CH.Axes(xlCategory, xlPrimary).AxisTitle.Caption = "Months"

CH.Axes(xlCategory, xlPrimary).AxisTitle. _

Format.TextFrame2.TextRange.Font.Fill. _

ForeColor.ObjectThemeColor = msoThemeColorAccent2

Applying a chart color

Excel 2013 introduced a ch.ChartColor property that assigns 1 of 26 color themes to a chart. Assign a value from 1 to 26, but be aware that the order of the colors in the Chart Styles fly-out menu (see Figure 15-3) has nothing to do with the 26 values.

To understand the ChartColor values, consider the color drop-down menu shown in Figure 15-4. This drop-down menu offers 10 columns of colors: Background 1, Text 1, Background 2, Text 2, and then Theme 1 through Theme 6.

Here is a synopsis of the 26 values you can use for ChartColor:

• ChartColor 1, 9, and 20 use grayscale colors from column 3. A ChartColor value of 1 starts with a dark gray, then a light gray, then a medium gray. A ChartColor value of 9 starts with a light gray and moves to darker grays. A ChartColor value of 20 starts with three medium grays, then black, then very light gray, then medium gray.

• Value 2 uses the six theme colors in the top row, from left to right.

• Values 3 through 8 use a single column of colors. For example, ChartColor = 3 uses the six colors in Theme 1, from dark to light. ChartColor values of 4 through 8 correspond to Themes 2 through 6.

• Value 10 repeats value 2 but adds a light border around the chart element.

• Vaues 11 through 13 are the most inventive. They use three theme colors from the top row combined with the same three theme colors from the bottom row. This produces light and dark versions of three different colors. ChartColor 11 uses the odd-numbered themes (1, 3, and 5). ChartColor 12 uses the even-numbered themes. ChartColor 13 uses Themes 4, 5, and 6.

• Values 14 through 19 repeat values 3 through 8 but add a light border.

• Values 21 through 26 are similar to values 3 through 8, but the colors progress from light to dark.

The following code changes the chart to use varying shades of Themes 4, 5, and 6:

ch.ChartColor = 13

Filtering a chart

In real life, creating charts from tables of data is not always simple. Tables frequently have totals or subtotals. The table in Figure 15-5 has quarterly total columns intermixed with monthly values. When you create a chart from this data, the total columns create a bad chart.

To filter a row or column in VBA, you set the new .IsFiltered property to True. The following code removes the total columns:

Click here to view code image

CH.ChartGroups(1).FullCategoryCollection(4).IsFiltered = True

CH.ChartGroups(1).FullCategoryCollection(8).IsFiltered = True

CH.ChartGroups(1).FullCategoryCollection(12).IsFiltered = True

CH.ChartGroups(1).FullCategoryCollection(16).IsFiltered = True

Using SetElement to emulate changes from the plus icon

When you select a chart, three icons appear to the right of the chart. The top icon is a plus sign. All the choices in the first- and second-level fly-out menus use the SetElement method in VBA. Note that the Add Chart Element drop-down menu on the Design tab includes all these settings, plus Lines and Up/Down Bars.

If you do not feel like looking up the proper constant in this book, you can always quickly record a macro.

The SetElement method is followed by a constant that specifies which menu item to select. For example, if you want to choose Show Legend At Left, you can use this code:

Click here to view code image

ActiveChart.SetElement msoElementLegendLeft

Table 15-2 shows all the available constants you can use with the SetElement method. These constants are in roughly the same order in which they appear in the Add Chart Element drop-down menu.

Using SetElement enables you to change chart elements quickly. As an example, charting gurus say that the legend should always appear to the left or above the chart. Few of the built-in styles show the legend above the chart. I also prefer to show the values along the axis in thousands or millions, when appropriate. This is better than displaying three or six zeros on every line.

The following code handles these settings after you create the chart:

Click here to view code image

Sub UseSetElement()

Dim WS As Worksheet

Dim CH As Chart

Set WS = ActiveSheet

Range("A1:M4").Select

Set CH = WS.Shapes.AddChart2(Style:=201, _

XlChartType:=xlColumnClustered, _

Left:=[B6].Left, _

Top:=[B6].Top, _

NewLayout:=False).Chart

' Set value axis to display thousands

CH.SetElement msoElementPrimaryValueAxisThousands

' move the legend to the top

CH.SetElement msoElementLegendTop

End Sub

Using the format method to micromanage formatting options

The Format tab offers icons for changing colors and effects for individual chart elements. Although many people call the Shadow, Glow, Bevel, and Material settings “chart junk,” there are ways in VBA to apply these formats.

Excel 2019 includes an object called the ChartFormat object that contains the settings for Fill, Glow, Line, PictureFormat, Shadow, SoftEdge, TextFrame2, and ThreeD. You can access the ChartFormat object by using the Format method on many chart elements. Table 15-3 lists a sampling of chart elements you can format using the Format method.

The Format method is the gateway to settings for Fill, Glow, and so on. Each of those objects has different options. The following sections provide examples of how to set up each type of format.

Changing an object’s fill

The Shape Fill drop-down menu on the Format tab enables you to choose a single color, a gradient, a picture, or a texture for the fill.

To apply a specific color, you can use the RGB (red, green, blue) setting. To create a color, you specify a value from 0 to 255 for levels of red, green, and blue. The following code applies a simple blue fill:

Click here to view code image

Dim cht As Chart

Dim upb As UpBars

Set cht = ActiveChart

Set upb = cht.ChartGroups(1).UpBars

upb.Format.Fill.ForeColor.RGB = RGB(0, 0, 255)

If you would like an object to pick up the color from a specific theme accent color, you use the ObjectThemeColor property. The following code changes the bar color of the first series to accent color 6, which is an orange color in the Office theme (but might be another color if the workbook is using a different theme):

Click here to view code image

Sub ApplyThemeColor()

Dim cht As Chart

Dim ser As Series

Set cht = ActiveChart

Set ser = cht.SeriesCollection(1)

ser.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6

End Sub

To apply a built-in texture, you use the PresetTextured method. The following code applies a green marble texture to the second series. However, you can apply any of the 20 textures:

Click here to view code image

Sub ApplyTexture()

Dim cht As Chart

Dim ser As Series

Set cht = ActiveChart

Set ser = cht.SeriesCollection(2)

ser.Format.Fill.PresetTextured msoTextureGreenMarble

End Sub

To fill the bars of a data series with a picture, you use the UserPicture method and specify the path and file name of an image on the computer, as in the following example:

Click here to view code image

Sub FormatWithPicture()

Dim cht As Chart

Dim ser As Series

Set cht = ActiveChart

Set ser = cht.SeriesCollection(1)

MyPic = "C:PodCastTitle1.jpg"

ser.Format.Fill.UserPicture MyPic

End Sub

In Excel 2019, you can apply a pattern by using the .Patterned method. Patterns have a type such as msoPatternPlain, as well as foreground and background colors. The following code creates dark red vertical lines on a white background:

Click here to view code image

Sub FormatWithPicture()

Dim cht As Chart

Dim ser As Series

Set cht = ActiveChart

Set ser = cht.SeriesCollection(1)

With ser.Format.Fill

.Patterned msoPatternDarkVertical

.BackColor.RGB = RGB(255,255,255)

.ForeColor.RGB = RGB(255,0,0)

End With

End Sub

Gradients are more difficult to specify than fills. Excel 2019 provides three methods that help you set up the common gradients. The OneColorGradient and TwoColorGradient methods require that you specify a gradient direction, such as msoGradientFromCorner. You can then specify one of four styles, numbered 1 through 4, depending on whether you want the gradient to start at the top left, top right, bottom left, or bottom right. After using a gradient method, you need to specify the ForeColor and the BackColor settings for the object. The following macro sets up a two-color gradient using two theme colors:

Click here to view code image

Sub TwoColorGradient()

Dim cht As Chart

Dim ser As Series

Set cht = ActiveChart

Set ser = cht.SeriesCollection(1)

ser.Format.Fill.TwoColorGradient msoGradientFromCorner, 3

ser.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6

ser.Format.Fill.BackColor.ObjectThemeColor = msoThemeColorAccent2

End Sub

When using the OneColorGradient method, you specify a direction, a style (1 through 4), and a darkness value between 0 and 1 (0 for darker gradients to 1 for lighter gradients).

When using the PresetGradient method, you specify a direction, a style (1 through 4), and the type of gradient, such as msoGradientBrass, msoGradientLateSunset, or msoGradientRainbow. Again, as you are typing this code in the VB Editor, the AutoComplete tool provides a complete list of the available preset gradient types.

Formatting line settings

The LineFormat object formats either a line or the border around an object. You can change numerous properties of a line, such as the color, arrows, and dash style.

The following macro formats the trendline for the first series in a chart:

Click here to view code image

Sub FormatLineOrBorders()

Dim cht As Chart

Set cht = ActiveChart

With cht.SeriesCollection(1).Trendlines(1).Format.Line

.DashStyle = msoLineLongDashDotDot

.ForeColor.RGB = RGB(50, 0, 128)

.BeginArrowheadLength = msoArrowheadShort

.BeginArrowheadStyle = msoArrowheadOval

.BeginArrowheadWidth = msoArrowheadNarrow

.EndArrowheadLength = msoArrowheadLong

.EndArrowheadStyle = msoArrowheadTriangle

.EndArrowheadWidth = msoArrowheadWide

End With

End Sub

When you are formatting a border, the arrow settings are not relevant, so the code is shorter than the code for formatting a line. The following macro formats the border around a chart:

Click here to view code image

Sub FormatBorder()

Dim cht As Chart

Set cht = ActiveChart

With cht.ChartArea.Format.Line

.DashStyle = msoLineLongDashDotDot

.ForeColor.RGB = RGB(50, 0, 128)

End With

End Sub

Creating a combo chart

Sometimes you need to chart series of data that are of differing orders of magnitude. Normal charts do a lousy job of showing smaller series. Combo charts can save the day.

Consider the data and chart in Figure 15-6. Here you want to plot the number of sales per month and also show two quality ratings. Perhaps this is a fictitious car dealer that sells 80 to 100 cars a month, and the customer satisfaction usually runs in the 80% to 90% range. When you try to plot this data on a regular line chart, the column for 90 cars sold dwarfs the column for 80% customer satisfaction.

Creating map charts

The new filled map chart offers some settings unique to map charts. Say that you have data for six states in the southeast United States. By default, the map chart shows 48 of the 50 states. Set the .GeoMappingLevel to xlGeoMappingDataOnly to limit the map to only states with data, as shown in Figure 15-8.

Click here to view code image

Sub RegionMapChart()

Dim CH As Chart

Set CH = ActiveSheet.Shapes.AddChart2(-1, xlRegionMap).Chart

CH.SetSourceData Source:=ActiveSheet.Range("A1:B7")

' the following properties are specific to filled map charts

With CH.FullSeriesCollection(1)

.GeoMappingLevel = xlGeoMappingLevelDataOnly

.RegionLabelOption = xlRegionLabelOptionsBestFitOnly

End With

End Sub

Note that Mississippi is not labeled in the chart in Figure 15-8. This is because RegionLabelOption is set to xlRegionLabelOptionsBestFitOnly. To force all labels to appear, use xlRegionLabelOptionsShowAll instead.

You can export any chart to an image file on your hard drive. The ExportChart method requires you to specify a file name and a graphic type. The available graphic types depend on graphic file filters installed in your Registry. It is a safe bet that JPG, BMP, PNG, and GIF work on most computers.

Creating waterfall charts

Waterfall charts are often used to show profit on a sale or cash flow over the course of a year. A waterfall chart is composed of floating columns that raise or lower from the previous column. However, some points will be marked as Totals, such as the Net Price column in Figure 15-9. Use the .IsTotal property to force a column to not float.

Click here to view code image

Sub WaterfallChart()

Dim CH As Chart

Set CH = ActiveSheet.Shapes.AddChart2(-1, xlWaterfall).Chart

CH.SetSourceData Source:=ActiveSheet.Range("A1:B7")

' Mark certain points as totals

With CH.FullSeriesCollection(1)

.Points(1).IsTotal = True

.Points(3).IsTotal = True

.Points(7).IsTotal = True

End With

End Sub

One of the frustrations with the new Ivy charting engines is this: It is often difficult to figure out how to change the colors. In the waterfall chart in Figure 15-9, there are colors for Increase, Decrease, and Total. The only way to format those colors is to do the following:

1. Click the legend to select the legend.

2. Click the Increase legend entry to select that one single legend entry.

3. Right-click to see a menu with a choice to change the fill for Increase.

The equivalent VBA often crashes Excel. This might be a temporary bug, and it might be fixed by the time you are reading this:

Click here to view code image

Sub FormatWaterfall()

Dim cht As Chart

Dim lg As Legend

Dim lgentry As LegendEntry

Dim iLegEntry As Long

Set cht = ActiveChart

Set lg = cht.Legend

For iLegEntry = 1 To lg.LegendEntries.Count

Set lgentry = lg.LegendEntries(iLegEntry)

lgentry.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1 + iLegEntry - 1

Next

End Sub

Exporting a chart as a graphic

You can export any chart to an image file on your hard drive. The ExportChart method requires you to specify a file name and a graphic type. The available graphic types depend on graphic file filters installed in your Registry—usually JPG, BMP, PNG, and GIF.

For example, the following code exports the active chart as a GIF file:

Click here to view code image

Sub ExportChart()

Dim cht As Chart

Set cht = ActiveChart

cht.Export Filename:="C:Chart.gif", Filtername:="GIF"

End Sub

Considering backward compatibility

The .AddChart2 method works in Excel 2013 through Excel 2019. For Excel 2007 and 2010, you have to revert to using the .AddChart method, as shown here:

Click here to view code image

Sub CreateChartIn20072010()

'Create a Clustered Column Chart in B8:G15 from data in A3:G6

Dim CH As Chart

Range("A3:G6").Select

Set CH = ActiveSheet.Shapes.AddChart( _

XlChartType:=xlColumnClustered, _

Left:=Range("B8").Left, _

Top:=Range("B8").Top, _

Width:=Range("B8:G15").Width, _

Height:=Range("B8:G15").Height).Chart

End Sub

With this method, you can specify neither a Style nor a NewLayout.

Next steps

In Chapter 16, “Data visualizations and conditional formatting,” you’ll find out how to automate data visualization tools such as icon sets, color scales, and data bars.