| Exploring Chart Objects Chart objects have hundreds of properties and methods. Many attributes of a chart are themselves separate objects. Because charts have so many objects and properties, learning how to create and manipulate charts by reading a reference manual is difficult. But creating and manipulating a chart is easy to record, and even though you might see many new methods, properties, and objects, the new objects work according to the same principles as other objects in Excel. Record a Macro That Creates a Chart -
Activate the ChartData worksheet in the Chapter04 workbook, and select cell A1. Record Macro -
Click the Record Macro button on the Visual Basic toolbar, type MakeChart as the name for the macro, and then click OK. Chart Wizard -
Click the ChartWizard button on the Standard toolbar, and then click the Finish button to create the default chart. Stop Recording -
Click the Stop Recording button, delete the chart, and then edit the recorded macro. The following code shows what the macro looks like. (Continuation characters shown here might be in different locations than in your macro.) Sub MakeChart() Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData _ Source:=Sheets("ChartData").Range("A1:C4") ActiveChart.Location _ Where:=xlLocationAsObject, _ Name:="ChartData" End Sub The macro creates an embedded chart in four steps. First it uses the Add method to create a new, blank chart (as a stand-alone sheet). Second it uses the ChartType property to set the type of the chart. Third it uses the SetSourceData method to assign a data range to the chart. And finally it uses the Location method to move the chart onto the worksheet. Modify the Macro That Creates a Chart Once you've recorded the macro to create a chart, you can make modifications to it to instantly create exactly the type of chart you want. -
Click the word xlColumnClustered in the macro. This is one of an enumerated list of values that can be assigned to the ChartType property. -
Click the Edit menu and the List Constants command. Excel displays the entire list of possible chart types. | Tip | Visual Basic is able to display the list of possible values because it knows that ActiveChart can return only a Chart object. Visual Basic doesn't display the list of methods and properties when ActiveSheet or Selection is the object because Visual Basic can't be sure what kind of object is currently selected. If you declare an object variable and assign the object to the variable, Visual Basic will be able to display the helpful lists. | -
Select xlConeBarStacked from the list, and double-click it to insert it into the code. -
At the end of the statement that sets the source data, type a comma, a space, and an underscore. Then press the Enter key to create a new line. -
Add PlotBy:=xlRows as an additional argument to the statement to make the column headings into the category labels. Here's what the revised macro looks like: Sub MakeChart() Charts.Add ActiveChart.ChartType = xlConeBarStacked ActiveChart.SetSourceData _ Source:=Sheets("ChartData").Range("A1:C4"), _ PlotBy:=xlRows ActiveChart.Location Where:=xlLocationAsObject, _ Name:="ChartData" End Sub -
Press F8 repeatedly to step through the modified macro. Watch how Excel creates the chart as a separate sheet first, adds the data, and finally moves it onto the worksheet. An Excel chart can exist in either of two locations. It can exist as a separate sheet in the workbook, or it can be embedded in a worksheet. Regardless of where a chart is located, it behaves the same way. There are some differences, however, in how you refer to each type of chart. Refer to an Existing Embedded Chart When you create a new chart, Excel selects the chart, so you can use ActiveChart to refer to it. If a chart already exists, you have to refer to it differently. A chart that's on a separate sheet is easy to refer to; simply select a single item from the Charts collection. Referring to a chart that's embedded on a worksheet, however, can be confusing. This section, however, will make it all clear. -
In Excel, if the Chart is selected, press the Esc key to deselect the chart. (If you had selected an object inside the chart, you may need to press the Esc key more than once.) -
In Visual Basic, at the bottom of the open module, type Sub SelectChart and press the Enter key. Add these three declaration statements to the top of the macro: Dim myShape As Shape Dim myObject As ChartObject Dim myChart As Chart You'll assign objects to these variables to see how Excel handles charts embedded on a worksheet. -
Press F8 twice to step down to the End Sub statement in the new macro. From the View menu, click the Immediate Window command to display the Immediate window. -
In the Immediate window, type Set myShape = ActiveSheet.Shapes(1) and press the Enter key to assign a reference to the chart's container to the myShape variable. -
In the Immediate window, type Set myObject = ActiveSheet.ChartObjects(1) and press the Enter key to assign a reference to the chart's container to the myObject variable. (Both myShape and myObject refer to the same chart container object, but myShape refers to the chart as a Shape object, and myObject refers to the chart as a ChartObject object.) -
In the Immediate window, type ?myObject.Name and press the Enter key. The name of the chart appears. | Tip | In the Immediate window, if you type a question mark in front of an expression that returns a value and then press the Enter key, you'll see the value displayed immediately. | -
In the Immediate window, type ?myShape.Name and press the Enter key. The same name appears again. Both myObject and myShape refer to the same object. -
In the Immediate window, type myObject.Left = 0 and press the Enter key. Then type myShape.Left = 50 and press the Enter key. In each case, the chart shifts. You can use either container object to move and resize the chart. -
In the Immediate window, type myObject.Select and press the Enter key. Then type myShape.Select and press the Enter key. In both cases, the statement works and you see white boxes at the corner of the chart. The white handles show that you selected the container object, and not the chart inside. | Tip | Interactively, when you click in an embedded chart, you immediately select the portion of the chart that you clicked. For example, if you click around the edge of a chart, you select the Chart Area portion of the chart, which has black handles. To select the container object interactively, you must first click the Select Objects button on the Drawing toolbar. When the container is selected, you can't select any of the chart objects inside. | -
In the Immediate window, type myObject.Activate and press the Enter key. When you activate the ChartObject, you see the boxes at the corners of the chart turn black, indicating that you have now selected the Chart Area inside the chart. As soon as you typed the period, Visual Basic displayed the Activate method in the Auto List, showing that Activate is a method of the ChartObject object. -
In the Immediate window, type myShape.Activate and press the Enter key. (Click OK to close the error message.) When you try to activate the Shape object, you get an error because the Shape object doesn't have an Activate method. (When Visual Basic displayed the Auto List for the Shape object, the Activate method wasn't in the list.) This is one of the ways in which a new-style Shape object differs from an old-style ChartObject object. -
In the Immediate window, type Set myChart = myObject.Chart and press the Enter key. In this statement, the Chart property assigns to the variable myChart a reference to the chart that is contained in the ChartObject object. In the same way that a Shape object doesn't have an Activate method, a Shape object doesn't have a Chart property either. | Tip | The ChartObjects collection is left over from the old-style Excel drawing objects. It couldn't be hidden like the other old graphical object collections because the Shape object doesn't have an Activate method or a Chart property for working with charts. | -
In the Immediate window, type myChart.ChartArea.Interior.Color = vbRed and press the Enter key. The interior of the chart changes to red. Once you have a reference to the chart, you can manipulate the objects inside it. The Interior and Color properties of a ChartObject object correspond to the Fill and RGB properties of a Shape object. -
Close the Immediate window, and press F8 to finish the macro. A chart that's embedded in a worksheet consists of two parts: the container box (a ChartObject object) and the chart inside (a Chart object). You can refer to the container box by using either the Shapes collection (which returns a Shape object) or the ChartObjects collection (which returns a ChartObject object), but to get to the chart inside, you must use the ChartObjects collection, not the Shapes collection. For example, you can move and resize the container using either the Shapes collection or the ChartObjects collection, but to change the color of the chart area, you must get to the chart inside using the ChartObjects collection. Record a Macro That Modifies Chart Attributes Now that you understand how Excel refers to the chart container and the chart inside, you can record a macro that changes a chart and learn what methods and properties you use to control a chart. -
On the ChartData worksheet, select cell A1. Start recording a macro named ChangeChart. -
Click the edge of the chart you created in the section 'Modify the Macro That Creates a Chart.' This activates the chart. -
Double-click one of the numbers along the bottom of the chart. Doing this selects the value axis and displays the Format Axis dialog box. Select the Scale tab. -
Change the Maximum value to 50000. (Entering a value clears the Auto check box.) Click OK. -
Turn off the recorder, and edit the macro. It looks similar to this, although the chart name inside the quotation marks will probably be different: Sub ChangeChart() ActiveSheet.ChartObjects("Chart 20").Activate ActiveChart.ChartArea.Select ActiveChart.Axes(xlValue).Select With ActiveChart.Axes(xlValue) .MinimumScaleIsAuto = True .MaximumScale = 50000 .MinorUnitIsAuto = True .MajorUnitIsAuto = True .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With End Sub The macro first activates the chart, using the ChartObjects collection. (If you want to refer to the chart inside the embedded container without activating it, you must use the Chart property of the ChartObject object.) The macro then selects the ChartArea object. This statement is superfluous because the statement after it selects the value axis. The macro then sets several properties of the value axis, even though you changed only one in the dialog box. You could simplify this entire macro to a single statement: ActiveSheet.ChartObjects("Chart 20").Chart.Axes(xlValue) _ .MaximumScale = 50000 You can be grateful, however, that the recorder included everything it did because you can learn the names of a lot of properties very quickly. In the next section, we'll put some of those properties to work in a very useful macro. Write a Macro That Synchronizes Two Charts The TwoCharts worksheet contains two charts that show total orders for two different regions. Based on a quick glance at the charts, you might conclude that the performance of the two regions was roughly equal. But that would be wrong. The East region is substantially lower than the West, but Excel automatically scales the axes to fit the data. Let's create a macro that will change the value axis on the East chart to match the axis on the West chart. Select Objects -
Name the charts so that you can refer to them by descriptive names. On the Drawing toolbar, click the Select Objects button, click the West chart, and then enter West in the Name box. Follow the same steps to give the name East to the East chart. Then click the Select Objects button again to deselect it. -
In Visual Basic, at the bottom of the module, type Sub SynchronizeCharts and press the Enter key. Then enter the following two variable declarations: Dim myWest As Chart Dim myEast As Chart You'll store a reference to a chart in each of these variables. -
Next enter the following two statements to assign the charts to the variables: Set myWest = ActiveSheet.ChartObjects("West").Chart Set myEast = ActiveSheet.ChartObjects("East").Chart You must include the Chart property to move from the container to the chart inside. If you hadn't renamed the charts, you'd have to use either the default 'Chart 1' and 'Chart 2' or the numbers 1 and 2 (and determine which was which by trial and error). Giving the charts explicit, meaningful names makes your code easier to read and less likely to contain errors. -
Add the following statement to make sure that the value axis on the West chart is automatic: myWest.Axes(xlValue).MaximumScaleIsAuto = True The expression Axes(xlValue) was in the recorded macro. That's how you know how to refer to the value axis. The MaximumScaleIsAuto property didn't appear in the recorded macro, but the MinimumScaleIsAuto property did, and you can guess the rest. -
Add the following statement to make the axes have the same maximum scale: myEast.Axes(xlValue).MaximumScale = _ myWest.Axes(xlValue).MaximumScale Even though the maximum scale of the West chart is set to automatic, you still can read the current value from it. -
Press F8 repeatedly to step through the macro. The difference between the two regions is much more obvious now. -
On the worksheet, change the March value for the West region to 15,000. Then run the SynchronizeCharts macro again. You could add still other synchronization tasks to this macro as well. For example, you could make the minimum value for each axis the same. You could make it so that if you interactively changed the color of the background on the West chart, running the macro would make the East chart the same color. For each enhancement, you simply record a macro to learn what you need to change, and then add it to your macro. Formatting Charts Charts are an interesting hybrid of Excel's older DrawingObjects and Office's Shapes. Charts have been around since the first version of Excel, so all the features that can be controlled with old properties, such as Interior, still use those properties. For example, to set red as the color of the ChartArea of a chart assigned to the myChart variable, you could use the statement myChart.Interior.Color = vbRed. Office Shape objects are newer than Chart objects, but Chart objects can still take advantage of the fancy formatting that's part of a Shape object. To get to those newer features, you must use the Fill property. For example, to create a gradient background for the ChartArea, you could use the statement myChart.Fill.TwoColorGradient msoGradientHorizontal, 1. The formatting properties that can be set using the Interior property can't be set using the Fill property. For example, you can set the fill color of a shape using the statement myShape.Fill.ForeColor.RGB = vbRed, but with a chart, the RGB property is read-only. You can find out the color using the new property, but you have to change it using the old one. Because the properties and methods of the fill for a chart are somewhat different from those for a shape, the Fill property for a chart object returns a ChartFillFormat object, whereas the Fill property for a shape returns a FillFormat object. |