13. Using Excel VBA to Create Charts

Introducing VBA

Version 5 of Excel introduced a powerful new macro language called Visual Basic for Applications (VBA). Every copy of Excel shipped since 1993 has had a copy of the powerful VBA language hiding behind the worksheets. VBA allows you to perform steps that you normally perform in Excel, but to perform them very quickly and flawlessly. A VBA program can turn a process that used to take days each month into a single button click and a minute of processing time. If you have a lot of charts to create, you can set up a macro to automatically produce a series of charts. This is appropriate if you regularly have to produce a similar set of charts every day, week, or month.

You shouldn’t be intimidated by VBA. The VBA macro recorder tool gets you 80 percent of the way to a useful macro, and the examples in this chapter get you the rest of the way there.

Every example in this chapter is available for download from http://www.mrexcel.com/chart2010data.html.

Enabling VBA in Your Copy of Excel

By default, VBA is disabled in Office 2010. Before you can start using VBA, you need to enable macros in the Trust Center. Follow these steps:

1. From the File menu, select Options.
2. In the left navigation, select Trust Center.
3. On the right side of the Options dialog, click Trust Center Settings.
4. In the Trust Center left navigation, select Macro Settings.
5. Select Disable All Macros With Notification.

This will allow macros to run after you verify that you are expecting the macros to be in the file. New in Office 2010, for files stored on the local hard drive, you only need to confirm that you trust the macros once per workbook.

Further, when you save your files, you have to save the files as Excel 2010 macro-enabled workbooks, with the .xlsm extension.

Enabling the Developer Tab

Most of the VBA tools are located on the Developer tab of the Excel 2010 Ribbon. By default, this tab is not displayed. To enable it, go to File, Options, Customize Ribbon. In the right list box, the Developer tab will be unchecked. Click to add a checkmark, and then click OK.

As shown in Figure 13.1, the Code group on the Developer tab of the Ribbon offers icons for accessing the Visual Basic Editor, the Macros dialog box, macro recording tools, and the Macro Security setting.

Figure 13.1 You need to enable the Developer tab to access the VBA tools.

The Visual Basic Editor

From Excel, you press Alt+F11 or, from the Developer tab, you select Visual Basic to open the Visual Basic Editor. The VBA Editor, shown in Figure 13.2, has three main sections:

Figure 13.2 The Visual Basic Editor window is lurking behind every copy of Excel shipped since 1993.

• Project Explorer—This pane display

• Properties window—The Properties window is important when you begin to program user forms. It is also useful when you’re writing normal code. You enable it by pressing F4.

• Code window—This is the area where you write your code. Code is stored in one or more code modules attached to the workbook. To add a code module to a workbook, you select Insert, Module from the application menu.

Visual Basic Tools

Visual Basic is a powerful development environment. Although this chapter cannot offer a complete course on VBA, if you are new to VBA, you should take advantage of the following:

• As you begin to type code, Excel usually offers a drop-down with valid choices. This feature, known as AutoComplete, allows you to type code faster and eliminate typing mistakes.

• For assistance with any keyword, you can put the cursor in the keyword and press F1. You might need your installation CDs because the VBA Help file can be excluded from the installation of Office 2010.

• Excel checks each line of code as you finish it. Lines in error appear in red. Comments appear in green. You can add a comment by typing a single apostrophe. You should use lots of comments so you can remember what each section of code is doing.

• Despite the aforementioned error checking, Excel may still encounter errors at runtime. If this happens, you can click the Debug button. The line that caused the error is highlighted in yellow. You can then hover your mouse cursor over any variable to see the current value of the variable.

• When you are in Debug mode, you can use the Debug menu to step through code line-by-line. You can toggle back and forth between Excel and VBA to see the effect of running a line of code on the worksheet.

• Other great debugging tools are breakpoints, the Watch window, the Object Browser, and the Immediate window. You can read about these tools in the Excel VBA Help menu.

The Macro Recorder

Excel offers a macro recorder that is about 80 percent perfect for charts. Code that you record to work with one dataset is hard-coded to work only with that dataset. This behavior might work fine if your chart data occupies Cells A1:E7 every single day, but if you might have a different number of customers each day, it is unlikely that you will have the same number of rows each day. Given that you might need to work with other data, it would be a lot better if Excel could record your actions of selecting cells when you use the End key. This is one of the shortcomings of the macro recorder.

Excel pros often use the macro recorder to record code and expect to have to then clean up the recorded code. While the macro recorder in Excel 2007 was not finished for charting, it is now working well in Excel 2010.

Understanding Object-Oriented Code

If you took a class in BASIC a long time ago, the recorded code in VBA is going to appear rather foreign to you. Whereas BASIC is a procedural language, VBA is an object-oriented language. Most lines of VBA code follow the Noun.Verb, or Object.Method, syntax. Objects can be workbooks, worksheets, charts, cells, or ranges of cells. Methods can be typical Excel actions, such as Copy, Paste, and PasteSpecial.

Many methods allow adverbs—that is, parameters you use to specify how to perform a method. If you see a construction that includes a colon and an equal sign, it is an adverb, and you know that the macro recorder is describing how the method should work.

You might also see adjectives, or properties. If you set ActiveCell.Font.ColorIndex = 3, you are setting the font color (the property) of the active cell to red (the value).

Learning Tricks of the VBA Trade

You need to master a few simple techniques to be able to write efficient VBA code. These techniques will help you make the jump to writing effective code.

Writing Code to Handle a Data Range of Any Size

The macro recorder hard-codes the fact that your data is in a range, such as A1:E7. Although this hard-coding works for today’s dataset, it may not work as you get new datasets. You need to write code that can deal with different sizes of datasets.

One method is to use the CurrentRegion property. If you specify one nonblank cell and ask for the current region, Excel extends the selection in each direction until it encounters the edge of the worksheet, a blank row, or a blank column. In Figure 13.3, for example, the following line of code selected A1:E4:

Range("B1").CurrentRegion.Select

Figure 13.3 Selecting the current region extends the selection out until a blank row/column is encountered.

If you are absolutely sure that Cell B1 is nonblank and that no other data touches your chart data, you could use the CurrentRegion approach to specify the data to a chart.

The macro recorder uses syntax such as Range(“H12”) to refer to a cell. However, it is more flexible to use Cells(12, 8) to refer to H12. Why (12, 8)? Because H12 is in Row 12, Column 8. Similarly, the macro recorder refers to a rectangular range using syntax such as Range(“A1:K415501”). However, it is more flexible to use the Cells syntax to refer to the upper-left corner of the range and then use the Resize() syntax to refer to the number of rows and columns in the range:

Cells(1, 1).Resize(415501,11)

This approach is more flexible than using Range(“A1:K415501”) because you can replace any of the numbers with a variable. Rather than hard-coding Cells(12,8), you will frequently replace the numbers with variables such as Cells(i, j).

In the Excel user interface, you can use the End key on the keyboard to jump to the end of a range of data. If you move the cell pointer to the final row on the worksheet and press the End key and then the Up Arrow key, the cell pointer jumps to the last row that contains data. The equivalent of doing this in VBA is to use the following code:

Range("A1048576").End(xlUp).Select

You don’t need to select this cell; you just need to find the row number that contains the last row. The following code locates this row and saves the row number to a variable named FinalRow:

FinalRow = Range("A1048576").End(xlUp).Row

There is nothing magical about the variable name FinalRow. You could call this variable x or y, or even give it your dog’s name. However, because VBA allows you to use meaningful variable names, you should use something such as FinalRow to describe the final row.

You can also find the final column in a dataset. If you are relatively sure that the dataset begins in Row 1, you can use the End key in combination with the left-arrow key to jump from cell XFD1 to the last column that contains data. To generalize for the possibility that the code is running in legacy versions of Excel, you can use the following code:

FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column

Using Super-Variables: Object Variables

In typical programming languages, a variable holds a single value. You might use x = 4 to assign a value of 4 to the variable x.

Many properties describe a single cell in Excel. A cell might contain a value such as 4, and the cell also has a font size, a font color, a row, a column, possibly a formula, possibly a comment, a list of precedents, and more. It is possible to use VBA to create a super-variable that contains all the information about a cell or any other object. A statement to create a typical variable such as x = Range(“A1”) assigns the current value of Cell A1 to the variable x.

