Chapter 16: Working with Charts

In This Chapter

• Discovering essential background information on Excel charts

• Knowing the difference between embedded charts and chart sheets

• Understanding the Chart object model

• Using methods other than the macro recorder to help you learn about Chart objects

• Exploring examples of common charting tasks that use VBA

• Navigating more complex charting macros

• Finding out some interesting (and useful) chart-making tricks

• Working with Sparkline charts

Getting the Inside Scoop on Charts

Excel's charting feature lets you create a wide variety of charts using data that's stored in a worksheet. You have a great deal of control over nearly every aspect of each chart.

An Excel chart is simply packed with objects, each of which has its own properties and methods. Because of this, manipulating charts with Visual Basic for Applications (VBA) can be a bit of a challenge. In this chapter, I discuss the key concepts that you need to understand to write VBA code that generates or manipulates charts. The secret, as you'll see, is a good understanding of the object hierarchy for charts.

Chart locations

In Excel, a chart can be located in either of two places in a workbook:

• As an embedded object on a worksheet: A worksheet can contain any number of embedded charts.

• In a separate chart sheet: A chart sheet normally holds a single chart.

Most users create charts manually by using the commands in the Insert⇒Charts group. But you can also create charts by using VBA. And, of course, you can use VBA to modify existing charts.

The fastest way to create a chart manually is to select your data and then press Alt+F1. Excel creates an embedded chart and uses the default chart type. To create a new default chart on a chart sheet, select the data and press F11.

A key concept when working with charts is the active chart — that is, the chart that's currently selected. When the user clicks an embedded chart or activates a chart sheet, a Chart object is activated. In VBA, the ActiveChart property returns the activated Chart object (if any). You can write code to work with this Chart object, much like you can write code to work with the Workbook object returned by the ActiveWorkbook property.

Here's an example: If a chart is activated, the following statement will display the Name property for the Chart object:

MsgBox ActiveChart.Name

If a chart isn't activated, the preceding statement generates an error.

As you see later in this chapter, you don't need to activate a chart to manipulate it with VBA.

The macro recorder and charts

If you've read other chapters in the book, you know that I often recommend using the macro recorder to learn about objects, properties, and methods. As always, recorded macros are best viewed as a learning tool. The recorded code will almost always steer you to the relevant objects, properties, and methods.

The Chart object model

When you first start exploring the object model for a Chart object, you'll probably be confused — which isn't surprising because the object model is confusing. It's also deep.

For example, assume that you want to change the title displayed in an embedded chart. The top-level object, of course, is the Application object (Excel). The Application object contains a Workbook object, and the Workbook object contains a Worksheet object. The Worksheet object contains a ChartObject object, which contains a Chart object. The Chart object has a ChartTitle object, and the ChartTitle object has a Text property that stores the text displayed as the chart's title.

Here's another way to look at this hierarchy for an embedded chart:

Application

    Workbook

        Worksheet

            ChartObject

                Chart

                    ChartTitle

Your VBA code must, of course, follow this object model precisely. For example, to set a chart's title to YTD Sales, you can write a VBA instruction like this:

Worksheets(1).ChartObjects(1).Chart.ChartTitle.Text = “YTD Sales”

This statement assumes the active workbook is the Workbook object. The statement works with the first object in the ChartObjects collection on the first worksheet. The Chart property returns the actual Chart object, and the ChartTitle property returns the ChartTitle object. Finally, you get to the Text property.

Note that the preceding statement will fail if the chart doesn't have a title. To add a default title to the chart (which displays the text Chart Title), use this statement:

Worksheets(“Sheet1”).ChartObjects(1).Chart.HasTitle = True

For a chart sheet, the object hierarchy is a bit different because it doesn't involve the Worksheet object or the ChartObject object. For example, here's the hierarchy for the ChartTitle object for a chart in a chart sheet:

Application

    Workbook

        Chart

             ChartTitle

You can use this VBA statement to set the chart title in a chart sheet to YTD Sales:

Sheets(“Chart1”).ChartTitle.Text = “YTD Sales”

A chart sheet is essentially a Chart object, and it has no containing ChartObject object. Put another way, the parent object for an embedded chart is a ChartObject object, and the parent object for a chart on a separate chart sheet is a Workbook object.

Both of the following statements will display a message box that displays the word Chart:

MsgBox TypeName(Sheets(“Sheet1”).ChartObjects(1).Chart)

Msgbox TypeName(Sheets(“Chart1”))

When you create a new embedded chart, you're adding to the ChartObjects collection and the Shapes collection contained in a particular worksheet. (There is no Charts collection for a worksheet.) When you create a new chart sheet, you're adding to the Charts collection and the Sheets collection for a particular workbook.

Creating an Embedded Chart

A ChartObject is a special type of Shape object. Therefore, it's a member of the Shapes collection. To create a new chart, use the AddChart2 method of the Shapes collection. The following statement creates an empty embedded chart with all default settings:

ActiveSheet.Shapes.AddChart2

The AddChart2 method can use seven arguments (all are optional):

• Style: A numeric code that specifies the style (or overall look) of the chart.

• xlChartType: The type of chart. If omitted, the default chart type is used. Constants for all the chart types are provided (for example, xlArea and xlColumnClustered).

• Left: The left position of the chart, in points. If omitted, Excel centers the chart horizontally.

• Top: The top position of the chart, in points. If omitted, Excel centers the chart vertically.

• Width: The width of the chart, in points. If omitted, Excel uses 354.

• Height: The height of the chart, in points. If omitted, Excel uses 210.

• NewLayout: A numeric code that specifies the layout of the chart.

Here's a statement that creates a clustered column chart, using Style 2 and Layout 5, positioned 50 pixels from the left, 60 pixels from the top, 300 pixels wide, and 200 pixels high:

ActiveSheet.Shapes.AddChart2 201, xlColumnClustered, 50, 60, 300, 200, 5

In many cases, you may find it efficient to create an object variable when the chart is created. The following procedure creates a line chart that you can reference in code by using the MyChart object variable. Note that the AddChart2 method specifies only the first two arguments. The other five arguments use default values.

Sub CreateChart()

    Dim MyChart As Chart

    Set MyChart = ActiveSheet.Shapes.AddChart2(212,xlLineMarkers).Chart

End Sub

A chart without data isn't useful. You can specify data for a chart in two ways:

• Select cells before your code creates the chart

• Use the SetSourceData method of the Chart object after the chart is created

Here's a simple procedure that selects a range of data and then creates a chart:

Sub CreateChart2()

    Range(“A1:B6”).Select

    ActiveSheet.Shapes.AddChart2 201, xlColumnClustered

End Sub

The procedure that follows demonstrates the SetSourceData method. This procedure uses two object variables: DataRange (for the Range object that holds the data) and MyChart (for the Chart object). The MyChart object variable is created at the same time the chart is created.

Sub CreateChart3()

    Dim MyChart As Chart

    Dim DataRange As Range

    Set DataRange = ActiveSheet.Range(“A1:B6”)

    Set MyChart = ActiveSheet.Shapes.AddChart2.Chart

    MyChart.SetSourceData Source:=DataRange

End Sub

The code creates the chart shown in Figure 16-1. Note the AddChart2 method has no arguments, so a default chart is created.

Figure 16-1: A few lines of VBA code created this chart.

Creating a Chart on a Chart Sheet

The preceding section describes the basic procedures for creating an embedded chart. To create a chart directly on a chart sheet, use the Add2 method of the Charts collection. The Add2 method of the Charts collection uses several optional arguments, but these arguments specify the position of the chart sheet — not chart-related information.

The example that follows creates a chart on a chart sheet and specifies the data range and chart type:

Sub CreateChartSheet()

    Dim MyChart As Chart

    Dim DataRange As Range

    Set DataRange = ActiveSheet.Range(“A1:C7”)

    Set MyChart = Charts.Add2

    MyChart.SetSourceData Source:=DataRange

    ActiveChart.ChartType = xlColumnClustered

End Sub

Figure 16-2 shows the result.

Figure 16-2: Creating a chart on a chart sheet.

Modifying Charts

Enhancements in Excel 2013 make it easier than ever for end users to create and modify charts. For example, when a chart is activated, Excel displays three icons on the right side of the chart: Chart Elements (used to add or remove elements from the chart), Style & Color (used to select a chart style or change the color palette), and Chart Filters (used to hide series or data points).

Your VBA code can perform all the actions available from the new chart controls. For example, if you turn on the macro recorder while you add or remove elements from a chart, you'll see that the relevant method is SetElement (a method of the Chart object). This method takes one argument, and predefined constants are available. For example, to add primary horizontal gridlines to the active chart, use this statement:

ActiveChart.SetElement msoElementPrimaryValueGridLinesMajor

To remove the primary horizontal gridlines, use this statement:

ActiveChart.SetElement msoElementPrimaryValueGridLinesNone

