Exploring Chart Objects | Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))

Embedded charts are shapes in Excel. You add, manipulate, and delete Chart objects in much the same way you do rectangles. Chart objects, of course, have additional properties that are unique to charts. The macro recorder works for creating or modifying the basic structure of a chart, but it does not capture new advanced formatting. You can take advantage of the recorder where it is useful and use what you have learned with other shapes for enhanced chart formatting.

Create a Chart

The ChartData worksheet in the Chapter06 workbook has enough data to create a simple chart. In this section, you start by recording a macro and then create your own macro to create the chart.

Activate the ChartData worksheet in the Chapter06 workbook, and select cell A1.
Start recording a macro named rMakeChart.
On the Insert tab of the Ribbon, in the Charts group, click the Column arrow, and select the top-left chart from the list (Clustered Column).
Stop the macro recorder, delete the chart, and then edit the recorded macro.

The macro should look similar to the following:
```
Sub rMakeChart()   ActiveSheet.Shapes.AddChart.Select   ActiveChart.SetSourceData Source:=Range("ChartData!$A$1:$C$4")   ActiveChart.ChartType = xlColumnClustered End Sub 
```
Once you’ve recorded the macro to create a chart, you can create your own macro to create the type of chart you want.
In Excel, select cell A1.
In Visual Basic, create the following macro shell, and press F8 twice to initialize the variable.
```
Sub MakeChart()   Dim myChart As Chart End Sub
```
In the Immediate window, type Set myChart = ActiveSheet.Shapes.AddChart.Chart and press Enter.

This creates a default chart using data from the current region of the active cell. If the active cell is in the correct data region, you don’t need the SetSourceData method.
Type myChart.ChartType = xlCylinderColStacked and press Enter.

After you type the equal sign, the Auto List displays the entire list of possible chart types, so you can just select the one you want. However, the row and column orientation, is not right: it doesn’t make sense to stack dollars on top of units.
Type myChart.PlotBy = xlRows and press Enter to make the column headings into the category labels.
Press F5 to finish the macro. Then copy the statements from the Immediate window into the CreateChart macro, and press F8 to step through the macro to test it.

Here’s what your finished macro looks like:
```
Sub MakeChart()   Dim myChart As Chart   Set myChart = ActiveSheet.Shapes.AddChart.Chart   myChart.ChartType = xlCylinderColStacked   myChart.PlotBy = xlRows End Sub 
```

Creating a new chart on a worksheet-without going through the recorder’s selection process-is not difficult, but you must remember to use the AddChart method of the Shapes collection, and then explicitly reference the Chart from the resulting Shape object.

The Current Selection and Charts

With most shapes, it’s easy to refer to an object in the Shapes collection but harder to refer to the currently selected object. (In the section titled “Reference a Selected Shape” earlier in this chapter, you learned how to refer to a selected shape.) With charts, you have the opposite issue. When you click a chart, you don’t actually select the chart, or even the old-style ChartObject drawing object that contains it. Rather, you select one of the objects within the chart. Regardless of what is selected within the chart, the ActiveChart property returns the Chart object. But from a macro, you often want to manipulate an object without having to select it first.

To get to the chart without first selecting it, you need to navigate to the Chart object. You can do that in one of two ways. One way is to use the old-style ChartObjects collection (which is what the recorder does). The other way is to use the new-style Shapes collection. Both object classes-ChartObject and Shapes-have a Chart property that returns the actual chart object you want. So, assuming that there’s only one shape on the worksheet, you can assign either ActiveSheet.ChartObjects(1).Chart or ActiveSheet.Shapes(1).Chart to a variable declared as a Chart object.

One side effect of never selecting the chart’s ChartObject or Shape directly is thatit is virtually impossible to use the user interface to rename the active chart the way you can with other shapes. With most shapes, you simply select the object, type the name in the Name Box to the left of the Formula Bar, and press Enter. With a chart, that technique doesn’t work. From a macro statement, however, you can give the name My Chart to the active chart by using the statement ActiveChart.Parent.Name = "My Chart". This renames the shape so that you can then refer to it with the expression ActiveSheet.Shapes("My Chart").

Synchronize Two Charts

The TwoCharts worksheet in the Chapter06 workbook contains two charts that show total orders for two different regions. The one on the left is named West and the one on the right is named East.

image from book

Based on a quick glance at the charts, you might conclude that the performance of the two regions is roughly equal. But that conclusion would be wrong. The East region’s performance is substantially lower than the West region’s, but Excel automatically scales the axes to fit the data. You want to create a macro that will change the value axis on the East chart to match the axis on the West chart.

Activate the TwoCharts sheet, and press F9 a couple of times.

The data values for the charts come from formulas that automatically randomly adjust the scale so that the relative scale of the two charts will change. The macro should make the upper scale of both charts match the automatic scale of whichever one has bigger values.
Start recording a macro named rSetChartAxis. Right-click the value axis of the West chart, and click Format Axis. Under Axis Options, for the Maximum, click the Fixed option, and click the Auto option. Then turn off the recorder, and edit the macro.