You can use the Set keyword to create an object variable:

Set x = Range("A1")

This formula creates a super-variable that contains all the properties of the cell. Instead of having a variable with only one value, you now have a variable in which you can access the value of many properties associated with that variable. You can reference x.Formula to learn the formula in Cell A1 or x.Font.ColorIndex to learn the color of the cell.

Using object variables can make it easier to write code. Instead of continuously referring to ThisWorkbook.Worksheets(“Income Statement”), you can define an object variable and use that as shorthand. For example, the following code repeatedly refers to the same workbook:

ThisWorkbook.Worksheets("Income Statement").ChartObjects("Chart1").Chart _
    .SetSourceData Source:= ThisWorkbook.Worksheets("Income Statement") _
    .Range("A1:E4")
ThisWorkbook.Worksheets("Income Statement").ChartObjects("Chart1").Left = 10
ThisWorkbook.Worksheets("Income Statement").ChartObjects("Chart1").Top = 30
ThisWorkbook.Worksheets("Income Statement").ChartObjects("Chart1").Width = 300
ThisWorkbook.Worksheets("Income Statement").ChartObjects("Chart1").Height = 200

If you define an object variable first, the code becomes shorter and easier to write:

Dim WS as Worksheet
Set WS = ThisWorkbook.Worksheets("Income Statement")
WS.ChartObjects("Chart1").Chart.SetSourceData Source:= WS.Range("A1:E4")
WS.ChartObjects("Chart1").Left = 10
WS.ChartObjects("Chart1").Top = 30
WS.ChartObjects("Chart1").Width = 300
WS.ChartObjects("Chart1").Height = 200

If you define two object variables, you can simplify the code even further:

Dim WS as Worksheet
Dim ChtO as ChartObject
Set WS = ThisWorkbook.Worksheets("Income Statement")
Set ChtO = WS.ChartObjects("Chart 1")
ChtO.Chart.SetSourceData Source:= WS.Range("A1:E4")
ChtO.Left = 10
ChtO.Top = 30
ChtO.Width = 300
ChtO.Height = 200

Using With and End With When Referring to an Object

In the previous code, several lines all refer to the same chart object. Rather than reference this object on every line of code, you could specify the chart object once in a With statement. In each subsequent line, you could leave off the name of the chart and begin the line with a period. You would end the block of code with an End With statement. This is faster to write than typing the complete object name multiple times, and it executes faster because Excel only has to figure out what WS.ChartObjects(“Chart1”) means once. The following code uses the With syntax while setting five properties:

Dim WS as Worksheet
Set WS = ThisWorkbook.Worksheets("Income Statement")
With WS.ChartObjects("Chart1")
    .Chart.SetSourceData Source:= WS.Range("A1:E4")
    .Left = 10
    .Top = 30
    .Width = 300
    .Height = 200
End With

Continuing a Line

Some lines of code can get very long. To improve readability, you can break a line and continue it. To indicate that the current line is continued on the next line, you can type a space and then an underscore character. Typically, the convention is to then indent the continued line of code. This is not required by VBA, but improves readability. For example, the following two lines of code are really a single line of code:

FinalCol = Cells(1, Columns.Count). _
    End(xlToLeft).Column

Adding Comments to Code

When you figure out an interesting technique in code, it’s a good idea to add comments in the code. This will help you if you return to the code several months later, and it will help others who have to troubleshoot your code.

The comment character in VBA is a single apostrophe (‘). You can use a single-line comment, a several-line comment, or a comment that takes up only the end of a line. The Visual Basic Editor changes the color of your comments to green to differentiate them from other code. The following macro has comments to document where you can turn to for more information:

Sub CommentsImproveReadability()
    Dim WS As Worksheet
    Set WS = Worksheets("Sheet1")
    ' Create a chart on a new sheet
    Charts.Add
    ' This technique is from chapter 13
    ' of the Excel Charting book
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=WS.Range("A1:B4")
    ActiveChart.Interior.ColorIndex = 4 ' Green
End Sub

Coding for New Charting Features in Excel 2010

Charts have been completely rewritten for Excel 2007. Most code from Excel 2003 will continue to work in Excel 2010. However, if you write code to take advantage of the new charting features, that code will not be backward compatible with Excel 2003.

The following are some of the methods and features that will not work in Excel 2003:

• ApplyLayout—This method applies one of the chart layouts available on the Design tab.

• SetElement—This method chooses any of the built-in element choices from the Layout tab.

• ChartFormat—This object allows you to change the fill, glow, line, reflection, shadow, soft edge, or 3-D format of most individual chart elements. This is similar to settings on the Format tab.

• AddChart—This method allows you to add a chart to an existing worksheet.

Referencing Charts and Chart Objects in VBA Code

If you go back far enough in Excel history, you find that all charts used to be created as their own chart sheets. Then, in the mid-1990s, Excel added the amazing capability to embed a chart right onto an existing worksheet. This allowed a report to be created with tables of numbers and charts all on the same page, something we take for granted today. These two different ways of dealing with charts have made it necessary for us to deal with two separate object models for charts. When a chart is on its own standalone chart sheet, you are dealing with a Chart object. When a chart is embedded in a worksheet, you are dealing with a ChartObject object. Excel 2007 introduced a third evolutionary branch because objects on a worksheet are also a member of the Shapes collection.

In Excel 2003, to reference the color of the chart area for an embedded chart, you would have to refer to the chart in this manner:

Worksheets("Jan").ChartObjects("Chart 1").Chart.ChartArea.Interior. _
    ColorIndex = 4

In Excel 2010, you can instead use the Shapes collection:

Worksheets("Jan").Shapes("Chart 1").Chart.ChartArea.Interior.ColorIndex = 4

In any version of Excel, if a chart is on its own chart sheet, you don’t have to specify the container; you can simply refer to the Chart object:

Sheets("Chart1").ChartArea.Interior.ColorIndex = 4

Creating a Chart

In legacy versions of Excel, you used the Charts.Add command to add a new chart. You then specified the source data, the type of chart, and if the chart should be on a new sheet or embedded on an existing worksheet. The first three lines of the following code create a clustered column chart on a new chart sheet. The fourth line moves the chart back to be an embedded object in Sheet1:

Charts.Add
ActiveChart.SetSourceData Source:=Worksheets("Sheet1").Range("A1:E4")
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

If you plan to share your macros with people who still use Excel 2003, you should use the Charts.Add method. However, if your application will only be running in Excel 2010, you can use the new AddChart method. The code for the AddChart method can be as simple as the following:

' Create chart on the current sheet
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("A1:E4")
ActiveChart.ChartType = xlColumnClustered

Alternatively, you can specify the chart type, size, and location as part of the AddChart method, as described in the next section.

Specifying the Size and Location of a Chart

The AddChart method has additional parameters you can use to specify the type of chart, the chart’s location on the worksheet, and the size of the chart.

The location and size of a chart are specified in points (72 points = 1 inch). For example, the Top parameter requires the number of points from the top of row 1 to the top edge of the worksheet.

The following code creates a chart that roughly covers the range C11:J30:

Sub SpecifyLocation()
    Dim WS As Worksheet
    Set WS = Worksheets("Sheet1")
    WS.Shapes.AddChart(xlColumnClustered, _
        Left:=100, Top:=150, _
        Width:=400, Height:=300).Select
    ActiveChart.SetSourceData Source:=WS.Range("A1:E4")
End Sub

It would require a lot of trial and error to randomly figure out the exact distance in points to cause a chart to line up with a certain cell. Fortunately, you can ask VBA to tell you the distance in points to a certain cell. If you ask for the Left property of any cell, you find the distance to the top-left corner of that cell. You can also ask for the width of a range or the height of a range. For example, the following code creates a chart in exactly C11:J30:

Sub SpecifyExactLocation()
    Dim WS As Worksheet
    Set WS = Worksheets("Sheet1")
    WS.Shapes.AddChart(xlColumnClustered, _
        Left:=WS.Range("C11").Left, _
        Top:=WS.Range("C11").Top, _
        Width:=WS.Range("C11:J11").Width, _
        Height:=WS.Range("C11:C30").Height _
        ).Select
    ActiveChart.SetSourceData Source:=WS.Range("A1:E4")
