XL: Unexpected Results/Error Using SELECT() with Chart Series

ID: Q114520

5.00 5.00c 7.00 WINDOWS kbcode

The information in this article applies to:

SYMPTOMS

In Microsoft Excel versions 5.x and 7.0, when you use the SELECT() function in a Microsoft Excel 4.0 macro sheet to select a series on a chart, you may receive either an error message or unexpected results when you run the macro.

CAUSE

When you use the SELECT() function to select a chart series, for example SELECT("S1"), the series number is the order in which the series data appears on the supporting worksheet, not necessarily the series order that you have given the series data.

For example, if you create a chart with data series in columns A through C on a worksheet, and you use the command SELECT("S1") function in a macro to select the first series on the chart, the series created from column A is selected, even if you have changed the series order so that the series in column A is actually the third series.

This behavior is different from earlier versions of Microsoft Excel: in Microsoft Excel version 4.0 and earlier, when you use the SELECT() command to select a series on a chart, the series order is used.

WORKAROUND

If you are writing a macro that you need to run in Microsoft Excel versions 4.0, 5.0, and 7.0, and the syntax that you need to use depends on the version in which the macro is running. You can use the GET.WORKSPACE() function to determine the version, and then use the appropriate syntax as in the following example: 

   =IF(GET.WORKSPACE(2)<5, SELECT("S1"), SELECT("S4"))
Because the GET.WORKSPACE(2) command returns the version of Microsoft Excel that you are running, you can use this to take the appropriate action. In the above macro command, if the version of Microsoft Excel is less than 5.0, the first series is selected, otherwise, the fourth series is selected.

MORE INFORMATION

Note that if you use the SeriesCollection method with the Select method in a Visual Basic macro, the series with the series order number that you specify is returned. For example, the following macro selects series one on the active chart. 

   ActiveChart.SeriesCollection(1).Select
Microsoft provides examples of Visual Basic 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. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

REFERENCES

For more information about SELECT, choose the Search button in Microsoft Excel Macro Functions Help and type: 

    SELECT function
KBCategory: kbcode KBSubcategory:

Additional reference words: 5.00 5.00c 7.00 PATTERN() 

Version           : 5.00 5.00c 7.00
Platform          : WINDOWS

Last Reviewed: September 15, 1996