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, we 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.

MsgBox ActiveChart.Name

Application
    Workbook
        Worksheet
            ChartObject
                Chart
                    ChartTitle

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

Worksheets("Sheet1").ChartObjects(1).Chart.HasTitle = True

Application
    Workbook
        Chart
             ChartTitle

Sheets("Chart1").ChartTitle.Text ="YTD Sales"

MsgBox TypeName(Sheets("Sheet1").ChartObjects(1).Chart)
Msgbox TypeName(Sheets("Chart1"))

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

Note the AddChart2 method has no arguments, so a default chart is created.

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

Modifying Charts

Enhancements introduced with 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 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

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

Unfortunately, you can’t undo this action once the macro is triggered. However, you can use the following code to do 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 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?

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, we 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 that data could be lost. The user must confirm the deletion before the macro can continue. You probably won’t want to inundate the user with this warning prompt. To eliminate the prompt, use the DisplayAlerts property to temporarily turn alerts off before deleting.

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

Figure 9.1 shows four charts that use a variety of different formatting; Figure 9.2 shows the same charts after running the FormatAllCharts macro.

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. We use an InputBox function to get the number of columns. The values for the Left and Top properties are calculated within the loop.

Creating Lots of Charts

The example in this section demonstrates how to automate the task of creating multiple charts. Figure 9.3 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.

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

Next, we can apply the procedure Make50Charts, which 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. We used the Union method to join these two ranges into one Range object, which is passed to the CreateChart procedure. The other tricky part is to determine the top and left position for each chart. This code does that:

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 9.4 shows some of the 50 charts.

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.

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

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.

Changing chart data based on the active cell

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

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.

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

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

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

Using VBA to Display Arbitrary Data Labels on a Chart

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 9.6 shows an example. We specify 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.

This feature is great but is not completely backward compatible. Figure 9.7 shows how the chart looks when opened in Excel 2010.

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 9.8 shows an XY chart. It would be useful to display the associated name for each data point.

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

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

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)

Displaying a Chart in a UserForm

In Chapter 15, we 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.

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 cleaned up to make it 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

Figure 9.10 illustrates the resulting userform when the macro is run.

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.

Table 9.1 lists all the chart events.

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 9.11).

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. This is made possible with 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

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.

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

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.

Public WithEvents clsChart As Chart

Dim MyChart As New clsChart

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

Public WithEvents clsChart As Chart

Private Sub clsChart_Activate()
    MsgBox clsChart.Parent.Name &" was activated!"
End Sub

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

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

Discovering VBA Charting Tricks

This section contains a few charting tricks that might be useful in your applications. Others are simply for fun, or at the very least studying them could give you some new insights into the object model for charts.

Sub PrintEmbeddedCharts()
    Dim ChtObj As ChartObject
    For Each ChtObj In ActiveSheet.ChartObjects
        ChtObj.Chart.PrintOut
    Next ChtObj
End Sub

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 theXValuesorValuesproperties of theSeriesobject. This technique is described next.

The following procedure creates a chart 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

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 9.13 for an example.

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.

This section describes an alternative to chart tips. Figure 9.14 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.

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 9.15). If the mouse pointer isn’t over a series, the text box is hidden.

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

Scrolling a chart

Figure 9.16 illustrates the example chart found in the scrolling chart.xlsm sample workbook. This chart displays only a portion of the source data, but it can be scrolled to show additional values.

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 (note the workbook name and sheet name have been eliminated 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.

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, we use an End statement rather than an Exit Sub statement. The Exit Sub doesn’t work reliably in this scenario and may even crash Excel.

Working with Sparkline Charts

We 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 9.17 shows examples of the three types of Sparklines supported by Excel.

As with most features, Microsoft added Sparklines to Excel’s object model, which means that you can work with Sparklines using VBA. At the top of the object hierarchy is the SparklineGroups collection, which is a collection of all SparklineGroup objects. A SparklineGroup object contains Sparkline objects. Contrary to what you might expect, the parent of the SparklineGroups collection is a Range object, not a Worksheet object. Therefore, the following statement generates an error:

MsgBox ActiveSheet.SparklineGroups.Count

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

Unfortunately, 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 9.18 shows a sample report generated from this procedure.