All the constants are listed in the Help system, or you can use the macro recorder to discover them.

Use the ChartStyle property to change the chart to a predefined style. The styles are numbers, and no descriptive constants are available. For example, this statement changes the style of the active chart to Style 215:

ActiveChart.ChartStyle = 215

Valid values for the ChartStyle property are 1–48 and 201–248. The latter group consists of new styles introduced in Excel 2013. Also, keep in mind that the actual appearance of the styles isn't consistent across Excel versions. For example, applying style 48 looks different in Excel 2010.

To change the color scheme used by a chart, set its ChartColor property to a value between 1 and 26. For example:

ActiveChart.ChartColor = 12

The ChartColor property is new to Excel 2013.

When you combine the 96 ChartStyle values with the 26 ChartColor options, you have 2,496 combinations — enough to satisfy just about anyone. And if those prebuilt choices aren't enough, you have control over every element in a chart. For example, the following code changes the fill color for one point in a chart series:

With ActiveChart.FullSeriesCollection(1).Points(2).Format.Fill

    .Visible = msoTrue

    .ForeColor.ObjectThemeColor = msoThemeColorAccent2

    .ForeColor.TintAndShade = 0.4

    .ForeColor.Brightness = -0.25

    .Solid

End With

Again, recording your actions while you make changes to a chart will give you the object model information you need to write your code.

Using VBA to Activate a Chart

When a user clicks any area of an embedded chart, the chart is activated. Your VBA code can activate an embedded chart with the Activate method. Here's a VBA statement that's the equivalent of licking an embedded chart to activate it:

ActiveSheet.ChartObjects(“Chart 1”).Activate

If the chart is on a chart sheet, use a statement like this:

Sheets(“Chart1”).Activate

Alternatively, you can activate a chart by selecting its containing Shape:

ActiveSheet.Shapes(“Chart 1”).Select

When a chart is activated, you can refer to it in your code by using the ActiveChart property (which returns a Chart object). For example, the following instruction displays the name of the active chart. If no active chart exists, the statement generates an error:

MsgBox ActiveChart.Name

To modify a chart with VBA, it's not necessary to activate it. The two procedures that follow have exactly the same effect. That is, they change the embedded chart named Chart 1 to an area chart. The first procedure activates the chart before performing the manipulations; the second one doesn't:

Sub ModifyChart1()

    ActiveSheet.ChartObjects(“Chart 1”).Activate

    ActiveChart.ChartType = xlArea

End Sub

    

Sub ModifyChart2()

    ActiveSheet.ChartObjects(“Chart 1”).Chart.ChartType = xlArea

End Sub

Moving a Chart

A chart embedded on a worksheet can be converted to a chart sheet. To do so manually, just activate the embedded chart and choose Chart Tools⇒Design⇒Location⇒Move Chart. In the Move Chart dialog box, select the New Sheet option and specify a name.

You can also convert an embedded chart to a chart sheet by using VBA. Here's an example that converts the first ChartObject on a worksheet named Sheet1 to a chart sheet named MyChart:

Sub MoveChart1()

    Sheets(“Sheet1”).ChartObjects(1).Chart. _

      Location xlLocationAsNewSheet, “MyChart”

End Sub

The following example does just the opposite of the preceding procedure: It converts the chart on a chart sheet named MyChart to an embedded chart on the worksheet named Sheet1.

Sub MoveChart2()

    Charts(“MyChart”).Location xlLocationAsObject, “Sheet1”

End Sub

Using the Location method also activates the relocated chart.

Using VBA to Deactivate a Chart

You can use the Activate method to activate a chart, but how do you deactivate (that is, deselect) a chart?

As far as I can tell, the only way to deactivate a chart by using VBA is to select something other than the chart. For an embedded chart, you can use the RangeSelection property of the ActiveWindow object to deactivate the chart and select the range that was selected before the chart was activated:

ActiveWindow.RangeSelection.Select

To deactivate a chart on a chart sheet, just write code that activates a different sheet.

Determining Whether a Chart Is Activated

A common type of macro performs some manipulations on the active chart (the chart selected by a user). For example, a macro might change the chart's type, apply a style, add data labels, or export the chart to a graphics file.

The question is how can your VBA code determine whether the user has actually selected a chart? By selecting a chart, I mean either activating a chart sheet or activating an embedded chart by clicking it. Your first inclination might be to check the TypeName property of the Selection, as in this expression:

TypeName(Selection) = “Chart”

In fact, this expression never evaluates to True. When a chart is activated, the actual selection will be an object within the Chart object. For example, the selection might be a Series object, a ChartTitle object, a Legend object, or a PlotArea object.

The solution is to determine whether ActiveChart is Nothing. If so, a chart isn't active. The following code checks to ensure that a chart is active. If not, the user sees a message, and the procedure ends:

If ActiveChart Is Nothing Then

    MsgBox “Select a chart.”

    Exit Sub

Else

    ‘other code goes here

End If

You may find it convenient to use a VBA function procedure to determine whether a chart is activated. The ChartIsSelected function, which follows, returns True if a chart sheet is active or if an embedded chart is activated, but returns False if a chart isn't activated:

Private Function ChartIsActivated() As Boolean

    ChartIsActivated = Not ActiveChart Is Nothing

End Function

Deleting from the ChartObjects or Charts Collection

To delete a chart on a worksheet, you must know the name or index of the ChartObject or the Shape object. This statement deletes the ChartObject named Chart 1 on the active worksheet:

ActiveSheet.ChartObjects(“Chart 1”).Delete

Keep in mind that multiple ChartObjects can have the same name. If that's the case, you can delete a chart by using its index number:

ActiveSheet.ChartObjects(1).Delete

To delete all ChartObject objects on a worksheet, use the Delete method of the ChartObjects collection:

ActiveSheet.ChartObjects.Delete

You can also delete embedded charts by accessing the Shapes collection. The following statement deletes the shape named Chart 1 on the active worksheet:

ActiveSheet.Shapes(“Chart 1”).Delete

This code deletes all embedded charts (and all other shapes) on the active sheet:

Dim shp as Shape

For Each shp In ActiveSheet.Shapes

    shp.Delete

Next shp

To delete a single chart sheet, you must know the chart sheet's name or index. The following statement deletes the chart sheet named Chart1:

Charts(“Chart1”).Delete

To delete all chart sheets in the active workbook, use the following statement:

ActiveWorkbook.Charts.Delete

Deleting sheets causes Excel to display a warning like the one shown in Figure 16-3. The user must reply to this prompt for the macro to continue.

Figure 16-3: Attempting to delete one or more chart sheets results in this message.

If you're deleting a sheet with a macro, you probably won't want this warning prompt to appear. To eliminate the prompt, use the following series of statements:

Application.DisplayAlerts = False

ActiveWorkbook.Charts.Delete

Application.DisplayAlerts = True

Looping through All Charts

In some cases, you may need to perform an operation on all charts. The following example applies changes to every embedded chart on the active worksheet. The procedure uses a loop to cycle through each object in the ChartObjects collection and then accesses the Chart object in each and changes several properties.

Sub FormatAllCharts()

    Dim ChtObj As ChartObject

    For Each ChtObj In ActiveSheet.ChartObjects

      With ChtObj.Chart

        .ChartType = xlLineMarkers

        .ApplyLayout 3

        .ChartStyle = 12

        .ClearToMatchStyle

        .SetElement msoElementChartTitleAboveChart

        .SetElement msoElementLegendNone

        .SetElement msoElementPrimaryValueAxisTitleNone

        .SetElement msoElementPrimaryCategoryAxisTitleNone

        .Axes(xlValue).MinimumScale = 0

        .Axes(xlValue).MaximumScale = 1000

        With .Axes(xlValue).MajorGridlines.Format.Line

            .ForeColor.ObjectThemeColor = msoThemeColorBackground1

            .ForeColor.TintAndShade = 0

            .ForeColor.Brightness = -0.25

            .DashStyle = msoLineSysDash

            .Transparency = 0

        End With

      End With

    Next ChtObj

End Sub

This example is available on the book's website in the format all charts.xlsm file.

Figure 16-4 shows four charts that use a variety of different formatting; Figure 16-5 shows the same charts after running the FormatAllCharts macro.

Figure 16-4: These charts use different formatting.

Figure 16-5: A simple macro applied consistent formatting to the four charts.

The following macro performs the same operation as the preceding FormatAllCharts procedure but works on all the chart sheets in the active workbook:

Sub FormatAllCharts2()

    Dim cht as Chart

    For Each cht In ActiveWorkbook.Charts

      With cht

        .ChartType = xlLineMarkers

        .ApplyLayout 3

        .ChartStyle = 12

        .ClearToMatchStyle

        .SetElement msoElementChartTitleAboveChart

        .SetElement msoElementLegendNone

        .SetElement msoElementPrimaryValueAxisTitleNone

        .SetElement msoElementPrimaryCategoryAxisTitleNone

        .Axes(xlValue).MinimumScale = 0

        .Axes(xlValue).MaximumScale = 1000

        With .Axes(xlValue).MajorGridlines.Format.Line

            .ForeColor.ObjectThemeColor = msoThemeColorBackground1

            .ForeColor.TintAndShade = 0

            .ForeColor.Brightness = -0.25

            .DashStyle = msoLineSysDash

            .Transparency = 0

        End With

      End With

    Next cht

End Sub

Sizing and Aligning ChartObjects

A ChartObject object has standard positional (Top and Left) and sizing (Width and Height) properties that you can access with your VBA code. The Excel Ribbon has controls (in the Chart Tools⇒Format⇒Size group) to set the Height and Width, but not the Top and Left.

The following example resizes all ChartObject objects on a sheet so that they match the dimensions of the active chart. It also arranges the ChartObject objects into a user-specified number of columns.

Sub SizeAndAlignCharts()

    Dim W As Long, H As Long

    Dim TopPosition As Long, LeftPosition As Long

    Dim ChtObj As ChartObject

    Dim i As Long, NumCols As Long

    

    If ActiveChart Is Nothing Then

        MsgBox “Select a chart to be used as the base for the sizing”

        Exit Sub

    End If

    

    ‘Get columns

    On Error Resume Next

    NumCols = InputBox(“How many columns of charts?”)

    If Err.Number <> 0 Then Exit Sub

    If NumCols < 1 Then Exit Sub

    On Error GoTo 0

    

    ‘Get size of active chart

    W = ActiveChart.Parent.Width

    H = ActiveChart.Parent.Height

    

    ‘Change starting positions, if necessary

    TopPosition = 100

    LeftPosition = 20

    For i = 1 To ActiveSheet.ChartObjects.Count

        With ActiveSheet.ChartObjects(i)

            .Width = W

            .Height = H

            .Left = LeftPosition + ((i - 1) Mod NumCols) * W

            .Top = TopPosition + Int((i - 1) / NumCols) * H

        End With

    Next i

End Sub

If no chart is active, the user is prompted to activate a chart that will be used as the basis for sizing the other charts. I use an InputBox function to get the number of columns. The values for the Left and Top properties are calculated within the loop.

Figure 16-6 shows some charts, neatly sized and arranged.

This workbook, named size and align charts.xlsm, is available on the book's website.

Figure 16-6: Using a VBA macro to size and align embedded charts.

Creating Lots of Charts

The example in this section demonstrates how to automate the task of creating multiple charts. Figure 16-7 shows part of the data to be charted. The worksheet contains data for 50 people, and the goal is to create 50 charts, consistently formatted and nicely aligned.

Figure 16-7: Each row of data will be used to create a chart.

I started out by creating the CreateChart procedure, which accepts the following arguments:

• rng: The range to be used for the chart

• l: The left position for the chart

• t: The top position for the chart

• w: The width of the chart

• h: The height of the chart

The CreateChart procedure uses these arguments to create a line chart with axis scale values ranging from 0 to 100.

Sub CreateChart(rng, l, t, w, h)

    With Worksheets(“Sheet2”).Shapes. _

      AddChart2(332, xlLineMarkers, l, t, w, h).Chart

        .SetSourceData Source:=rng

        .Axes(xlValue).MinimumScale = 0

        .Axes(xlValue).MaximumScale = 100

    End With

End Sub

When I was satisfied that this procedure worked, I wrote another procedure, Make50Charts, that uses a For-Next loop to call CreateChart 50 times. Note that the chart data consists of the first row (the headers), plus data in a row from 2 through 50. I used the Union method to join these two ranges into one Range object, which is passed to the CreateChart procedure. The other tricky part was writing code to determine the top and left position for each chart.

Sub Make50Charts()

    Dim ChartData As Range

    Dim i As Long

    Dim leftPos As Long, topPos As Long

‘   Delete existing charts if they exist

    With Worksheets(“Sheet2”).ChartObjects

        If .Count > 0 Then .Delete

    End With

    

‘   Initialize positions

    leftPos = 0

    topPos = 0

    

‘   Loop through the data

    For i = 2 To 51

‘       Determine the data range

        With Worksheets(“Sheet1”)

          Set ChartData = Union(.Range(“A1:F1”), _

            .Range(.Cells(i, 1), .Cells(i, 6)))

        End With

    

‘       Create a chart

        Call CreateChart(ChartData, leftPos, topPos, 180, 120)

    

‘       Adjust positions

        If (i - 1) Mod 5 = 0 Then

            leftPos = 0

            topPos = topPos + 120

        Else

            leftPos = leftPos + 180

        End If

    Next i

End Sub

Figure 16-8 shows some of the 50 charts.

Figure 16-8: A sampling of the 50 charts created by the macro.

Exporting a Chart

In some cases, you may need an Excel chart in the form of a graphics file. For example, you may want to post the chart on a website. One option is to use a screen capture program and copy the pixels directly from the screen. Another choice is to write a simple VBA macro.

The procedure that follows uses the Export method of the Chart object to save the active chart as a GIF file:

Sub SaveChartAsGIF ()

  Dim Fname as String

  If ActiveChart Is Nothing Then Exit Sub

  Fname = ThisWorkbook.Path & “” & ActiveChart.Name & “.gif”

  ActiveChart.Export FileName:=Fname, FilterName:=”GIF”

End Sub

Other choices for the FilterName argument are “JPEG” and “PNG”. Usually, GIF and PNG files look better. The Help system lists a third argument for the Export method: Interactive. If this argument is True, you're supposed to see a dialog box in which you can specify export options. However, this argument has no effect.

Keep in mind that the Export method will fail if the user doesn't have the specified graphics export filter installed. These filters are installed in the Office setup program.

Exporting all graphics

One way to export all graphic images from a workbook is to save the file in HTML format. Doing so creates a directory that contains GIF and PNG images of the charts, shapes, clipart, and even copied range images (created with Home⇒Clipboard⇒Paste⇒Picture (U)).

Here's a VBA procedure that automates the process. It works with the active workbook:

Sub SaveAllGraphics()

    Dim FileName As String

    Dim TempName As String

    Dim DirName As String

    Dim gFile As String

    

    FileName = ActiveWorkbook.FullName

    TempName = ActiveWorkbook.Path & “” & _

       ActiveWorkbook.Name & “graphics.htm”

    DirName = Left(TempName, Len(TempName) - 4) & “_files”

    

‘   Save active workbookbook as HTML, then reopen original

    ActiveWorkbook.Save

    ActiveWorkbook.SaveAs FileName:=TempName, FileFormat:=xlHtml

    Application.DisplayAlerts = False

    ActiveWorkbook.Close

    Workbooks.Open FileName

    

‘   Delete the HTML file

    Kill TempName

    

‘   Delete all but *.PNG files in the HTML folder

    gFile = Dir(DirName & “*.*”)

    Do While gFile <> “”

        If Right(gFile, 3) <> “png” Then Kill DirName & “” & gFile

        gFile = Dir

    Loop

    

‘   Show the exported graphics

    Shell “explorer.exe “ & DirName, vbNormalFocus

End Sub

The procedure starts by saving the active workbook. Then it saves the workbook as an HTML file, closes the file, and reopens the original workbook. Next, it deletes the HTML file because we're just interested in the folder that it creates (because that folder contains the images). The code then loops through the folder and deletes everything except the PNG files. Finally, it uses the Shell function to display the folder.

See Chapter 25 for more information about the file manipulation commands.

This example is available on the book's website in the export all graphics.xlsm file.

Changing the Data Used in a Chart

The examples so far in this chapter have used the SourceData property to specify the complete data range for a chart. In many cases, you'll want to adjust the data used by a particular chart series. To do so, access the Values property of the Series object. The Series object also has an XValues property that stores the category axis values.

The Values property corresponds to the third argument of the SERIES formula, and the XValues property corresponds to the second argument of the SERIES formula. See the sidebar, “Understanding a chart's SERIES formula.”

Changing chart data based on the active cell

Figure 16-9 shows a chart that's based on the data in the row of the active cell. When the user moves the cell pointer, the chart is updated automatically.

This example uses an event handler for the Sheet1 object. The SelectionChange event occurs whenever the user changes the selection by moving the cell pointer. The event-handler procedure for this event (which is located in the code module for the Sheet1 object) is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    If CheckBox1 Then Call UpdateChart

End Sub

Figure 16-9: This chart always displays the data from the row of the active cell.

In other words, every time the user moves the cell cursor, the Worksheet_SelectionChange procedure is executed. If the Auto Update Chart check box (an ActiveX control on the sheet) is checked, this procedure calls the UpdateChart procedure, which follows:

Sub UpdateChart()

    Dim ChtObj As ChartObject

    Dim UserRow As Long

    Set ChtObj = ActiveSheet.ChartObjects(1)

    UserRow = ActiveCell.Row

    If UserRow < 4 Or IsEmpty(Cells(UserRow, 1)) Then

        ChtObj.Visible = False

    Else

        ChtObj.Chart.SeriesCollection(1).Values = _

           Range(Cells(UserRow, 2), Cells(UserRow, 6))

        ChtObj.Chart.ChartTitle.Text = Cells(UserRow, 1).Text

        ChtObj.Visible = True

    End If

End Sub

The UserRow variable contains the row number of the active cell. The If statement checks that the active cell is in a row that contains data. (The data starts in row 4.) If the cell cursor is in a row that doesn't have data, the ChartObject object is hidden, and the underlying text is visible (“Cannot display chart”). Otherwise, the code sets the Values property for the Series object to the range in columns 2–6 of the active row. It also sets the ChartTitle object to correspond to the text in column A.

This example, named chart active cell.xlsm, is available on the book's website.

Using VBA to determine the ranges used in a chart

The previous example demonstrated how to use the Values property of a Series object to specify the data used by a chart series. This section discusses using VBA macros to identify the ranges used by a series in a chart. For example, you might want to increase the size of each series by adding a new cell to the range.

Following is a description of three properties that are relevant to this task:

• Formula property: Returns or sets the SERIES formula for the Series. When you select a series in a chart, its SERIES formula is displayed in the formula bar. The Formula property returns this formula as a string.

• Values property: Returns or sets a collection of all the values in the series. This property can be specified as a range on a worksheet or as an array of constant values, but not a combination of both.

• XValues property: Returns or sets an array of X values for a chart series. The XValues property can be set to a range on a worksheet or to an array of values, but it can't be a combination of both. The XValues property can also be empty.

If you create a VBA macro that needs to determine the data range used by a particular chart series, you might think that the Values property of the Series object is just the ticket. Similarly, the XValues property seems to be the way to get the range that contains the X values (or category labels). In theory, that way of thinking certainly seems correct. But in practice, it doesn't work.

When you set the Values property for a Series object, you can specify a Range object or an array. But when you read this property, an array is always returned. Unfortunately, the object model provides no way to get a Range object used by a Series object.

One possible solution is to write code to parse the SERIES formula and extract the range addresses. This task sounds simple, but it's actually difficult because a SERIES formula can be complex. Following are a few examples of valid SERIES formulas:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1)

=SERIES(,,Sheet1!$B$2:$B$4,1)

=SERIES(,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1)

=SERIES(“Sales Summary”,,Sheet1!$B$2:$B$4,1)

=SERIES(,{“Jan”,”Feb”,”Mar”},Sheet1!$B$2:$B$4,1)

=SERIES(,(Sheet1!$A$2,Sheet1!$A$4),(Sheet1!$B$2,Sheet1!$B$4),1)

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1,Sheet1!$C$2:$C$4)

As you can see, a SERIES formula can have missing arguments, use arrays, and even use noncontiguous range addresses. And, to confuse the issue even more, a bubble chart has an additional argument (for example, the last SERIES formula in the preceding list). Attempting to parse the arguments is certainly not a trivial programming task.

I spent a lot of time working on this problem, and I eventually arrived at a solution that involves evaluating the SERIES formula by using a dummy function. This function accepts the same arguments as a SERIES formula and returns a 2 x 5 element array that contains all the information in the SERIES formula.

I simplified the solution by creating four custom VBA functions, each of which accepts one argument (a reference to a Series object) and returns a two-element array. These functions are the following:

• SERIESNAME_FROM_SERIES: The first array element contains a string that describes the data type of the first SERIES argument (Range, Empty, or String). The second array element contains a range address, an empty string, or a string.

• XVALUES_FROM_SERIES: The first array element contains a string that describes the data type of the second SERIES argument (Range, Array, Empty, or String). The second array element contains a range address, an array, an empty string, or a string.

• VALUES_FROM_SERIES: The first array element contains a string that describes the data type of the third SERIES argument (Range or Array). The second array element contains a range address or an array.

• BUBBLESIZE_FROM_SERIES: The first array element contains a string that describes the data type of the fifth SERIES argument (Range, Array, or Empty). The second array element contains a range address, an array, or an empty string. This function is relevant only for bubble charts.

Note that I did not create a function to get the fourth SERIES argument (plot order). You can obtain this argument directly by using the PlotOrder property of the Series object.

The VBA code for these functions is too lengthy to be listed here, but the code is available on the book's website in a file named get series ranges.xlsm. These functions are documented in such a way that they can be easily adapted to other situations.

The following example demonstrates the VALUES_FROM_SERIES function. It displays the address of the values range for the first series in the active chart.

Sub ShowValueRange()

    Dim Ser As Series

    Dim x As Variant

    Set Ser = ActiveChart.SeriesCollection(1)

    x = VALUES_FROM_SERIES(Ser)

    If x(1) = “Range” Then

        MsgBox Range(x(2)).Address

    End If

End Sub

The variable x is defined as a variant and will hold the two-element array that's returned by the VALUES_FROM_SERIES function. The first element of the x array contains a string that describes the data type. If the string is Range, the message box displays the address of the range contained in the second element of the x array.

Figure 16-10 shows another example. The chart has three data series. Buttons on the sheet execute macros that expand and contract each of the data ranges.

Figure 16-10: This workbook demonstrates how to expand and contract the chart series by using VBA macros.

The ContractAllSeries procedure follows. This procedure loops through the SeriesCollection collection and uses the XVALUE_FROM_SERIES and the VALUES_FROM_SERIES functions to retrieve the current ranges. It then uses the Resize method to decrease the size of the ranges.

Sub ContractAllSeries()

    Dim s As Series

    Dim Result As Variant

    Dim DRange As Range

    For Each s In ActiveSheet.ChartObjects(1).Chart.SeriesCollection

        Result = XVALUES_FROM_SERIES(s)

        If Result(1) = “Range” Then

            Set DRange = Range(Result(2))

            If DRange.Rows.Count > 1 Then

                Set DRange = DRange.Resize(DRange.Rows.Count - 1)

                s.XValues = DRange

            End If

        End If

        Result = VALUES_FROM_SERIES(s)

        If Result(1) = “Range” Then

            Set DRange = Range(Result(2))

            If DRange.Rows.Count > 1 Then

                Set DRange = DRange.Resize(DRange.Rows.Count - 1)

                s.Values = DRange

            End If

        End If

    Next s

End Sub

The ExpandAllSeries procedure is similar. When executed, it expands each range by one cell.

Using VBA to Display Arbitrary Data Labels on a Chart

If you use Excel 2013 exclusively, I'm pleased to announce that this section is no longer relevant. Microsoft has finally responded to what must be thousands of requests for the capability to specify a range of data for chart data labels. That feature is now available in Excel 2013.

Here's how to specify a range of data labels for a chart series:

1. Create your chart and select the data series that will contain labels from a range.

2. Click the Chart Elements icon to the right of the chart and choose Data Labels.

3. Click the arrow to right of the Data Labels item and choose More Options.

The Label Options section of the Format Data Labels task pane is displayed.

4. Select Value From Cells.

Excel prompts you for the range that contains the labels.

Figure 16-11 shows an example. I specified range C2:C7 as the data labels for the series. In the past, specifying a range as data labels had to be done manually or with a VBA macro.

Figure 16-11: Data labels from an arbitrary range show the percent change for each week.

This feature is great but is not backward compatible. Figure 16-12 shows how the chart looks when I opened it in Excel 2010.

Figure 16-12: Data labels created from a range of data are not compatible with earlier versions of Excel.

The remainder of this section describes how to apply data labels from an arbitrary range using VBA. The data labels applied in this manner are compatible with previous versions of Excel.

Figure 16-13 shows an XY chart. It would be useful to display the associated name for each data point.

Figure 16-13: An XY chart that would benefit by having data labels.

The DataLabelsFromRange procedure works with the first chart on the active sheet. It prompts the user for a range and then loops through the Points collection and changes the Text property to the values found in the range.

Sub DataLabelsFromRange()

    Dim DLRange As Range

    Dim Cht As Chart

    Dim i As Integer, Pts As Integer

    

‘   Specify chart

    Set Cht = ActiveSheet.ChartObjects(1).Chart

    

‘   Prompt for a range

    On Error Resume Next

    Set DLRange = Application.InputBox _

      (prompt:=”Range for data labels?”, Type:=8)

    If DLRange Is Nothing Then Exit Sub

    On Error GoTo 0

      

‘   Add data labels

    Cht.SeriesCollection(1).ApplyDataLabels _

      Type:=xlDataLabelsShowValue, _

      AutoText:=True, _

      LegendKey:=False

      

‘   Loop through the Points, and set the data labels

    Pts = Cht.SeriesCollection(1).Points.Count

    For i = 1 To Pts

        Cht.SeriesCollection(1). _

          Points(i).DataLabel.Text = DLRange(i)

    Next i