End Sub

In this case, you are not moving the location of the Chart object; rather, you are moving the location of the container that contains the chart. In Excel 2010, it is either the ChartObject or the Shape object. If you try to change the actual location of the chart, you move it within the container. Because you can actually move the chart area a few points in either direction inside the container, the code will run, but you will not get the desired results.

To move a chart that has already been created, you can reference either ChartObject or the Shape and change the Top, Left, Width, and Height properties as shown in the following macro:

Sub MoveAfterTheFact()
    Dim WS As Worksheet
    Set WS = Worksheets("Sheet1")
    With WS.ChartObjects("Chart 9")
        .Left = WS.Range("C21").Left
        .Top = WS.Range("C21").Top
        .Width = WS.Range("C1:H1").Width
        .Height = WS.Range("C21:C25").Height
    End With
   End Sub

Later Referring to a Specific Chart

When a new chart is created, it is given a sequential name, such as Chart 1. If you select a chart and then look in the name box, you see the name of the chart. In Figure 13.4, the name of the chart is Chart 14. This does not mean that there are 14 charts on the worksheet. In this particular case, many individual charts have been created and deleted.

Figure 13.4 You can select a chart and look in the name box to find the name of the chart.

This means that on any given day that your macro runs, the Chart object might have a different name. If you need to reference the chart later in the macro, perhaps after you have selected other cells and the chart is no longer active, you might ask VBA for the name of the chart and store it in a variable for later use, as shown here:

Sub RememberTheName()
    Dim WS As Worksheet
    Set WS = Worksheets("Sheet1")
    WS.Shapes.AddChart(xlColumnClustered, _
        Left:=WS.Range("C11").Left, _
        Top:=WS.Range("C11").Top, _
        Width:=WS.Range("C11:J11").Width, _
        Height:=WS.Range("C11:C30").Height _
        ).Select
    ActiveChart.SetSourceData Source:=WS.Range("A1:E4")
    ' Remember the name in a variable
    ThisChartObjectName = ActiveChart.Parent.Name
    ' more lines of code…
    ' then later in the macro, you need to re-assign the chart
    With WS.Shapes(ThisChartObjectName)
        .Chart.SetSourceData Source:=WS.Range("A20:E24"), PlotBy:=xlColumns
        .Top = WS.Range("C26").Top
    End With
End Sub

In the preceding macro, the variable ThisChartObjectName contains the name of the chart object. This method works great if your changes will happen later in the same macro. However, after the macro finishes running, the variable will be out of scope, and you won’t be able to access the name later.

If you want to be able to remember a chart name, you could store the name in an out-of-the way cell on the worksheet. The first macro here stores the name in Cell Z1, and the second macro then later modifies the chart using the name stored in Cell Z1:

Sub StoreTheName()
    Dim WS As Worksheet
    Set WS = Worksheets("Sheet1")
    WS.Shapes.AddChart(xlColumnClustered, _
        Left:=WS.Range("C11").Left, _
        Top:=WS.Range("C11").Top, _
        Width:=WS.Range("C11:J11").Width, _
        Height:=WS.Range("C11:C30").Height _
        ).Select
    ActiveChart.SetSourceData Source:=WS.Range("A1:E4")
    Range("Z1").Value = ActiveChart.Parent.Name
End Sub

After the previous macro stored the name in Cell Z1, the following macro will use the value in Z1 to figure out which macro to change:

Sub ChangeTheChartLater()
    Dim WS As Worksheet
    Set WS = Worksheets("Sheet1")
    MyName = WS.Range("Z1").Value
    With WS.Shapes(MyName)
        .Chart.SetSourceData Source:=WS.Range("A20:E24"), PlotBy:=xlColumns
        .Top = WS.Range("C26").Top
    End With

End Sub

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

WS.ChartObjects(1).Chart.Interior.ColorIndex = 4

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

For each Cht in ActiveSheet.ChartObjects
    If Cht.TopLeftCell.Address = "$A$4" then
        Cht.Interior.ColorIndex = 4
    End if
Next Cht

Coding Commands from the Design Tab

With charts in Excel 2010, there are three levels of chart changes that correspond to the three Chart Tools tabs. The global chart settings—chart type and style—are on the Design tab. Selections from the built-in element settings appear on the Layout tab. You make micro-changes by using the Format tab. This section deals with changes on the Design tab.

Specifying a Built-In Chart Type

There are 73 built-in chart types in Excel 2010. To change a chart to one of the 73 types, you use the ChartType property. This property can either be applied to a chart or to a series within a chart. Here’s an example that changes the type for the entire chart:

ActiveChart.ChartType = xlBubble

To change the second series on a chart to a line chart, you use this:

ActiveChart.Series(2).ChartType = xlLine

Table 13.1 lists the 73 chart type constants that you can use to create various charts. The sequence of Table 13.1 matches the sequence of the charts in the Chart Type dialog.

Table 13.1 Chart Types for Use in VBA

Specifying a Template Chart Type

In “Creating a Chart Template” in Chapter 2, “Customizing Charts,” you learned how to create a custom chart template. This is a great technique for saving time when you are creating a chart with a lot of custom formatting.

A VBA macro can make use of a custom chart template, provided that you plan on distributing the custom chart template to each person who will run your macro.

In Excel 2010, you save custom chart types as CRTX files and stored them in the %appdata%MicrosoftTemplatesCharts folder.

To apply a custom chart type, you use the following:

ActiveChart.ApplyChartTemplate "MyChart.crtx"

If the chart template does not exist, VBA returns an error. If you would like Excel to simply continue without displaying a debug error, you can turn on an error handler before the code and turn it back on when you are done. Here’s how you do that:

On Error Resume Next
ActiveChart.ApplyChartTemplate ("MyChart.crtx")
On Error GoTo 0 ' that final character is a zero

Changing a Chart’s Layout or Style

Two galleries—the Chart Layout gallery and the Styles gallery—make up the bulk of the Design tab.

The Chart Layout gallery offers from 4 to 12 combinations of chart elements. These combinations are different for various chart types. When you look at the gallery shown in Figure 13.5, the ToolTips for the layouts show that the layouts are imaginatively named Layout 1 through Layout 10.

Figure 13.5 The built-in layouts for bar charts are numbered 1 through 10. For other chart types, you might have 4 to 12 layouts.

To apply one of the built-in layouts in a macro, you have to use the ApplyLayout method with a number from 1 through 12 to correspond to the built-in layouts. The following code will apply Layout 1 to the active chart:

ActiveChart.ApplyLayout 1

Clearly, to effectively use a built-in layout, you must have actually built a chart by hand and found a layout that you actually like.

As shown in Figure 13.6, the Styles gallery contains 48 styles. These layouts are also numbered sequentially, with Styles 1 through 8 in Row 1, Styles 9 through 16 in Row 2, and so on. These styles actually follow a bit of a pattern:

Figure 13.6 The built-in styles are numbered 1 through 48.

• Styles 1, 9, 17, 25, 33, and 41 (that is, the styles in column 1) are monochrome.

• Styles 2, 10, 18, 26, 34, and 42 (that is, the styles in column 2) use different colors for each point.

• All the other styles use hues of a particular theme color.

• Styles 1 through 8 use simple effects.

• Styles 9 through 17 use moderate effects.

• Styles 33 through 40 use intense effects.

• Styles 41 through 48 appear on a dark background.

If you are going to mix styles in a single workbook, you should consider staying within a single row or a single column of the gallery.

To apply a style to a chart, you use the ChartStyle property, assigning it a value from 1 to 48:

ActiveChart.ChartStyle = 1

The ChartStyle property changes the colors in the chart. However, a number of formatting changes from the Format tab do not get overwritten when you change the ChartStyle property. For example, in Figure 13.7, the second series previously had a glow applied and the third series had a clear glass bevel applied. Running the preceding code did not clear that formatting.

Figure 13.7 Setting the ChartStyle property does not override all settings.

To clear any previous formatting, you use the ClearToMatchStyle method:

ActiveChart.ChartStyle = 1
ActiveChart.ClearToMatchStyle

Using SetElement to Emulate Changes on the Layout Tab

