Microsoft Knowledge Base

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SUMMARY

This article contains a Visual Basic for Applications macro example that demonstrates how to reverse the series order in a chart. For example, the macro allows you to plot the following data:

   A1:  Month   B1:  Series 1  C1:  Series 2
   A2:  jan     B2:  10        C2:  15
   A3:  feb     B3:  20        C3:  25
   A4:  mar     B4:  30        C4:  35
   A5:  apr     B5:  40        C5:  45

   A1:  Month   B1:  Series 2  C1:  Series 1
   A2:  jan     B2:  15        C2:  10
   A3:  feb     B3:  25        C3:  20
   A4:  mar     B4:  35        C4:  30
   A5:  apr     B5:  45        C5:  40

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 Product Support Services (PSS) 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.

EXAMPLE

The sample macro in this article reverses the series order of column, bar, line, xy (scatter), or area charts. The example involves the following steps:

• Typing Sample Data
• Creating the Chart and a Visual Basic Module
• Creating the Macro

Typing Sample Data

Before you create the macro, type the following data in new worksheet:

   A1:  Month   B1:  Series 1  C1:  Series 2
   A2:  jan     B2:  10        C2:  15
   A3:  feb     B3:  20        C3:  25
   A4:  mar     B4:  30        C4:  35
   A5:  apr     B5:  40        C5:  45

Creating the Chart and a Visual Basic Module

Microsoft Excel 97:

1. Select the range A1:C5. On the Insert menu, click Chart.

2. In step 1 of the Chart Wizard, click Column in the Chart Type list. Click Finish.

3. Select the chart.

4. On the Tools menu, point to Macro, and click Visual Basic Editor. In the Visual Basic Editor, click Module on the Insert menu.

5. On the new module sheet, type the macro in the "Creating the Macro" section, and then run the macro.

1. Select the range B2:E5. On the Insert menu, point to Chart and click On This Sheet, or click ChartWizard on the Standard toolbar.

2. Using the mouse, click on the worksheet and drag to draw a chart. In step 1 of the wizard, click Next.

3. In Step 2 of the wizard, click Column, and then click Finish.

4. Select the chart.

5. On the Insert menu, point to Macro and click Module.

6. On the new module sheet, type the macro in the "Creating the Macro" section, and then run the macro.

Creating the Macro

   Sub ReversePlotOrder()

       'Activate the chart.
       ActiveChart.Parent.Activate

       'Count the number of series.
       x = ActiveChart.SeriesCollection.Count

       'Loop through each series in the chart, reversing the plot order.
       For i = 1 To ActiveChart.SeriesCollection.Count - 1

           ActiveChart.ChartGroups(1).SeriesCollection(1).PlotOrder = x
           x = x - 1

       Next i

   End Sub