End Sub

This example, named data labels.xlsm, is available on the book's website.

Figure 16-14 shows the chart after running the DataLabelsFromRange procedure and specifying A2:A9 as the data range.

Figure 16-14: This XY chart has data labels, thanks to a VBA procedure.

A data label in a chart can also consist of a link to a cell. To modify the DataLabelsFromRange procedure so it creates cell links, just change the statement in the For-Next loop to

        Cht.SeriesCollection(1).Points(i).DataLabel.Text = _

          “=” & “'” & DLRange.Parent.Name & “'!” & _

          DLRange(i).Address(ReferenceStyle:=xlR1C1)

The preceding procedure is crude and does little error checking. In addition, it works with only the first Series object. The book's website contains a stand-alone version of my Power Utility Pak utility, which is more sophisticated. Figure 16-15 shows the dialog box. The filename is PUP chart data labeler.xlsm, and it could easily be converted to an add-in.

Figure 16-15: The dialog box for the PUP Chart Data Labeler utility.

Displaying a Chart in a UserForm

In Chapter 13, I describe a way to display a chart in a UserForm. The technique saves the chart as a GIF file and then loads the GIF file into an Image control on the UserForm.

The example in this section uses that same technique but adds a new twist: The chart is created on the fly and uses the data in the row of the active cell. Figure 16-16 shows an example.

Figure 16-16: The chart in this UserForm is created on the fly from the data in the active row.

The UserForm for this example is simple. It contains an Image control and a CommandButton (Close). The worksheet that contains the data has a button that executes the following procedure:

Sub ShowChart()

    Dim UserRow As Long

    UserRow = ActiveCell.Row

    If UserRow < 2 Or IsEmpty(Cells(UserRow, 1)) Then

        MsgBox “Move the cell pointer to a row that contains data.”

        Exit Sub

    End If

    CreateChart (UserRow)

    UserForm1.Show

End Sub

Because the chart is based on the data in the row of the active cell, the procedure warns the user if the cell pointer is in an invalid row. If the active cell is appropriate, ShowChart calls the CreateChart procedure to create the chart and then displays the UserForm.

The CreateChart procedure accepts one argument, which represents the row of the active cell. This procedure originated from a macro recording that I cleaned up to make more general.

Sub CreateChart(r)

    Dim TempChart As Chart

    Dim CatTitles As Range

    Dim SrcRange As Range, SourceData As Range

    Dim FName As String

    

    Set CatTitles = ActiveSheet.Range(“A2:F2”)

    Set SrcRange = ActiveSheet.Range(Cells(r, 1), Cells(r, 6))

    Set SourceData = Union(CatTitles, SrcRange)

    

‘   Add a chart

    Application.ScreenUpdating = False

    Set TempChart = ActiveSheet.Shapes.AddChart2.Chart

        TempChart.SetSourceData Source:=SourceData

    

‘   Fix it up

    With TempChart

        .ChartType = xlColumnClustered

        .SetSourceData Source:=SourceData, PlotBy:=xlRows

        .ChartStyle = 25

        .HasLegend = False

        .PlotArea.Interior.ColorIndex = xlNone

        .Axes(xlValue).MajorGridlines.Delete

        .ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False

        .Axes(xlValue).MaximumScale = 0.6

        .ChartArea.Format.Line.Visible = False

    End With

    

‘   Adjust the ChartObject's size

    With ActiveSheet.ChartObjects(1)

        .Width = 300

        .Height = 200

    End With

    

‘   Save chart as GIF

    

    FName = Application.DefaultFilePath & Application.PathSeparator & “temp.gif”

    

    TempChart.Export Filename:=FName, filterName:=”GIF”

    ActiveSheet.ChartObjects(1).Delete

    Application.ScreenUpdating = True

End Sub

When the CreateChart procedure ends, the worksheet contains a ChartObject with a chart of the data in the row of the active cell. However, the ChartObject isn't visible because ScreenUpdating is turned off. The chart is exported and deleted, and ScreenUpdating is turned back on.

The final instruction of the ShowChart procedure loads the UserForm. Following is the UserForm_Initialize procedure, which simply loads the GIF file into the Image control:

Private Sub UserForm_Initialize()

    Dim FName As String

    FName = Application.DefaultFilePath & _

        Application.PathSeparator & “temp.gif”

    UserForm1.Image1.Picture = LoadPicture(FName)

End Sub

This workbook, named chart in userform.xlsm, is available on the book's website.

Understanding Chart Events

Excel supports several events associated with charts. For example, when a chart is activated, it generates an Activate event. The Calculate event occurs after the chart receives new or changed data. You can, of course, write VBA code that gets executed when a particular event occurs.

Refer to Chapter 17 for additional information about events.

Table 16-1 lists all the chart events.

Table 16-1: Events Recognized by the Chart Object

Event	Action That Triggers the Event
Activate	A chart sheet or embedded chart is activated.
BeforeDoubleClick	An embedded chart is double-clicked. This event occurs before the default double-click action.
BeforeRightClick	An embedded chart is right-clicked. The event occurs before the default right-click action.
Calculate	New or changed data is plotted on a chart.
Deactivate	A chart is deactivated.
MouseDown	A mouse button is pressed while the pointer is over a chart.
MouseMove	The position of the mouse pointer changes over a chart.
MouseUp	A mouse button is released while the pointer is over a chart.
Resize	A chart is resized.
Select	A chart element is selected.
SeriesChange	The value of a chart data point is changed.

An example of using Chart events

To program an event handler for an event taking place on a chart sheet, your VBA code must reside in the code module for the Chart object. To activate this code module, double-click the Chart item in the Project window. Then, in the code module, select Chart from the Object drop-down list on the left and select the event from the Procedure drop-down list on the right (see Figure 16-17).

Figure 16-17: Selecting an event in the code module for a Chart object.

Because an embedded chart doesn't have its own code module, the procedure that I describe in this section works only for chart sheets. You can also handle events for embedded charts, but you must do some initial setup work that involves creating a class module. This procedure is described later in “Enabling events for an embedded chart.”

The example that follows simply displays a message when the user activates a chart sheet, deactivates a chart sheet, or selects any element on the chart. I created a workbook with a chart sheet; then I wrote three event-handler procedures named as follows:

• Chart_Activate: Executed when the chart sheet is activated

• Chart_Deactivate: Executed when the chart sheet is deactivated

• Chart_Select: Executed when an element on the chart sheet is selected

This workbook, named events – chart sheet.xlsm, is available on the book's website.

The Chart_Activate procedure follows:

Private Sub Chart_Activate()

    Dim msg As String

    msg = “Hello “ & Application.UserName & vbCrLf & vbCrLf

    msg = msg & “You are now viewing the six-month sales “

    msg = msg & “summary for Products 1-3.” & vbCrLf & vbCrLf

    msg = msg & _

     “Click an item in the chart to find out what it is.”

    MsgBox msg, vbInformation, ActiveWorkbook.Name

End Sub

This procedure displays a message whenever the chart is activated. See Figure 16-18.

The Chart_Deactivate procedure that follows also displays a message, but only when the chart sheet is deactivated:

Private Sub Chart_Deactivate()

    Dim msg As String

    msg = “Thanks for viewing the chart.”

    MsgBox msg, , ActiveWorkbook.Name

End Sub

Figure 16-18: Activating the chart causes Chart_Activate to display this message.

The Chart_Select procedure that follows is executed whenever an item on the chart is selected:

Private Sub Chart_Select(ByVal ElementID As Long, _

  ByVal Arg1 As Long, ByVal Arg2 As Long)

    Dim Id As String

    Select Case ElementID

        Case xlAxis: Id = “Axis”

        Case xlAxisTitle: Id = “AxisTitle”

        Case xlChartArea: Id = “ChartArea”

        Case xlChartTitle: Id = “ChartTitle”

        Case xlCorners: Id = “Corners”

        Case xlDataLabel: Id = “DataLabel”

        Case xlDataTable: Id = “DataTable”

        Case xlDownBars: Id = “DownBars”

        Case xlDropLines: Id = “DropLines”

        Case xlErrorBars: Id = “ErrorBars”

        Case xlFloor: Id = “Floor”

        Case xlHiLoLines: Id = “HiLoLines”

        Case xlLegend: Id = “Legend”

        Case xlLegendEntry: Id = “LegendEntry”

        Case xlLegendKey: Id = “LegendKey”

        Case xlMajorGridlines: Id = “MajorGridlines”

        Case xlMinorGridlines: Id = “MinorGridlines”

        Case xlNothing: Id = “Nothing”

        Case xlPlotArea: Id = “PlotArea”

        Case xlRadarAxisLabels: Id = “RadarAxisLabels”

        Case xlSeries: Id = “Series”

        Case xlSeriesLines: Id = “SeriesLines”

        Case xlShape: Id = “Shape”

        Case xlTrendline: Id = “Trendline”

        Case xlUpBars: Id = “UpBars”

        Case xlWalls: Id = “Walls”

        Case xlXErrorBars: Id = “XErrorBars”

        Case xlYErrorBars: Id = “YErrorBars”

        Case Else:: Id = “Some unknown thing”

    End Select

    

    MsgBox “Selection type:” & Id & vbCrLf & Arg1 & vbCrLf & Arg2