As discussed in Chapter 2, the Layout tab contains a number of built-in settings. Figure 13.8 shows a few of the built-in menu items for the Legend tab. There are similar menus for each of the icons in the figure.

Figure 13.8 There are built-in menus similar to this one for each icon. If your choice is in the menu, the VBA code uses the SetElement method.

If you use a built-in menu item to change the titles, legend, labels, axes, gridlines, or background, it is probably handled in code that uses the SetElement method, which is new in Excel 2010.

SetElement does not work with the More choices at the bottom of each menu. It also does not work with the 3-D Rotation button. Other than that, you can use SetElement to change everything in the Labels, Axes, Background, and Analysis groups.

The macro recorder always works for the built-in settings on the Layout tab. If you don’t 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:

ActiveChart.SetElement msoElementLegendLeft

Table 13.2 shows all the available constants that you can use with the SetElement method. These are in roughly the same order as they appear on the Layout tab.

Table 13.2 Constants Available with SetElement

Changing a Chart Title Using VBA

The Layout tab’s built-in menus let you add a title above a chart, but they don’t offer you the ability to change the characters in a chart title or axis title.

In the user interface, you can simply double-click the chart title text and type a new title to change the title. Unfortunately, the macro recorder does not record this action.

To specify a chart title, you must type this code:

ActiveChart.ChartTitle.Caption = "My Chart"

Similarly, you can specify the axis titles by using the Caption property. The following code will change the axis title along the category axis:

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

Emulating Changes on the Format Tab

The Format tab offers many new options for formatting such as Glow, Reflection, and Soft Edge. To handle all of these new formats, Microsoft introduced the ChartFormat object.

When the ChartFormat was introduced in Excel 2007, the macro recorder would not record changes on the Format tab, making it tough to figure out how to write code to implementthe new formatting. This gap in the macro recorder has been fixed in Excel 2010.

Using the Format Method to Access New Formatting Options

Excel 2010 introduces a new object called the ChartFormat object. This object 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 13.3 lists a sampling of chart elements that can be formatted 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 give examples of how to set up each type of format.

Table 13.3 Chart Elements to Which Formatting Applies

Changing an Object’s Fill

As shown in Figure 13.9, the Shape Fill drop-down on the Format tab allows 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:

Dim cht As Chart

Figure 13.9 Fill options include a solid color, a gradient, a texture, or a picture.

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

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

The standard color chooser drop-down in Excel offers six theme colors and then five variations of those colors ranging from light to dark. To lighten the theme color, use a positive value between 0 and 1 for the .TintAndShade property:

ser.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6
ser.Format.Fill.ForeColor.TintAndShade = 0.2

To darken a color, choose a negative value between 0 and 1.

To apply a built-in texture, you use the PresetTextured method. The following code applies a green marble texture to the second series, but there are 20 different textures that can be applied:

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 filename of an image on the computer, as in the following example:

Sub FormatWithPicture()
    Dim cht As Chart
    Dim ser As Series
    Set cht = ActiveChart
    Set ser = cht.SeriesCollection(1)
    MyPic = "C:dollarbills.jpg"
    ser.Format.Fill.UserPicture (MyPic)
End Sub

Microsoft had removed patterns as fills from Excel 2007. There was a lot of outcry from customers who used patterns to differentiate columns that were printed on monochrome printers. To apply a pattern, use the .Patterned method. Patterns have a type such as msoPatternPlain and also a foreground and background color. This code will create red dark vertical lines on a white background:

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 2010 offers 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:

Sub TwoColorGradient()
    Dim cht As Chart
    Dim ser As Series
    Set cht = ActiveChart
    Set ser = cht.SeriesCollection(1)
    MyPic = "C:PodCastTitle1.jpg"
    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 or 1 for lighter gradients).

When using the PresetGradient method, you specify a direction, a style (1 through 4), and the type of gradient (for example, msoGradientBrass, msoGradientLateSunset, or msoGradientRainbow). Again, as you are typing this code in the VBA 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 for a line, such as the color, arrows, dash style, and so on.

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

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:

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

Formatting Glow Settings

To create a glow, you have to specify a color and a radius. The radius value can be from 1 to 20. A radius of 1 is barely visible, and a radius of 20 is often way too thick.

The following macro adds a line around the title and adds a glow around that line:

Sub AddGlowToTitle()
    Dim cht As Chart
    Set cht = ActiveChart
    cht.ChartTitle.Format.Line.ForeColor.RGB = RGB(255, 255, 255)
    cht.ChartTitle.Format.Line.DashStyle = msoLineSolid
    cht.ChartTitle.Format.Glow.Color.ObjectThemeColor = msoThemeColorAccent6
    cht.ChartTitle.Format.Glow.Radius = 8
End Sub

Formatting Shadow Settings

A shadow is composed of a color, a transparency, and the number of points by which the shadow should be offset from the object. If you increase the number of points, it appears that the object is farther from the surface of the chart. The horizontal offset is known as OffsetX, and the vertical offset is known as OffsetY.

The following macro adds a light blue shadow to the box surrounding a legend:

Sub FormatShadow()
    Dim cht As Chart
    Set cht = ActiveChart
    With cht.Legend.Format.Shadow
        .ForeColor.RGB = RGB(0, 0, 128)
        .OffsetX = 5
        .OffsetY = -3
        .Transparency = 0.5
        .Visible = True
    End With
End Sub

Formatting Reflection Settings

No chart elements can have reflections applied. The Reflection settings on the Format tab are constantly grayed out when a chart is selected. Similarly, the ChartFormat object does not have a reflection object.

Formatting Soft Edges

There are six levels of soft edge settings. The settings feather the edges by 1, 2.5, 5, 10, 25, or 50 points. The first setting is barely visible. The biggest settings are usually larger than most of the chart elements you are likely to format.

Microsoft says that the following is the proper syntax for SoftEdge:

Chart.Series(1).Points(i).Format.SoftEdge.Type = msoSoftEdgeType1

However, msoSoftEdgeType1 and words like it are really variables defined by Excel. To try a cool trick, go to the VBA editor and open the Immediate pane by pressing Ctrl+G. In the Immediate pane, type Print msoSoftEdgeType2 and press Enter. The Immediate window tells you that using this word is equivalent to typing 2. So, you could either use msoSoftEdgeType2 or the value 2.

If you use msoSoftEdgeType2, your code will be slightly easier to understand than if you use simply 2. However, if you hope to format each point of a data series with a different format, you might want to use a loop such as this one, in which case it is far easier to use just the numbers 1 through 6 than msoSoftEdgeType1 through msoSoftEdgeType6, as shown in this macro:

Sub FormatSoftEdgesWithLoop()
    Dim cht As Chart
    Dim ser As Series
    Set cht = ActiveChart
    Set ser = cht.SeriesCollection(1)
    For i = 1 To 6
        ser.Points(i).Format.SoftEdge.Type = i
    Next i
End Sub

Formatting 3-D Rotation Settings

The 3-D settings handle three different menus on the Format tab. In the Shape Effects drop-down, settings under Preset, Bevel, and 3-D are all actually handled by the ThreeD object in the ChartFormat object. This section discusses settings that affect the 3-D rotation. The next section discusses settings that affect the bevel and 3-D format.

The methods and properties that can be set for the ThreeD object are very broad. In fact, the 3-D settings in VBA include more preset options than do the menus on the Format tab.

Figure 13.10 shows the presets available in the 3-D Rotation fly-out menu.

To apply one of the 3-D rotation presets to a chart element, you use the SetPresetCamera method, as shown here:

Sub Assign3DPreset()
    Dim cht As Chart
    Dim shp As Shape
    Set cht = ActiveChart
    Set shp = cht.Shapes(1)
    shp.ThreeD.SetPresetCamera msoCameraIsometricLeftDown
End Sub

Figure 13.10 Whereas the 3-D Rotation menu offers 25 presets, VBA offers 62 presets.

Table 13.4 lists all the possible SetPresetCamera values. If the first column indicates that it is a bonus or an Excel 2003 style, the value is a preset that is available in VBA but was not chosen by Microsoft to be included in the 3-D Rotation fly-out menu.

Table 13.4 3-D Preset Formats and Their VBA Constant Values

