XL97: How to Change the Taper Style for All Series in a Chart

• Microsoft Excel 97 for Windows

SUMMARY

Microsoft Excel 97 includes many new built-in chart types, including cone and pyramid charts. By default, these chart types contain cones or pyramids that taper to a point for each data point in a series; the height of each cone or pyramid is equal to the value of the data point.

You can change the shape of the cones or pyramids for a series so that it tapers to the value of the largest data point in all the chart series. With this shape, all cones or pyramids for the series are the same, but they are truncated according to the values of the data points. Note that you cannot manually change the cones or pyramids for all of the series in a chart at one time. You must change the shape of the cones or pyramids for each series separately (one series at a time). To change the cones or pyramids for all the series at one time, you must use a Visual Basic for Applications macro.

This article provides an example macro that modifies all the series in a chart to the same shape.

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Creating the Sample Chart

Before you use the sample macro, create a chart to modify. To do this, use the following steps:

1. Save and close all open workbooks, and then create a new workbook.

2. In Sheet1 of the new workbook, type the following data:

         A1: a   B1: 1   C1: 3   D1: 2
         A3: b   B2: 4   C2: 2   D2: 1
         A3: c   B3: 5   C3: 1   D3: 3
   
   

3. Select cell A1, and then click Chart on the Insert menu.

4. In step 1 of the Chart Wizard, click Cone in the Chart Type list.

5. Under Chart Sub-type, click the sample chart in the third row of types and click Finish.

NOTE: The cones for each data point taper to a point.

Sample Macro for Changing Cone Shapes

1. Start the Visual Basic Editor (press ALT+F11).

2. On the Insert, menu click Module.

3. Type the following code in the module:

         Sub Change_Chart_Shapes()
   
             Dim x As Chart
             Dim y As Series
   
             'x is the Chart object.
             Set x = ActiveSheet.ChartObjects(1).Chart
   
             'Loop through all the chart series.
             For Each y In x.SeriesCollection
   
                 'Set the series chart subtype.
                 y.BarShape = xlConeToMax
   
             Next
         End Sub
   
   

4. Run the Change_Chart_Shapes macro.

5. Switch to Microsoft Excel (press ALT+F11).

REFERENCES

For more information about cone and pyramid charts, click the Index tab in Microsoft Excel Help, type the following text

   cone

For more information about the BarShape Property, click the Office Assistant in the Visual Basic Editor, type "barshape", click Search, and then click to view "BarShape Property".

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Excel Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q120802
   TITLE     : Office: How to Add/Remove a Single Office
               Program or Component