End Sub

This procedure displays a message box that contains a description of the selected item, plus the values for Arg1 and Arg2. When the Select event occurs, the ElementID argument contains an integer that corresponds to what was selected. The Arg1 and Arg2 arguments provide additional information about the selected item (see the Help system for details). The Select Case structure converts the built-in constants to descriptive strings.

Because the code doesn't contain a comprehensive listing of all items that could appear in a Chart object, I included the Case Else statement.

Enabling events for an embedded chart

As I note in the preceding section, Chart events are automatically enabled for chart sheets but not for charts embedded in a worksheet. To use events with an embedded chart, you need to perform the following steps.

Create a class module

In the Visual Basic Editor (VBE) window, select your project in the Project window and choose Insert⇒Class Module. This step adds a new (empty) class module to your project. Then use the Properties window to give the class module a more descriptive name (such as clsChart). Renaming the class module isn't necessary but is a good practice.

Declare a public Chart object

The next step is to declare a Public variable that will represent the chart. The variable should be of type Chart and must be declared in the class module by using the WithEvents keyword. If you omit the WithEvents keyword, the object will not respond to events. Following is an example of such a declaration:

Public WithEvents clsChart As Chart

Connect the declared object with your chart

Before your event-handler procedures will run, you must connect the declared object in the class module with your embedded chart. You do this by declaring an object of type clsChart (or whatever your class module is named). This should be a module-level object variable, declared in a regular VBA module (not in the class module). Here's an example:

Dim MyChart As New clsChart

Then you must write code to associate the clsChart object with a particular chart. The following statement accomplishes this task:

Set MyChart.clsChart = ActiveSheet.ChartObjects(1).Chart

After this statement is executed, the clsChart object in the class module points to the first embedded chart on the active sheet. Consequently, the event-handler procedures in the class module will execute when the events occur.

Write event-handler procedures for the chart class

In this section, I describe how to write event-handler procedures in the class module. Recall that the class module must contain a declaration such as the following:

Public WithEvents clsChart As Chart

After this new object has been declared with the WithEvents keyword, it appears in the Object drop-down list box in the class module. When you select the new object in the Object box, the valid events for that object are listed in the Procedure drop-down box on the right.

The following example is a simple event-handler procedure that is executed when the embedded chart is activated. This procedure simply pops up a message box that displays the name of the Chart object's parent (which is a ChartObject object).

Private Sub clsChart_Activate()

    MsgBox clsChart.Parent.Name & “ was activated!”

End Sub

The book's website contains a workbook that demonstrates the concepts that I describe in this section. The file is events – embedded chart.xlsm.

Example: Using Chart events with an embedded chart

The example in this section provides a practical demonstration of the information presented in the previous section. The example shown in Figure 16-19 consists of an embedded chart that functions as a clickable image map. When chart events are enabled, clicking one of the chart columns activates a worksheet that shows detailed data for the region.

Figure 16-19: This chart serves as a clickable image map.

The workbook is set up with four worksheets. The sheet named Main contains the embedded chart. The other sheets are named North, South, and West. Formulas in B2:B4 sum the data in the respective sheets, and this summary data is plotted in the chart. Clicking a column in the chart triggers an event, and the event-handler procedure activates the appropriate sheet so that the user can view the details for the desired region.

The workbook contains both a class module named EmbChartClass and a normal VBA module named Module1. For demonstration purposes, the Main worksheet also contains a check box control (from the Forms group). Clicking the check box executes the CheckBox1_Click procedure, which turns event monitoring on and off:

In addition, each of the other worksheets contains a button that executes the ReturnToMain macro that reactivates the Main sheet.

The complete listing of Module1 follows:

Dim SummaryChart As New EmbChartClass

    

Sub CheckBox1_Click()

    If Worksheets(“Main”).CheckBoxes(“Check Box 1”) = xlOn Then

        ‘Enable chart events

        Range(“A1”).Select

        Set SummaryChart.myChartClass = _

          Worksheets(1).ChartObjects(1).Chart

    Else

        ‘Disable chart events

        Set SummaryChart.myChartClass = Nothing

        Range(“A1”).Select

    End If

End Sub

    

Sub ReturnToMain()

‘   Called by worksheet button

    Sheets(“Main”).Activate

End Sub

The first instruction declares a new object variable SummaryChart to be of type EmbChartClass — which, as you recall, is the name of the class module. When the user clicks the Enable Chart Events button, the embedded chart is assigned to the SummaryChart object, which, in effect, enables the events for the chart. The contents of the class module for EmbChartClass follow:

Public WithEvents myChartClass As Chart

    

Private Sub myChartClass_MouseDown(ByVal Button As Long, _

  ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)

    

    Dim IDnum As Long

    Dim a As Long, b As Long

    

‘   The next statement returns values for

‘   IDnum, a, and b

    myChartClass.GetChartElement X, Y, IDnum, a, b

    

‘   Was a series clicked?

    If IDnum = xlSeries Then

        Select Case b

            Case 1

                Sheets(“North”).Activate

            Case 2

                Sheets(“South”).Activate

            Case 3

                Sheets(“West”).Activate

        End Select

    End If

    Range(“A1”).Select

End Sub

Clicking the chart generates a MouseDown event, which executes the myChartClass_MouseDown procedure. This procedure uses the GetChartElement method to determine what element of the chart was clicked. The GetChartElement method returns information about the chart element at specified X and Y coordinates (information that is available through the arguments for the myChartClass_MouseDown procedure).

This workbook, named chart image map.xlsm, is available on the book's website.

Discovering VBA Charting Tricks

This section contains a few charting tricks that I've discovered over the years. Some of these techniques might be useful in your applications, and others are simply for fun. At the very least, studying them could give you some new insights into the object model for charts.

Printing embedded charts on a full page

When an embedded chart is selected, you can print the chart by choosing File⇒Print. The embedded chart will be printed on a full page by itself (just as if it were on a chart sheet), yet it will remain an embedded chart.

The following macro prints all embedded charts on the active sheet, and each chart is printed on a full page:

Sub PrintEmbeddedCharts()

    Dim ChtObj As ChartObject

    For Each ChtObj In ActiveSheet.ChartObjects

        ChtObj.Chart.PrintOut

    Next ChtObj

End Sub

Hiding series by hiding columns

By default, Excel charts don't display data contained in hidden rows or columns. The workbook shown in Figure 16-20 demonstrates an easy way to allow the user to hide and unhide particular chart series. The chart has seven data series, and it's a confusing mess.

Figure 16-20: Using CheckBox controls to specify which data series to display.

A few simple macros allow the user to use the ActiveX CheckBox to indicate which series they'd like to view. Figure 16-21 shows the chart with only three series displayed.

Figure 16-21: A confusing line chart is less confusing when some of the data columns are hidden.

Each series is in a named range: Product_A, Product_B, and so on. Each check box has its own Click event procedure. For example, the procedure that's executed when the user clicks the Product A check box is

Private Sub CheckBox1_Click()

    ActiveSheet.Range(“Product_A”).EntireColumn.Hidden = _

      Not ActiveSheet.OLEObjects(1).Object.Value

End Sub

Excel 2013 includes a new feature that makes it easy to hide and unhide chart series. When a chart is selected, three icons appear to the right of the chart. The third icon, Chart Filters, contains a checkbox for each data series. Just deselect the series name to hide the series. You can also hide and unhide specific data points.

This workbook, named hide and unhide series.xlsm, is available at this book's Web site.

Creating unlinked charts

Normally, an Excel chart uses data stored in a range. Change the data in the range, and the chart is updated automatically. In some cases, you might want to unlink the chart from its data ranges and produce a dead chart (a chart that never changes). For example, if you plot data generated by various what-if scenarios, you might want to save a chart that represents some baseline so that you can compare it with other scenarios.

The three ways to create such a chart are

• Copy the chart as a picture. Activate the chart and choose Home⇒Clipboard⇒Copy⇒Copy As Picture. Accept the defaults in the Copy Picture dialog box. Then click a cell and choose Home⇒Clipboard⇒Paste. The result will be a picture of the copied chart.

• Convert the range references to arrays. Click a chart series and then click the formula bar. Press F9 to convert the ranges to an array, and press Enter. Repeat these steps for each series in the chart.

• Use VBA to assign an array rather than a range to the XValues or Values properties of the Series object. This technique is described next.

The following procedure creates a chart (see Figure 16-22) by using arrays. The data isn't stored in the worksheet. As you can see, the SERIES formula contains arrays and not range references.