If you prefer not to use the presets, you can explicitly control the rotation around the x-, y-, or z-axis. You can use the following properties and methods to change the rotation of an object:

• RotationX—Returns or sets the rotation of the extruded shape around the x-axis, in degrees. This can be a value from -90 through 90. A positive value indicates upward rotation; a negative value indicates downward rotation.

• RotationY—Returns or sets the rotation of the extruded shape around the y-axis, in degrees. Can be a value from -90 through 90. A positive value indicates rotation to the left; a negative value indicates rotation to the right.

• RotationZ—Returns or sets the rotation of the extruded shape around the z-axis, in degrees. Can be a value from -90 through 90. A positive value indicates upward rotation; a negative value indicates downward rotation.

• IncrementRotationX—Changes the rotation of the specified shape around the x-axis by the specified number of degrees. You specify an increment from -90 to 90. Negative degrees tip the object down, and positive degrees tip the object up.

• IncrementRotationY—Changes the rotation of the specified shape around the y-axis by the specified number of degrees. A positive value tilts the object left, and a negative value tips the object right.

• IncrementRotationZ—Changes the rotation of the specified shape around the z-axis by the specified number of degrees. A positive value tilts the object left, and a negative value tips the object right.

• IncrementRotationHorizontal—Changes the rotation of the specified shape horizontally by the specified number of degrees. You specify an increment from -90 to 90 to specify how much (in degrees) the rotation of the shape is to be changed horizontally. A positive value moves the shape left; a negative value moves it right.

• IncrementRotationVertical—Changes the rotation of the specified shape vertically by the specified number of degrees. You specify an increment from -90 to 90 to specify how much (in degrees) the rotation of the shape is to be changed horizontally. A positive value moves the shape left; a negative value moves it right.

• ResetRotation—Resets the extrusion rotation around the x-axis and the y-axis to 0 so that the front of the extrusion faces forward. This method doesn’t reset the rotation around the z-axis.

Changing the Bevel and 3-D Format

There are 12 presets in the Bevel fly-out menu. These presets affect the bevel on the top face of the object. Usually in charts you see the top face; however, there are some bizarre rotations of a 3-D chart where you see the bottom face of charting elements.

The Format Data Series dialog contains the same 12 presets as the Bevel fly-out but allows you to apply the preset to the top or bottom face. You can also control the width and height of the bevel. The VBA properties and methods correspond to the settings on the 3-D Format category of the Format Data Series dialog (see Figure 13.11).

Figure 13.11 You can control the 3-D Format settings, such as bevel, surface, and lighting.

You set the type of bevel by using the BevelTopType and BevelBottomType properties. You can further modify the bevel type by setting the BevelTopInset value to set the width and the BevelTopDepth value to set the height. The following macro adds a bevel to the columns of Series 1:

Sub AssignBevel()
    Dim cht As Chart
    Dim ser As Series
    Set cht = ActiveChart
    Set ser = cht.SeriesCollection(1)
    ser.Format.ThreeD.Visible = True
    ser.Format.ThreeD.BevelTopType = msoBevelCircle
    ser.Format.ThreeD.BevelTopInset = 16
    ser.Format.ThreeD.BevelTopDepth = 6
End Sub

The 12 possible settings for the bevel type are shown in Table 13.5; these settings correspond to the thumbnails shown in Figure 13.12. To turn off the bevel, you use msoBevelNone.

Figure 13.12 Samples of the 12 bevel types listed in Table 13.5.

Table 13.5 Bevel Types

Usually, the accent color used in a bevel is based on the color used to fill the object. If you would like control over the extrusion color, however, you first specify that the extrusion color type is custom and then specify either a theme accent color or an RGB color, as in the following example:

ser.Format.ThreeD.ExtrusionColorType = msoExtrusionColorCustom
' either use this:
ser.Format.ThreeD.ExtrusionColor.ObjectThemeColor = msoThemeColorAccent1
' or this:
ser.Format.ThreeD.ExtrusionColor.RGB = RGB(255, 0, 0)

You use the Depth property to control the amount of extrusion in the bevel, and you specify the depth in points. Here’s an example:

ser.Format.ThreeD.Depth = 5

For the contour, you can specify either a color and a size of the contour or both. You can specify the color as an RGB value or a theme color. You specify the size in points, using the ContourWidth property. Here’s an example:

ser.Format.ThreeD.ContourColor.RGB = RGB(0, 255, 0)
ser.Format.ThreeD.ContourWidth = 10

The Surface drop-downs are controlled by the following properties:

• PresetMaterial—This contains choices from the Material drop-down.

• PresetLighting—This contains choices from the Lighting drop-down.

• LightAngle—This controls the angle from which the light is shining on the object.

Figure 13.13 shows the Material drop-down menu from the 3-D category of the Format dialog box. Although the drop-down offers 11 settings, it appears that Microsoft designed a 12th setting in the object model. It is not clear why Microsoft does not offer the SoftMetal style in the dialog box, but you can use it in VBA. There are also three legacy styles in the object model that are not available in the Format dialog box. In theory, the new Plastic2 material is better than the old Plastic material. The settings for each thumbnail are shown in Table 13.6.

Table 13.6 VBA Constants for Material Types

Figure 13.13 Samples of the 11 material types shown in Table 13.6.

In Excel 2003, the material property was limited to matte, metal, plastic, and wire frame. Microsoft apparently was not happy with the old matte, metal, and plastic settings. It left those values in place to support legacy charts but created the new Matte2, Plastic2, and Metal2 settings. These settings are actually available in the dialog box.

In VBA, you are free to use either the old or the new settings. The columns in Figure 13.14 compare the new and old settings. The final column is for the SoftMetal setting that Microsoft left out of the Format dialog box. This was probably an aesthetic decision instead of an “oh no; this setting crashes the computer” decision. You can feel free to use msoMaterialSoftMetal to create a look that has a subtle difference from charts others create using the settings in the Format dialog box.

Figure 13.14 Comparison of some new and old material presets.

Figure 13.15 shows the Lighting drop-down menu from the 3-D category of the Format dialog box. The drop-down offers 15 settings. The Object Model offers these 15 settings, plus 13 legacy settings from the Excel 2003 Lighting toolbar. The settings for each of these thumbnails are shown in Table 13.7.

Figure 13.15 Samples of the 15 lighting types shown in Table 13.7.

Table 13.7 VBA Constants for Lighting Types

Automating Changes in the Format Series Dialog

There is a huge gap in the Excel 2010 Ribbon interface. Somewhere between the global changes on the Design tab and the set element changes on the Layout tab, Microsoft offers no big icons to format the individual data series. Depending on the chart type, the Format Series dialog box holds special settings that can dramatically impact the look of your chart.

There are a few ways to access the Format Series dialog:

• Right-click a series in the chart and select Format Series from the context menu.

• From the first drop-down in either the Layout or Format tabs, choose the item that you want to format (for example, choose Series 1 from this drop-down to format Series 1). Then click the Format Selection button immediately below the drop-down.

The special settings appear in the Series Options category of the Format dialog box. The following are some of the settings you can control:

• Gap Width and Separation—Control whether the columns in a column chart should be touching each other, as in a histogram, or separated.

• Plot on Second Axis—Specifies that a series should be plotted on a secondary axis. This is useful when the magnitude of one series does not match the magnitude of another series.

• Angle of First Slice—Rotates pie and doughnut charts. Other settings for the round charts control features such as explosion and hole size.

• Bar of Pie and Pie of Pie—Control which categories appear in the secondary chart in these combination charts.

• Bubble Size—Controls how the bubbles are sized in a bubble chart.

• Surface and Radar—Control certain aspects of these chart types.

The following sections discuss the various options you can control in the Series Options dialog.

Controlling Gap Width and Series Separation in Column and Bar Charts

Typically, the individual bars in a bar or column chart are separated by gaps. When scientists create histograms, they want to eliminate the gaps between bars.

Excel offers the GapWidth property, whose value can range from 0 to 500 to represent 0 percent to 500 percent. Figure 13.16 shows a typical chart and a chart where the gap width has been reduced to 25 percent.

Figure 13.16 In the bottom chart, the gap width has been reduced from the default to 25 percent.

The gap width setting applies to chart groups that contain bar or column markers. It can also be used to format the volume markers in volume-high-low-close charts or volume-open-high-low-close charts.

