The deeply nested objects in the Chart object model can be confusing, and result in very long VBA statements. One way to make your code more efficient is to use object variables. For example, refer to the object hierarchy for changing the font of a single data label. Here's a VBA procedure that creates an object variable (MyFont) that represents the Font object:

Sub DeclareObjectVariable()
    Dim MyFont As Font
    Set MyFont = ActiveSheet.ChartObjects(1).Chart. _
      SeriesCollection(1).Points(1).DataLabel.Font
    MyFont.Size = 24
    MyFont.Bold = True
End Sub

The Dim statement declares the variable as a specific object type (in this case, a Font object). The Set statement creates the variable. After the MyFont variable is declared and created, you can use the MyFont object variable instead of the lengthy reference. In general, object variables are most useful when your code needs to refer to an object in several different places.

Following is a VBA procedure that declares object variables for each object in the hierarchy (six of them). This isn't really a practical example. It's more useful as a way to help understand the object hierarchy and object variables.

Sub ObjectVariableDemo()
    Dim MyChartObject As ChartObject
    Dim MyChart As Chart
    Dim MySeries As Series
    Dim MyPoint As Point
    Dim MyDataLabel As DataLabel
    Dim MyFont As Font

    Set MyChartObject = ActiveSheet.ChartObjects(1)
    Set MyChart = MyChartObject.Chart
    Set MySeries = MyChart.SeriesCollection(1)
    Set MyPoint = MySeries.Points(1)
    Set MyDataLabel = MyPoint.DataLabel

Set MyFont = MyDataLabel.Font
    MyFont.Size = 24
    MyFont.Bold = True
End Sub

Another way to deal with lengthy object references is to use a With-End With structure, as follows:

Sub With_Demo()
    With ActiveSheet.ChartObjects(1).Chart. _
      SeriesCollection(1).Points(1).DataLabel.Font
        .Size = 24
        .Bold = True
    End With
End Sub

The two statements between the With and the End With statements are property settings for the Font objects.

This technique also works with object variables. In the procedure that follows, an object variable is declared and created, and this object variable is used in the With-End With structure:

Sub With_Demo2()
    Dim MyFont As Font
    Set MyFont = ActiveSheet.ChartObjects(1).Chart. _
       SeriesCollection(1).Points(1).DataLabel.Font

    With MyFont
        .Size = 24
        .Bold = True
    End With
End Sub

Covering the Chart object model in a comprehensive manner would require a complete book. And it would be a very boring book. The best way to become familiar with the Chart object model is to find out about the pieces as you need them. The sections that follow describe some ways to help you determine which objects, methods, and properties are required to perform a particular VBA task.

Using the Help System

Many people complain about the Help system for Microsoft Office. It's certainly not perfect, but it can be very helpful for understanding objects, methods, and properties. When you're working in the VB Editor, you can type a search term in the Type a Question for Help box. Or just move the cursor over any keyword in your code and press F1. Figure 14-2 shows the Help window that displays when I pressed F1 when the cursor is located within the word Series.

Notice that, when a Help topic is displayed, its location within the Table of Contents is highlighted. Using the Table of Contents can often help you located related information. Also, you can click a link at the bottom (Series Object Members) to view the methods and properties of this object.

Figure 14-2. The Help topic for Series.

Declaring Object Variables and Using IntelliSense

The section "Using Object Variables," earlier in this chapter, describes how to declare and create object variables. One significant advantage to creating object variables is that you can use IntelliSense when writing your VBA code. When you're typing code, Excel continually monitors what you type. If it knows exactly which object you're working with, it offers assistance in the form of a drop-down list.

Figure 14-3 shows an example. Here, MyChart is an object variable that represents a Chart object. When I type the dot after the variable name, Excel displays a list of properties and methods for the object. If you continue typing, the IntelliSense feature scrolls the list to show items that begin with the characters you typed. Select an item from the list and press Tab, and Excel adds it to your code.

Figure 14-3. When you create an object variable, the Autocomplete feature can help you identify properties and methods.

Using the Macro Recorder

As I noted earlier, the macro recorder in Excel 2007 leaves much to be desired when it comes to recording chart-related macros. However, the macro recorder is not completely useless.

You can find out quite a bit by turning on the macro recorder while you create and modify charts. The code itself won't win any awards (assuming that it even works), but you'll often gain some insights regarding objects, properties, and methods. Then you can use the Help system to understand the details.