Sub CreateUnlinkedChart()

    Dim MyChart As Chart

    Set MyChart = ActiveSheet.Shapes.AddChart2.Chart

    With MyChart

        .SeriesCollection.NewSeries

        .SeriesCollection(1).Name = “Sales”

        .SeriesCollection(1).XValues = Array(“Jan”, “Feb”, “Mar”)

        .SeriesCollection(1).Values = Array(125, 165, 189)

        .ChartType = xlColumnClustered

        .SetElement msoElementLegendNone

    End With

End Sub

Figure 16-22: This chart uses data from arrays (not stored in a worksheet).

Because Excel imposes a limit to the length of a chart's SERIES formula, this technique works for only relatively small data sets.

The following procedure creates a picture of the active chart. (The original chart isn't deleted.) It works only with embedded charts.

Sub ConvertChartToPicture()

    Dim Cht As Chart

    If ActiveChart Is Nothing Then Exit Sub

    If TypeName(ActiveSheet) = “Chart” Then Exit Sub

    Set Cht = ActiveChart

    Cht.CopyPicture Appearance:=xlPrinter, _

      Size:=xlScreen, Format:=xlPicture

    ActiveWindow.RangeSelection.Select

    ActiveSheet.Paste

End Sub

When a chart is converted to a picture, you can create some interesting displays by choosing Picture Tools⇒Format⇒Picture Styles. See Figure 16-23 for an example.

Figure 16-23: After converting a chart to a picture, you can manipulate it by using a variety of commands.

The two examples in this section are available on the book's website in the unlinked charts.xlsm file.

Displaying text with the MouseOver event

A common charting question deals with modifying chart tips. A chart tip is the small message that appears next to the mouse pointer when you move the mouse over an activated chart. The chart tip displays the chart element name and (for series) the value of the data point. The Chart object model does not expose these chart tips, so there is no way to modify them.

To turn chart tips on or off, choose File⇒Options to display the Excel Options dialog box. Click the Advanced tab and locate the Chart section. The options are labeled Show Chart Element Names on Hover and Show Data Point Values on Hover.

This section describes an alternative to chart tips. Figure 16-24 shows a column chart that uses the MouseOver event. When the mouse pointer is positioned over a column, the text box (a Shape object) in the upper-left displays information about the data point. The information is stored in a range and can consist of anything you like.

Figure 16-24: A text box displays information about the data point under the mouse pointer.

The event procedure that follows is located in the code module for the Chart sheet that contains the chart.

Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, _

  ByVal X As Long, ByVal Y As Long)

    Dim ElementId As Long

    Dim arg1 As Long, arg2 As Long

    On Error Resume Next

    ActiveChart.GetChartElement X, Y, ElementId, arg1, arg2

    If ElementId = xlSeries Then

        ActiveChart.Shapes(1).Visible = msoCTrue

        ActiveChart.Shapes(1).TextFrame.Characters.Text = _

          Sheets(“Sheet1”).Range(“Comments”).Offset(arg2, arg1)

    Else

        ActiveChart.Shapes(1).Visible = msoFalse

    End If

End Sub

This procedure monitors all mouse movements on the Chart sheet. The mouse coordinates are contained in the X and Y variables, which are passed to the procedure. The Button and Shift arguments aren't used in this procedure.

As in the previous example, the key component in this procedure is the GetChartElement method. If ElementId is xlSeries, the mouse pointer is over a series. The TextBox is made visible and displays the text in a particular cell. This text contains descriptive information about the data point (see Figure 16-25). If the mouse pointer isn't over a series, the text box is hidden.

Figure 16-25: Range B7:C9 contains data point information that's displayed in the text box on the chart.

The example workbook also contains a Chart_Activate event procedure that turns off the normal ChartTip display, and a Chart_Deactivate procedure that turns the settings back on. The Chart_Activate procedure is

Private Sub Chart_Activate()

    Application.ShowChartTipNames = False

    Application.ShowChartTipValues = False

End Sub

The book's website contains this example set up for an embedded chart (mouseover event - embedded.xlsm) and for a chart sheet (mouseover event - chart sheet.xlsm).

Animating Charts

Most people don't realize it, but Excel is capable of performing simple animations. For example, you can animate shapes and charts. Consider the XY chart shown in Figure 16-26.

Figure 16-26: A simple VBA procedure will turn this graph into an interesting animation.

The X values (column A) depend on the value in cell A1. The value in each row is the previous row's value plus the value in A1. Column B contains formulas that calculate the SIN of the corresponding value in column A. The following simple procedure produces an interesting animation. It uses a loop to continually change the value in cell A1, which causes the values in the X and Y ranges to change. The effect is an animated chart.

Sub SimpleAnimation()

    Dim i As Long

    Range(“A1”) = 0

    For i = 1 To 150

        DoEvents

        Range(“A1”) = Range(“A1”) + 0.035

        DoEvents    

    Next i

    Range(“A1”) = 0

End Sub

The key to chart animation is to use one or more DoEvents statements. This statement passes control to the operating system, which (apparently) causes the chart to be updated when control is passed back to Excel. Without the DoEvents statements, the chart's changes would not be displayed inside of the loop.

The book's website contains a workbook that includes this animated chart, plus several other animation examples. The filename is animated charts.xlsm.

Scrolling a chart

Figure 16-27 shows a chart with 5,218 data points in each series. The chart shows only a portion of the data, but it can be scrolled to show additional values.

Figure 16-27: The values in column F determine which data to display in the chart.

The workbook contains six names:

• StartDay: A name for cell F1

• NumDays: A name for cell F2

• Increment: A name for cell F3 (used for automatic scrolling)

• Date: A named formula:

=OFFSET(Sheet1!$A$1,StartDay,0,NumDays,1)

• ProdA: A named formula:

=OFFSET(Sheet1!$B$1,StartDay,0,NumDays,1)

• ProdB: A named formula:

=OFFSET(Sheet1!$C$1,StartDay,0,NumDays,1)

Each SERIES formula in the chart uses names for the category values and the data. The SERIES formula for the Product A series is as follows (I deleted the sheet name and workbook name for clarity):

=SERIES($B$1,Date,ProdA,1)

The SERIES formula for the Product B series is

=SERIES($C$1,Date,ProdB,2)

Using these names enables the user to specify a value for StartDay and NumDays. The chart will display a subset of the data.

The book's website contains a workbook that includes this animated chart. The filename is scrolling chart.xlsm.

A relatively simple macro makes the chart scroll. The button in the worksheet executes the following macro that scrolls (or stops scrolling) the chart:

Public AnimationInProgress As Boolean

    

Sub AnimateChart()

    Dim StartVal As Long, r As Long

    If AnimationInProgress Then

        AnimationInProgress = False

        End

    End If

    AnimationInProgress = True

    StartVal = Range(“StartDay”)

    For r = StartVal To 5219 - Range(“NumDays”)Step Range(“Increment”)

        Range(“StartDay”) = r

        DoEvents

    Next r

    AnimationInProgress = False

End Sub

The AnimateChart procedure uses a public variable (AnimationInProgress) to keep track of the animation status. The animation results from a loop that changes the value in the StartDay cell. Because the two chart series use this value, the chart is continually updated with a new starting value. The Scroll Increment setting determines how quickly the chart scrolls.

To stop the animation, I use an End statement rather than an Exit Sub statement. For some reason, Exit Sub doesn't work reliably and may even crash Excel.

Creating a hypocycloid chart

Even if you hated your high school trigonometry class, you'll probably like the example in this section, which relies heavily on trigonometric functions. The workbook shown in Figure 16-28 contains an XY chart that displays a nearly infinite number of dazzling hypocycloid curves. A hypocycloid curve is the path formed by a point on a circle that rolls inside of another circle. This, as you may recall from your childhood, is the same technique used in Hasbro's popular Spirograph toy.

Figure 16-28: This workbook generates hypocycloid curves.

This workbook is available on the book's website. The filename is hypocycloid - animate.xlsm.

The chart is an XY chart, and all chart elements are hidden except the data series. The X and Y data are generated by using formulas stored in cells to the right (not visible in the figure). The scroll bar controls at the top let you adjust the three parameters that drive the formulas and determine the look of the chart. In addition, clicking the Random button generates random values for the three parameters.

The chart itself is interesting enough, but it gets really interesting when it's animated. The animation occurs by changing the starting value for the series within a loop.

Creating a clock chart

Figure 16-29 shows an XY chart formatted to look like a clock. It not only looks like a clock but also functions as a clock. I can't think of a single reason why anyone would need to display a clock like this on a worksheet, but creating the workbook was challenging, and you might find it instructive.

Figure 16-29: This clock is fully functional and is actually an XY chart in disguise.

This workbook, named vba clock chart.xlsm, is available on the book's website.

Besides the clock chart, the workbook contains a text box that displays the time as a normal string, as shown in Figure 16-30. Normally the text box is hidden, but you can display it by deselecting the Analog Clock check box.

