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 in VBA, 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:

• VBA ChartColor 1, 9, and 20 use grayscale colors from column 3 of Figure 15-4. A ChartColor value of 1 starts with a dark gray, then a light gray, and 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, and then medium gray.

• VBA ChartColor 2 uses the six theme colors in the top row of Figure 15-4, from left to right.

• VBA ChartColor 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.

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

• Values 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.

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

• ChartColor 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 tab 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 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

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 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