The following macro changes the gap width to 25 percent:

Sub FormatGapWidth()
    Dim cht As Chart
    Set cht = ActiveChart
    cht.ChartGroups(1).GapWidth = 25
    cht.ChartGroups(1).VaryByCategories = True
End Sub

When you create a clustered column chart with two series, the columns for each data point touch, as shown in the top chart in Figure 13.17. You can use the Overlap property to cause the columns to overlap. Values from 1 to 100 cause the columns to overlap anywhere from 1 percent to 100 percent. Values from -1 to -100 cause separations between the data points.

The middle chart in Figure 13.17 shows a 50 percent overlap. The bottom chart shows a –100 percent overlap.

Figure 13.17 The middle chart has an overlap, and the bottom chart has a negative overlap (that is, a separation).

The following macro creates a 25 percent overlap between the series:

Sub FormatOverLap()
    Dim cht As Chart
    Set cht = ActiveChart
    cht.ChartGroups(1).Overlap = 25
End Sub

The Overlap property applies to clustered column and clustered bar charts. It should be set to 100 percent for the volume series in stock charts.

Moving a Series to a Secondary Axis

You might want to communicate data on a chart where the series are different orders of magnitude. In the top chart in Figure 13.18, the first two series represent sales and profit and show numbers in the hundreds of thousands. The third series is a profit percentage where the values are between 20 percent and 80 percent. When you plot these three series on a column chart, the columns for the profit percentage series will be so small that they will not be seen.

The solution is to plot the third series on a secondary axis. The left axis will continue to show hundreds of thousands, but the right axis scales to show percentages from 0 percent to 100 percent.

To move a series to the secondary axis, you use the AxisGroup property:

ActiveChart.SeriesCollection(3).AxisGroup = xlSecondary

Using a secondary axis solves one problem but introduces a new problem: How will the reader know that it is the profit percentage series which is plotted against the secondary axis? You can change the chart type of the third series from a column to a line. You can change the ChartType property for an individual series as follows:

ActiveChart.SeriesCollection(3).ChartType = xlLineMarkers

To further help the chart reader, you can change the font color of the tick labels for the secondary axis to match the line color of the third series. In the following macro, the font color for the secondary axis is changed to match the fill color of the third series (see the bottom chart in Figure 13.18):

Sub MoveToSecondaryAxis()
    Dim cht As Chart
    Dim ser As Series
    Dim ax As Axis
    Set cht = ActiveChart
    Set ser = cht.SeriesCollection(3)
    ser.AxisGroup = xlSecondary
    ser.ChartType = xlLineMarkers
    Set ax = cht.Axes(xlValue, xlSecondary)
    ax.TickLabels.Font.Color = ser.Format.Fill.ForeColor
End Sub

Unfortunately, this is a snapshot type of change. If you later change the theme or the color scheme of the chart, you have to change the tick label color to match the new Series 3 color.

Figure 13.18 In the top chart, the third series is too small to be seen. In the bottom chart, a secondary axis, a new chart type, and an axis font color solves many problems.

Spinning and Exploding Round Charts

Pie and doughnut charts have rotation and explosion properties. In a typical data series, there might be a few tiny pie slices at the end of the series. These pie slices typically appear in the back of the chart. If you move them around to the front of the chart, they are more visible, and there is more room for the data labels to appear outside the chart.

You control the angle of the first slice of a pie by using the FirstSliceAngle property of the ChartGroup object. Valid values range from 0 to 360, representing rotations of 0 to 360 degrees. In the bottom-left chart in Figure 13.19, the original chart was rotated 159 degrees, using the following macro:

Sub RotateChart()
    ' Bottom Left Chart in Figure 13.19
    Dim cht As Chart
    Set cht = ActiveChart
    cht.ChartGroups(1).FirstSliceAngle = 159
End Sub

You can also explode pie and doughnut charts. In an exploded view, the individual wedges are separated from each other. You use the Explosion property to change the explosion effect. Valid values range from 0 to 400, representing 0 to 400 percent. With explosions, tiny values go a long way. The top-right chart in Figure 13.19 represents a 22 percent explosion and is created with this macro:

Sub ExplodeChart()
    ' Top Right Chart in Figure 13.19
    Dim cht As Chart
    Set cht = ActiveChart
    cht.ChartGroups(1).FirstSliceAngle = 159
    cht.ChartGroups(1).Explosion = 22
End Sub

Figure 13.19 You can rotate or explode pie charts to bring the small slices into view.

Sometimes, a better effect is to explode just a single slice of a pie. You can apply the Explosion property to a single data point. In the bottom-right chart in Figure 13.19, only the Ads slice is exploded. Note that the macro then adjusts the positioning of the adjoining data labels so that they can be seen around the exploded slice:

Sub ExplodeOneSlice()
    ' Bottom Right Chart in Figure 13.19
    Dim cht As Chart
    Dim ser As Series
    Dim poi As Point
    Dim dl As DataLabel
    Set cht = ActiveChart
    Set ser = cht.SeriesCollection(1)
    cht.ChartGroups(1).FirstSliceAngle = 114
    ' Explode one slice
    Set poi = ser.Points(4)
    poi.Explosion = 22
    ' fix the labels
    Set dl = ser.Points(3).DataLabel
    dl.Left = dl.Left + 30
    dl.Top = dl.Top - 50
    Set dl = ser.Points(5).DataLabel
    dl.Left = dl.Left + 10
    dl.Top = dl.Top + 20
End Sub

You can also control the hole size in the center of a doughnut chart. A typical doughnut chart starts out with a hole size that is 50 percent of the doughnut. You use the DoughnutHoleSize property to adjust this from 90 percent to 10 percent, using values of 90 to 10. The charts in Figure 13.20 show doughnut hole sizes of 70 percent on the top and 10 percent on the bottom. The following macro adjusts the doughnut hole size to 70 percent:

Sub ExplodeChart()
    Dim cht As Chart
    Set cht = ActiveChart
    cht.ChartGroups(1).DoughnutHoleSize = 70
End Sub

Figure 13.20 You can change the hole size in doughnut charts.

Controlling the Bar of Pie and Pie of Pie Charts

When your data contains many small pie slices and you care about the differences in those small slices, you can ask for the small slices to be plotted in a secondary pie or a secondary bar chart.

→ For complete details on these unique chart types, read “Using a Pie of Pie Chart” in Chapter 4. These chart types are found in the Pie category.

As these charts include two charts inside the chart area, Excel offers many additional settings for controlling the size and placement of each chart in relation to the other. There are also a myriad of ways to determine which pie wedges are represented in the secondary chart. You have complete control of these settings in VBA. You might consider the following:

• How do you decide which wedges are reported in the smaller pie? Excel offers choices to move the last n slices, move all slices smaller than n percent, or to move all slices smaller than a particular value. Excel also offers the custom method for moving the pie slices.

• Do you want leader lines from the “other” slice to the secondary plot? These are generally a good idea, but you can turn them off or even format them, if desired.

• How large should the secondary plot be compared to the first plot? Say that you are preparing charts for a meeting to discuss which product lines should be discontinued. In this case, the focus really is on the tiny wedges, and you might want to have the secondary plot be as large as or larger than the original pie.

• How wide should the gap be between the plots?

The following sections discuss how to adjust each of these settings with VBA.

Using a Rule to Determine Which Wedges Are in the Secondary Plot

Excel offers three built-in rules for determining which pie slices should be in the secondary plot. You can specify that all slices smaller than a certain percentage should be in the secondary plot, that all slices smaller than a certain value should be in the secondary plot, or even that the last n slices should be in the secondary plot.

You specify which of these rules to use with the SplitType property and then you specify a SplitValue setting to indicate where the split should occur.

In Figure 13.21, the top chart used Excel’s defaults to show the last four points in the secondary plot. You could use any of these methods to create the bottom chart:

Figure 13.21 In the bottom chart, 80 percent of the slices appear in the secondary chart.

• You could specify a split type of xlSplitByValue and then indicate that any values less than 10 should be in the secondary plot.

• You could specify a split type of xlSplitByPercentValue and then indicate that any values less than 5 percent should be in the secondary plot.