Figure 16-30: Displaying a digital clock in a worksheet is much easier but not as much fun to create.

As you explore this workbook, here are a few things to keep in mind:

• The ChartObject is named ClockChart, and it covers up a range named DigitalClock, which is used to display the time digitally.

• The two buttons on the worksheet are from the Forms toolbar, and each has a macro assigned (StartClock and StopClock).

• The CheckBox control (named cbClockType) on the worksheet is from the Forms toolbar — not from the Control Toolbox toolbar. Clicking the object executes a procedure named cbClockType_Click, which simply toggles the Visible property of ChartObject. When ChartObject is invisible, the digital clock is revealed.

• The chart is an XY chart with four Series objects. These series represent the hour hand, the minute hand, the second hand, and the 12 numbers. The numbers are data labels for the fourth series.

• The UpdateClock procedure is executed when the Start Clock button is clicked. The procedure also uses the OnTime method of the Application object to set up a new OnTime event that will occur in one second. In other words, the UpdateClock procedure is called every second.

• Unlike most charts, this one doesn't use any worksheet ranges for its data. Rather, the values are calculated in VBA and transferred directly to the Values and XValues properties of the chart's Series object.

Although this clock is an interesting demo, displaying a continually updating clock in a worksheet isn't feasible. The VBA macro must be running at all times in the background, which may interfere with other macros and reduce overall performance.

Creating an Interactive Chart without VBA

The example shown in Figure 16-31 is a useful application that allows the user to choose two U.S. cities (from a list of 284 cities) and view a chart that compares the cities by month in any of the following categories: average precipitation, average temperature, percent sunshine, and average wind speed.

The most interesting aspect of this application is that it uses no VBA macros. The interactivity is a result of using Excel's built-in features. The cities are chosen from a drop-down list, using Excel's Data Validation feature, and the data option is selected using four Option Button controls, which are linked to a cell. The pieces are all connected using a few formulas.

This example demonstrates that it is indeed possible to create a user-friendly, interactive application without the assistance of macros.

Figure 16-31: This application uses a variety of techniques (but no VBA code) to plot monthly climate data for two selected U.S. cities.

This workbook, named climate data.xlsx, is available on the book's website.

The following sections describe the steps I took to set up this application.

Getting the data to create an interactive chart

I did a Web search and spent about five minutes locating the data I needed at the National Climatic Data Center. I copied the data from my browser window, pasted it in an Excel worksheet, and did a bit of clean-up work. The result was four 13-column tables of data, which I named PrecipitationData, TemperatureData, SunshineData, and WindData. To keep the interface as clean as possible, I put the data on a separate sheet (named Data).

Creating the Option Button controls for an interactive chart

I needed a way to allow the user to select the data to plot and decided to use OptionButton controls from the Forms group of controls. Because option buttons work as a group, the four OptionButton controls are all linked to the same cell: cell O3. Cell O3, therefore, contains a value from 1 to 4, depending on which option button is selected.

I needed a way to obtain the name of the data table based on the numeric value in cell O3. The solution was to write a formula (in cell O4) that uses Excel's CHOOSE function:

=CHOOSE(O3,”TemperatureData”,”PrecipitationData”,”SunshineData”,”WindData”)

Therefore, cell O4 displays the name of one of the four named data tables. I then did some cell formatting behind the OptionButton controls to make them more visible.

Creating the city lists for the interactive chart

The next step is setting up the application: creating drop-down lists to enable the user to choose the cities to be compared in the chart. Excel's Data Validation feature makes creating a drop-down list in a cell easy. First, I did some cell merging to create a wider field. I merged cells J11:M11 for the first city list and gave them the name City1. I merged cells J13:M13 for the second city list and gave them the name City2.

To make working with the list of cities easier, I created a named range, CityList, which refers to the first column in the PrecipitationData table.

Following are the steps that I used to create the drop-down lists:

1. Select J11:M11.

(Remember, these are merged cells.)

2. Choose Data⇒Data Validation to display Excel's Data Validation dialog box.

3. Select the Settings tab in the Data Validation dialog box.

4. In the Allow field, choose List.

5. In the Source field, enter =CityList.

6. Click OK.

7. Copy J11:M11 to J13:M13.

This duplicates the Data Validation settings for the second city.

Figure 16-32 shows the result.

Figure 16-32: Use the Data Validation drop-down list to select a city.

Creating the interactive chart data range

The key to this application is that the chart uses data in a specific range. The data in this range is retrieved from the appropriate data table by using formulas that utilize the VLOOKUP function (see Figure 16-33).

Figure 16-33: The chart uses the data retrieved by formulas in A23:M24.

The formula in cell A23, which looks up data based on the contents of City1, is

=VLOOKUP(City1,INDIRECT(DataTable),COLUMN(),FALSE)

The formula in cell A24 is the same except that it looks up data based on the contents of City2:

=VLOOKUP(City2,INDIRECT(DataTable),COLUMN(),FALSE)

After entering these formulas, I simply copied them across to the next 12 columns.

You may be wondering about the use of the COLUMN function for the third argument of the VLOOKUP function. This function returns the column number of the cell that contains the formula. This method avoids hard coding the column to be retrieved and allows the same formula to be used in each column.

Row 25 contains formulas that calculate the difference between the two cities for each month. I used conditional formatting to apply a different color background for the largest difference and the smallest difference.

The label above the month names is generated by a formula that refers to the DataTable cell and constructs a descriptive title: The formula is

=”Average “ & LEFT(DataTable,LEN(DataTable)-4)

Creating the interactive chart

After completing the previous tasks, the final step — creating the actual chart — is a breeze. The line chart has two data series and uses the data in A22:M24. The chart title is linked to cell B21. The data in A23:M24 changes, of course, whenever an OptionButton control is selected or a new city is selected from either Data Validation list.

Working with Sparkline Charts

I conclude this chapter with a brief discussion of Sparkline charts, a feature introduced in Excel 2010. A Sparkline is a small chart displayed in a cell. A Sparkline lets the viewer quickly spot time-based trends or variations in data. Because they're so compact, Sparklines are often used in a group.

Figure 16-34 shows examples of the three types of Sparklines supported by Excel.

Rather, you need to use the Cells property (which returns a range object):

MsgBox Cells.SparklineGroups.Count

The following example lists the address of each Sparkline group on the active worksheet:

Sub ListSparklineGroups()

    Dim sg As SparklineGroup

    Dim i As Long

    For i = 1 To Cells.SparklineGroups.Count

        Set sg = Cells.SparklineGroups(i)

        MsgBox sg.Location.Address

    Next i

End Sub

Figure 16-34: Sparkline examples.

For some reason, you can't use the For Each construct to loop through the objects in the SparklineGroups collection. You need to refer to the objects by their index number.

Following is another example of working with Sparklines in VBA. The SparklineReport procedure lists information about each Sparkline on the active sheet.

Sub SparklineReport()

    Dim sg As SparklineGroup

    Dim sl As Sparkline

    Dim SGType As String

    Dim SLSheet As Worksheet

    Dim i As Long, j As Long, r As Long

    

    If Cells.SparklineGroups.Count = 0 Then

        MsgBox “No sparklines were found on the active sheet.”

        Exit Sub

    End If

    

    Set SLSheet = ActiveSheet

‘   Insert new worksheet for the report

    Worksheets.Add

    

‘   Headings

    With Range(“A1”)

        .Value = “Sparkline Report: “ & SLSheet.Name & “ in “ _

           & SLSheet.Parent.Name

        .Font.Bold = True

        .Font.Size = 16

    End With

    With Range(“A3:F3”)

        .Value = Array(“Group #”, “Sparkline Grp Range”, _

           “# in Group”, “Type”, “Sparkline #”, “Source Range”)

        .Font.Bold = True

    End With

    r = 4

    

    ‘Loop through each sparkline group

    For i = 1 To SLSheet.Cells.SparklineGroups.Count

        Set sg = SLSheet.Cells.SparklineGroups(i)

        Select Case sg.Type

            Case 1: SGType = “Line”

            Case 2: SGType = “Column”

            Case 3: SGType = “Win/Loss”

        End Select

        ‘ Loop through each sparkline in the group

        For j = 1 To sg.Count

            Set sl = sg.Item(j)

            Cells(r, 1) = i ‘Group #

            Cells(r, 2) = sg.Location.Address

            Cells(r, 3) = sg.Count

            Cells(r, 4) = SGType

            Cells(r, 5) = j ‘Sparkline # within Group

            Cells(r, 6) = sl.SourceData

            r = r + 1

        Next j

        r = r + 1

    Next i

End Sub

Figure 16-35 shows the report generated for the worksheet in Figure 16-34.

Figure 16-35: The result of running the SparklineReport procedure.

This workbook, named sparkline report.xlsm, is available on the book's website.