Searching the Internet

In a typical week, I may get 20-30 unsolicited questions via e-mail. I don't have the time to answer individual questions, but I have noticed that approximately 95% of these questions can be answered simply by searching the Internet.

The Web contains a massive amount of information about Excel — including lots of VBA coding examples. Even if the code you find isn't exactly what you need, it can often lead you in the right direction.

Clicking an embedded chart activates the chart. Your VBA code can activate an embedded chart using the Activate method. Here's an example that activates the first embedded chart on the active sheet:

ActiveSheet.ChartObjects(1).Activate

Note that the Activate method applies to the ChartObject object — not the Chart object contained in the ChartObject.

The following statement, for example, generates an error:

ActiveSheet.ChartObjects(1).Chart.Activate

When a chart is on a chart sheet, however, the Activate method does apply to the Chart object. To activate the chart on the first chart sheet in the active workbook, use a statement like this:

ActiveWorkbook.Charts(1).Activate

When a chart is activated, you can refer to it in your code with the ActiveChart property, which returns a Chart object. For example, the following statement displays the name of the active chart. If no existing chart is active, the statement generates an error.

MsgBox ActiveChart.Name

If you use the macro recorder to create chart-related macros, you'll find that the recorded code always activates the chart and then selects the objects that are manipulated. To modify a chart with VBA, you don't have to activate it or make any selections. In fact, it's usually more efficient if your code does not activate charts and select elements.