The macro should look something like this, although the maximum value might be different:
```
Sub rSetChartAxis()   ActiveSheet.ChartObjects("ChartWest").Activate   ActiveChart.Axes(xlValue).Select   ActiveChart.Axes(xlValue).MaximumScale = 20000   ActiveChart.Axes(xlValue).MaximumScaleIsAuto = True  End Sub
```
In Visual Basic, clear the Immediate window, create the following shell of a macro, and press F8 three times to initialize the variables and recalculate the chart values.
```
Sub SynchronizeCharts    Dim myWest As Axis   Dim myEast As Axis   Application.Calculate End Sub 
```
The macro uses the ChartObjects collection to get to a ChartObject of the active sheet. You can use the Shapes collection just as well. By using the code name for the active sheet-Sheet3 in this case-you can get Auto List Help as you type the statements.
Clear the Immediate window, and then type the following two statements, pressing Enter after each.
```
Set myWest = Sheet3.Shapes("ChartWest").Chart.Axes(xlValue) Set myEast = Sheet3.Shapes("ChartEast").Chart.Axes(xlValue) 
```
These statements assign the relevant axes to the object variables.
Type the following two statements to make sure that the value axis on each chart is set to automatic.
```
myWest.MaximumScaleIsAuto = True myEast.MaximumScaleIsAuto = True 
```
The MaximumScaleIsAuto property was in the recorded macro.
Type (on one line) myMax = WorksheetFunction.Max( myWest.MaximumScale, myEast.MaximumScale ) and press Enter to calculate the larger of the two axis maximum values.

Most worksheet functions are available in a macro. The ones that are available are grouped under the WorksheetFunction object. Even if the maximum scale of an axis is set to automatic, you still can read the current value from the MaximumScale property.
Type the following two statements to set both axes to the same maximum value.
```
myEast.MaximumScale = myMax myWest.MaximumScale = myMax 
```
This sets the two charts to have the same maximum value. Setting the value of the MaximumScale property automatically changes the MaximumScaleIsAuto property to False.
Press F5 to finish the macro. Then copy the contents of the Immediate window into the macro, and press F8 or F5 to test the macro.
In Excel, right-click the West chart, and click Assign Macro. Select SynchronizeCharts from the list, and click OK.
Press Esc to deselect the chart, and then click the chart multiple times to see the macro synchronize the charts at multiple scales.

The difference between the two regions is much more obvious now. Both charts share the same maximum scale, regardless of which one has larger values. You could add other synchronization tasks to this macro as well. For example, you could make the minimum value for each axis the same.

Format the Plot Area of a Chart

As with all the new formatting capabilities, you can’t record a macro to learn how the formatting object model works. But with Excel 2007, charts use exactly the same formatting subobjects as shapes, so you can readily apply what you learned about formatting shapes to formatting charts. All you need to understand is how to navigate from the chart to the object you want to format. In this section, you’ll gain that understanding by applying a gradient format to the plot area of the charts on the TwoCharts sheet.

Create the following macro shell, and press F8 twice to initialize the variables.

Sub FormatPlotArea()   Dim myPlot As PlotArea   Dim myFormat as FillFormat   End Sub

Clear the contents of the Immediate window, type Set myPlot = Sheet3.Shapes("ChartEast").Chart.PlotArea and press Enter.

By using the code name for the active sheet, you get Auto List Help. If you want to make a macro work with any sheet, replace the code name with ActiveSheet after you have tested the macro. Notice that you use the Chart property to navigate from the shape to the chart that it contains. From there, Auto List helps you find all the component subobjects within a chart.
Type Set myFormat = myPlot.Format.Fill and press Enter.

You declared the myFormat variable as a FillFormat object. This is exactly the same class that you used when creating a shape in the section titled “Add a Gradient-Filled Shape” earlier in this chapter. Once you have a reference to the FillFormat object, formatting a component within a chart is identical to working with any other shape. To get to the FillFormat object, you must first go through the Format property. There is actually a hidden Fill property for the plot area itself, but that returns an old-style ChartFillFormat object, and you don’t want to use that one.
Type myFormat.OneColorGradient msoGradientHorizontal, 2, 0 and press Enter.

This statement applies a gradient format, the same as it would with a shape.
Type myFormat.GradientStops(2).Color.ObjectThemeColor = msoThemeColorAccent1 and press Enter.

This changes the color of the gradient to a standard theme color.
Press F5 to stop the macro. Then copy the contents of the Immediate window into the macro, change West to East in the obect name, and test the macro.

Even though you can’t record the steps for formatting a chart, everything you learn about formatting a shape applies to formatting a chart, and vice versa.

The new shape formatting capabilities in Excel 2007 really are amazing. Even though you cannot use the macro recorder, you can learn to navigate your way around the objects so that you can format them. Fortunately, the formatting capabilities of the user interface and the object model correspond very closely, and you can sometimes even use the object model to discover new features that you never noticed in the user interface.

CLOSE the Chapter06.xlsm workbook.