• You could specify a split type of xlSplitByPosition and then indicate that the last eight values should be in the secondary plot.

Any one of these three macros could be used to create the second chart in Figure 13.21:

Sub SmallerThan10ToPlot2()
    Dim cht As Chart
    Dim chtg As ChartGroup
    Set cht = ActiveChart
    Set chtg = cht.ChartGroups(1)
    ' Anything less than 10 to second group
    cht.ChartGroups(1).SplitType = xlSplitByValue
    ActiveChart.ChartGroups(1).SplitValue = 10
End Sub

Sub SmallerThan10PctToPlot2()
    Dim cht As Chart
    Dim chtg As ChartGroup
    Set cht = ActiveChart
    Set chtg = cht.ChartGroups(1)
    ' Anything less than 10% to 2nd plot
    chtg.SplitType = xlSplitByPercentValue
    chtg.SplitValue = 10
End Sub

Sub Last8ToPlot2()
    Dim cht As Chart
    Dim chtg As ChartGroup
    Set cht = ActiveChart
    Set chtg = cht.ChartGroups(1)
    ' Send last 8 slices to secondary plot
    chtg.SplitType = xlSplitByPosition
    chtg.SplitValue = 8
End Sub

Defining Specific Categories to Be in the Secondary Plot

You can choose to have complete control over which slices of a pie appear in a secondary chart. If you are trying to decide among three specific products to discontinue, for example, you can move all three of those products to the secondary pie.

To do this with a macro, you first set SplitType to xlSplitByCustomSplit. You can then use the SecondaryPlot property on individual data points. A value of 0 shows the data point in the left pie. A value of 1 sends the data point to the secondary pie.

Because you aren’t sure how many items Excel will send to the secondary pie by default, you can write a macro such as the following to first loop through all data points and reset them to the primary pie, and then move three specific slices to the secondary pie:

Sub CustomPieofPie()
    Dim cht As Chart
    Dim chtg As ChartGroup
    Dim ser As Series
    Dim poi As Point
    Set cht = ActiveChart
    Set chtg = cht.ChartGroups(1)
    Set ser = cht.SeriesCollection(1)
    chtg.SplitType = xlSplitByCustomSplit
    ' Move all slices to first plot
    For Each poi In ser.Points
        poi.SecondaryPlot = 0
    Next poi
    ' Move points 1, 6, 10 to secondary plot
    ser.Points(2).SecondaryPlot = 1
    ser.Points(6).SecondaryPlot = 1
    ser.Points(10).SecondaryPlot = 1
End Sub

Figure 13.22 shows the results. This figure shows a bar of pie chart with three specific wedges moved to the secondary plot.

Figure 13.22 Instead of focusing on the smallest slices, this bar of pie chart focuses on three particular products.

Controlling the Gap, Size, and Lines of a Secondary Plot

You adjust the gap between an original pie and a secondary plot by using the GapWidth property, whose values range from 0 to 500. In Figure 13.23, the bottom chart has a gap of 500, and the middle chart has a gap of 0.

You can turn on or off the two leader lines extending from the main pie to the secondary plot by setting the HasSeriesLines property to True or False. To format those lines, you use the Format property of the SeriesLines object. The following macro moves the secondary plot to the maximum distance and changes the series lines to a dash/dot style:

Sub MindTheGap()
    Dim chtg As ChartGroup
    Set chtg = ActiveChart.ChartGroups(1)
    chtg.GapWidth = 500
    chtg.HasSeriesLines = True
    chtg.SeriesLines.Format.Line.DashStyle = msoLineDashDot
End Sub

The size of the secondary plot usually starts off at 75 percent of the original pie. You can use the SecondPlotSize property to change the plot size from 5 to 200, representing 5 percent to 200 percent of the original pie chart. Figure 13.24 shows charts with secondary plot sizes of 75 percent, 5 percent, and 200 percent.

Figure 13.23 You use the GapWidth property to move the secondary plot closer to or farther from the main pie.

Figure 13.24 You can shift the focus toward or away from the secondary plot by adjusting its size.

The following macro adjusts the size of the secondary plot to 50 percent of the size of the main pie:

Sub ChangeSize()
    Dim chtg As ChartGroup
    Set chtg = ActiveChart.ChartGroups(1)
    chtg.SecondPlotSize = 50
End Sub

Setting the Bubble Size

The bubble chart type includes an incredibly misleading setting that you should always avoid. A bubble chart places circles at particular x and y coordinates. The sizes of the circles are determined by the values specified in the third column of the data series.

By default, Excel scales the size of the circle so that the area of the circle is proportionate to the data. This is an appropriate choice. However, you can override this setting to say that the size represents the width of the circle. This always results in a misleading chart.

For example, in the upper-left chart in Figure 13.25, the smallest circle has a value of 1, and the largest circle has a value of 4. Because this chart uses the xlSizeIsArea setting, the bottom-left circle is four times larger than the top-right circle. The bottom-left chart plots the same data but uses the xlSizeIsWidth setting. With this setting, your circles will be completely out of scale.

Figure 13.25 The bottom-left chart uses xlSizeIsWidth and is misleading. The bottom-right chart shows a negative-sized circle, represented by a circle with no fill.

A circle with a width of 1 inch has an area of 0.785 square inches, according to =PI()*(1/2)^2. A circle with a width of 4 inches has an area of 12.56 square inches, according to =PI()*(4/2)^2. This means that with the xlSizeIsWidth setting, the bottom-left circle is 16 times larger than the top-right circle.

You can also choose to scale the circles from 0 to 300 percent by changing the BubbleScale property from 0 to 300. The top-left chart in Figure 13.25 has a 100 percent scale. The top-right chart uses a 50 percent scale. The bottom-right chart uses a 300 percent scale.

Finally, you can specify whether Excel should show negative-sized circles on a chart. In the lower-right chart in Figure 13.25, the –2 in the upper quadrant is represented by a circle with a white fill instead of a circle with a blue fill.

The following macro demonstrates some of the settings available for bubble charts:

Sub BubbleSettings()
    Dim chtg As ChartGroup
    Set chtg = ActiveChart.ChartGroups(1)
    ' Never use the following setting
    ' chtg.SizeRepresents = xlSizeIsWidth
    chtg.SizeRepresents = xlSizeIsArea
    chtg.BubbleScale = 50
    chtg.ShowNegativeBubbles = True
End Sub

Controlling Radar and Surface Charts

A couple minor settings affect surface charts and radar charts. A default surface chart uses contour shading within each color band to give a 3-D feeling. You can turn this off in a surface chart by using the Has3DShading property. In Figure 13.26, the bottom-left chart has the shading turned off.

Radar charts typically have the name of each category at the end of the axis. You can turn this off by setting the HasRadarAxisLabels property to False. The bottom-right chart in Figure 13.26 shows this setting.

The code for turning off the Has3DShading and HasRadarAxisLabels properties follows:

Sub FormatSurface()
    Dim chtg As ChartGroup
    Set chtg = ActiveChart.ChartGroups(1)
    chtg.Has3DShading = False
End Sub
Sub FormatRadar()
    Dim chtg As ChartGroup
    Set chtg = ActiveChart.ChartGroups(1)
    chtg.HasRadarAxisLabels = False
End Sub

Figure 13.26 The bottom charts reflect removing the 3-D contour (left) and radar axis labels (right).

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 filename 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 will work on most computers.

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

Sub ExportChart()
    Dim cht As Chart
    Set cht = ActiveChart
    cht.Export Filename:="C:Chart.gif", Filtername:="GIF"
End Sub

Creating a Dynamic Chart in a UserForm

With the ability to export a chart to a graphic file, you also have the ability to load a graphic file into an Image control in a UserForm. This means you can create a dialog box in which someone can dynamically control values used to plot a chart.

To create the dialog shown in Figure 13.27, you follow these steps:

Figure 13.27 This dialog box is a VBA UserForm displaying a chart. The chart redraws based on changes to the dialog controls.

1. In the VBA window, select Insert, UserForm. In the Properties pane, rename the form frmChart.

2. Resize the UserForm.

3. Add a large Image control to the UserForm.

4. Add two spin buttons named sbX and sbY. Set them to have a minimum of 1 and a maximum of 5.