The following two procedures have exactly the same effect. (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.Type = xlArea
End Sub

Sub ModifyChart2()
    ActiveSheet.ChartObjects("Chart 1").Chart.Type = xlArea
End Sub

Following is another variation that creates an object variable for the chart. Again, the chart is not activated.

Sub ModifyChart3()
    Dim MyChart As Chart
    Set MyChart = ActiveSheet.ChartObjects("Chart 1").Chart
MyChart.Type = xlArea
End Sub

You can use the Activate method to activate a chart, but how do you deactivate (that is, deselect) a chart? According to the Help system, you can use the Deselect method, as follows, to deactivate a chart:

ActiveChart.Deselect

However, this statement simply doesn't work — at least in the initial release of Excel 2007.

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

The following statement also deselects an embedded chart and selects the active cell (not the previously selected range):

ActiveCell.Select

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, or export the chart to a graphics file.

The simple macro that follows adds a title to the active chart. If a chart is not active when this macro is executed, the macro fails and you'll see the rather cryptic error message shown in Figure 14-4.

Sub AddTitle()
    With ActiveChart
       .SetElement msoElementChartTitleAboveChart
       .ChartTitle.Text = "Budget Projections"
    End With
End Sub

Figure 14-4. This error message appears if a chart is not active.

Your VBA code can determine whether the user has actually selected a chart by checking whether ActiveChart is Nothing. If so, a chart is not active. The AddTitle macro follows, showing the modification that checks for an active chart. If a chart is not selected, the user sees a message (see Figure 14-5) and the procedure ends with no further action.

Sub AddTitle()
    If ActiveChart Is Nothing Then
        MsgBox "Select a chart before running this macro.", vbInformation
        Exit Sub
End If

With ActiveChart
        .SetElement msoElementChartTitleAboveChart
        .ChartTitle.Text = "Budget Projections"
    End With
End Sub

This code will run without error if an embedded chart is active or if a chart sheet is selected.

Figure 14-5. Using a message box to inform the user that a chart must be selected before executing the macro.

As you know, a chart can be either one of two types: an embedded chart or a chart on a chart sheet. In some cases, your macro may need to make this determination. The TypeOfSelection macro, which follows, displays one of three messages:

Sub TypeOfSelection()
    If ActiveChart Is Nothing Then
        MsgBox "No Chart"
        Exit Sub
    End If
    If TypeName(ActiveChart.Parent) = "Workbook" Then
        MsgBox "Chart Sheet"
        Exit Sub
    End If
    If TypeName(ActiveChart.Parent) = "ChartObject" Then
        MsgBox "Embedded Chart"
    End If
End Sub

The TypeOfSelection macro first checks for whether a chart is active. If not, it displays No Chart and the macro ends. The next two If statements use VBA's TypeName function to determine the object type of the active chart's "parent" object. (An object's parent is the object that contains it.) The Chart object in an embedded chart is contained in a ChartObject object. The ChartObject's parent is the worksheet in which it is embedded. The parent of a chart sheet is the Workbook object that contains it.

To delete a chart in a worksheet, your VBA code must know the name or index of the ChartObject. The following statement deletes the ChartObject named Chart 1 in the active worksheet:

ActiveSheet.ChartObjects("Chart 1").Delete

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

ActiveSheet.ChartObjects.Delete

In Excel 2007, you can also delete embedded charts by accessing the Shapes collection. The following statement deletes Chart 1 in the active worksheet:

ActiveSheet.Shapes("Chart 1").Delete

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

Alternatively, you can use the following statement:

Sheets("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 14-6. The user must reply to this prompt for the macro to continue. If you are 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

Figure 14-6. Attempting to delete one or more chart sheets results in this message.

To determine how many chart sheets are in the active workbook, access the Count property of the Charts collection. For example, the following statement displays the number of chart sheets in the active workbook:

MsgBox Activeworkbook.Charts.Count

To count the number of embedded charts in a particular worksheet, access the Count property of the ChartObjects collection. The following statement, for example, displays the number of embedded charts on Sheet1 of the active workbook:

MsgBox ActiveWorkbook.Worksheets("Sheet1").ChartObjects.Count

A common task is to perform an operation on all existing charts. For example, you may want to write a macro to resize all embedded charts, or add the current date to each chart's title. The following macro loops through all embedded charts in the active worksheet and displays a list of their names in a message box:

Sub LoopThruChartObjects()
    Dim ChtObj As ChartObject
    Dim Msg As String
    Msg = ""
    For Each ChtObj In ActiveSheet.ChartObjects
        Msg = Msg & ChtObj.Name & vbNewLine
    Next ChtObj
    MsgBox Msg
End Sub

The LoopThruChartObjects macro uses a variable named Msg to store the names of the charts and then displays the list in a message box. vbNewLine is a built-in constant that adds a line break to the Msg variable.

The previous examples looped through embedded charts. The following procedure is similar, but it loops through all chart sheets in the active workbook:

Sub LoopThruChartSheets()
    Dim Cht As Chart
    Dim Msg As String
    Msg = ""
    For Each Cht In ActiveWorkbook.Charts
        Msg = Msg & Cht.Name & vbNewLine
    Next Cht
    MsgBox Msg
End Sub

The listing that follows combines the two previous macros and uses two loops: One loop cycles through all worksheets, and another loop cycles through all embedded charts. The result is a listing of all embedded charts in all worksheets. The result of running this macro is shown in Figure 14-7. This workbook contains six embedded charts in three worksheets.

Sub LoopThruChartObjects2()
    Dim Wks As Worksheet
    Dim ChtObj As ChartObject
    Dim Msg As String
    Msg = ""
    For Each Wks In ActiveWorkbook.Worksheets
        For Each ChtObj In Wks.ChartObjects
            Msg = Msg & Wks.Name & " –" & ChtObj.Name & vbNewLine
        Next ChtObj
    Msg = Msg & vbNewLine
    Next Wks
    MsgBox Msg
End Sub

Figure 14-7. Displaying the names of all embedded charts in all worksheets.

The Location method of a Chart object moves a chart. You can use this method to convert embedded charts to chart sheets (and vice versa).

The following macro loops through all embedded charts on the active sheet and moves each chart to a chart sheet:

Sub ConvertToChartSheets()
    Dim ChtObj As ChartObject
    For Each ChtObj In ActiveSheet.ChartObjects
        ChtObj.Chart.Location Where:=xlLocationAsNewSheet
    Next ChtObj
End Sub

The macro that follows performs the opposite task: It loops through all chart sheets and converts each chart to an embedded chart on the active sheet.

Sub ConvertToEmbeddedCharts()
    Dim Cht As Chart
    For Each Cht In ActiveWorkbook.Charts
        Cht.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name
    Next Cht
End Sub

As you know, printing a worksheet also prints the embedded charts in that worksheet. In some cases, you may prefer that each chart be printed on a separate page. The example in this section prints all embedded charts on the active sheet:

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

This procedure loops through all ChartObject objects and uses the PrintOut method of the Chart object. If you just want to preview the charts, use the PrintPreview method nstead of the PrintOut method.