5. Add a Label3 control to display the formula.

6. Add a command button labeled Close.

7. Enter this code in the code window behind the form:

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub sbX_Change()
    MyPath = ThisWorkbook.Path & Application.PathSeparator & "Chart.gif"
    Worksheets("Surface").Range("O2").Value = Me.sbX.Value
    Worksheets("Surface").Shapes("Chart 1").Chart.Export MyPath
    Me.Label3.Caption = Worksheets("Surface").Range("O4").Value
    Me.Image1.Picture = LoadPicture(MyPath)
End Sub

Private Sub sbY_Change()
    MyPath = ThisWorkbook.Path & Application.PathSeparator & "Chart.gif"
    Worksheets("Surface").Range("O3").Value = Me.sbY.Value
    Worksheets("Surface").Shapes("Chart 1").Chart.Export MyPath
    Me.Label3.Caption = Worksheets("Surface").Range("O4").Value
    Me.Image1.Picture = LoadPicture(MyPath)
End Sub

Private Sub UserForm_Initialize()
    MyPath = ThisWorkbook.Path & Application.PathSeparator & "Chart.gif"
    Me.sbX = Worksheets("Surface").Range("O2").Value
    Me.sbY = Worksheets("Surface").Range("O3").Value
    Me.Label3.Caption = Worksheets("Surface").Range("O4").Value
    Worksheets("Surface").Shapes("Chart 1").Chart.Export MyPath
    Me.Image1.Picture = LoadPicture(MyPath)
End Sub

8. Use Insert, Module to add a Module1 component with this code:

Sub ShowForm()
    frmChart.Show
End Sub

As someone changes the spin buttons in the UserForm, Excel writes new values to the worksheet. This causes the chart to update. The UserForm code then exports the chart and displays it in the UserForm (refer to Figure 13.27).

Creating Pivot Charts

A pivot chart is a chart that uses a pivot table as the underlying data source. As I lamented in Chapter 8, “Creating and Using Pivot Charts,” pivot charts don’t have the cool “show pages” functionality that regular pivot tables have. You can overcome this problem with a quick VBA macro that creates a pivot table and then a pivot chart based on the pivot table. The macro then adds the customer field to the page field of the pivot table. It then loops through each customer and exports the chart for each customer.

In Excel 2010, you first create a pivot cache by using the PivotCache.Create method. You can then define a pivot table based on the pivot cache. The usual procedure is to turn off pivot table updating while you add fields to the pivot table. Then you update the pivot table to have Excel perform the calculations.

It takes a bit of finesse to figure out the final range of the pivot table. If you have turned off the column and row totals, the chartable area of the pivot table starts one row below the PivotTableRange1 area. You have to resize the area to include one fewer row to make your chart appear correctly.

After the pivot table is created, you can switch back to the Charts.Add code discussed earlier in this chapter. You can use any formatting code to get the chart formatted as you desire.

The following code creates a pivot table and a single pivot chart that summarize revenue by region and product:

Figure 13.28 shows the resulting chart and pivot table.

Figure 13.28 VBA creates a pivot table and then a chart from the pivot table.

Creating Data Bars with VBA

The data bars in Excel 2010 now allow for negative values of a different color, solid or gradient fills, explicit min and max values, and even data bars that go right to left. The latter setting makes it easy to create comparative bar charts.

Figure 13.30 shows two sets of data bars created by VBA.

Figure 13.30 Two sets of data bars.

To add default data bars, use this code:

Range("C2:C11").FormatConditions.AddDataBar

You will want to customize the data bars by changing color, gradient, border color, and scale. Thus, it is easiest to assign the data bars to an object variable:

Dim DB As Databar
With Range("C2:C11")
    .FormatConditions.Delete
    ' Add the data bars
    Set DB = .FormatConditions.AddDatabar()
End With

The example in column C of Figure 13.30 includes negative data bars of a contrasting color, a horizontal axis in the center of the cell, a black border around the data bars, and a custom scale from -600 to +600. The code to create that data bar is as follows:

Sub DataBar()
' Add a Data bar
' Include negative data bars
' Control the min and max point
'
    Dim DB As Databar
    With Range("C2:C11")
        .FormatConditions.Delete
        ' Add the data bars
        Set DB = .FormatConditions.AddDatabar()
    End With

    ' Set the lower limit
    DB.MinPoint.Modify newtype:=xlConditionFormula, NewValue:="-600"
    DB.MaxPoint.Modify newtype:=xlConditionValueFormula, NewValue:="600"

    ' Change the data bar to Green
    With DB.BarColor
        .Color = RGB(0, 255, 0)
        .TintAndShade = -0.15
    End With

    ' All of this is new in Excel 2010
    With DB
        ' Use a gradiant
        .BarFillType = xlDataBarFillGradient
        ' Left to Right for direction of bars
        .Direction = xlLTR
        ' Assign a different color to negative bars
        .NegativeBarFormat.ColorType = xlDataBarColor
        ' Use a border around the bars
        .BarBorder.Type = xlDataBarBorderSolid
        ' Assign a different border color to negative
        .NegativeBarFormat.BorderColorType = xlDataBarSameAsPositive
        ' All borders are solid black
        With .BarBorder.Color
            .Color = RGB(0, 0, 0)
        End With
        ' Axis where it naturally would fall, in black
        .AxisPosition = xlDataBarAxisAutomatic
        With .AxisColor
            .Color = 0
            .TintAndShade = 0
        End With
        ' Negative bars in red
        With .NegativeBarFormat.Color
            .Color = 255
            .TintAndShade = 0
        End With
        ' Negative borders in red
    End With

End Sub

The next macro creates two sets of simpler data bars. There is no need to format the axis or the negative bar color since these are not ever going to be present in this dataset.

The main difference is that the bars on the left have a .Direction property of xlRTL, which stands for right to left:

Creating Sparklines with VBA

Regular sparklines are relatively easy to create with VBA:

Dim SG as SparklineGroup
FinalRow = Cells(Rows.Count, 6).End(xlUp).Row
Set SG = WSL.Range("B2:D2").SparklineGroups.Add( _
    Type:=xlSparkLine, _
    SourceData:="Data!D2:F" & FinalRow

Figure 13.31 shows a set of simple sparklines created by VBA.

Figure 13.31 Simple sparklines don’t communicate information about the min and max.

However, if you’ve read about sparklines in Edward Tufte’s book, Beautiful Evidence, then you will want to go beyond the “regular” sparklines that are the default from Microsoft Excel.

Tufte’s sparklines often have a gray band indicating the normal range for a variable. Sparklines don’t support the gray band, but you can simulate it by using a transparent shape.

My idea of sparkline perfection involves these elements:

• The entire sparkline presentation requires three adjacent cells.

• The sparkline itself is in the center cell. The min and max of the sparkline are set to be the min and max of the data set. This causes the sparkline to fill 100 percent of the vertical height of the cell.

• The height of the cell is either 55 or 110. With 110, you can fit 10 lines of 8-point wrapped text.

• In the cell to the left of the sparkline, you would right-justify the maximum value, 8 line feeds, and then the minimum value. This communicates the highest and lowest point experienced in the data set.

• In the cell to the right of the sparkline, you would left-justify the final value. Depending on if the final value is in the top, middle, or bottom of the range, you would add sufficient number of line feeds before the value to get the label roughly adjacent to the endpoint.

• In the sparkline cell, center the title for the sparkline.

• In the sparkline cell, add a semi-transparent rectangle to show the acceptable values. It is incredibly difficult to position this manually. With VBA, though, you can get the box positioned nearly perfectly.

Figure 13.32 shows a set of four sparklines. Each line is showing readings from manufacturing lines taken every six minutes throughout one day. The min and max value are shown to the left of the cell. The final value is shown to the right of the cell. The colored band represents the acceptable range of 97 to 103.

Figure 13.32 VBA adds labels, titles, and an acceptable range to each sparkline.

The code to create Figure 13.32 is shown here:

Next Steps

While I hope this book has taught you how to create meaningful charts, the next chapter shows many examples of bad charts. People might try to use bad charts to mislead the reader intentionally or might just create a misleading chart because they think it looks cool. By knowing how to spot bad charts, you can prevent people from misleading you the next time you are sitting through a presentation.