SeriesCollection(Item)
Working on a project last week, I encountered a somewhat amusing source of potential confusion. In VBA, you can reference a SeriesCollection item by name or by index.
In the screenshot above, I can reference the first series in the chart using
ActiveChart.SeriesCollection(1)or using
ActiveChart.SeriesCollection("alpha")Series 1 is Series “alpha”, pretty clear cut.
Well, in this project, several charts used numbers as series names.
So in the screenshot above, I could reference the first series in the chart with
ActiveChart.SeriesCollection(1)or with
ActiveChart.SeriesCollection("1")Series 1 is Series “1”. What’s the difference? Well, in one case, 1 is a number, and in the other, “1” is text. It didn’t seem to matter until the series were not plotted in numerical order.
In the screenshot above, Series 1 is Series “3”, and Series “1” is Series 3.
In Practice
The idea was to have an easy way to let a user select which series to perform some follow-up analysis on.
The first approach was to use a simple InputBox.
An input box accepts all input as text. So if you enter 1, the InputBox returns “1” to your code. If there is a series named “1”, then it’s the series being evaluated, but if there isn’t, hilarity ensues. You don’t get an error that series “1” isn’t found; instead, Excel picks a series using some internal logic all its own.
Okay, let’s use Application.InputBox, which allows you to accept one or more variable types.
You can set this InputBox to accept text or numbers. If you enter a number, you will select a series by index. If you try to select a series by name, by entering a number in quotes, the quotes become part of the series name sought by the code, and it fails (this time without picking any series).
The safest approach was to construct a dialog (aka a UserForm) with a two-column listbox, and listing each series by number and by name. This allows the user to specifically select a series, and if there is any potential confusion, it is obvious in the list.
Paul Clarke says
Dear Jon,
I have reviewed many of your articles relating to ‘chart series’, but have not found a solution to the seemingly simple task of removing the word ‘Series’ from the Series Legend. Can you, or any of your readers, help with this please?
Sincerely
Paul
Paul Carke says
I should have added – this question applies only to an xlBoxwhisker chart.
Jon Peltier says
Paul –
An Excel chart uses Series 1, Series 2, etc. in a legend if you have not specified a range that includes series name labels.
You can rectify this by right-clicking on the chart and choosing Select Data, then modifying the options in the dialog. Select a series and click the Edit button, then either enter a name for that series, or select a cell that contains the name of the series.
This works for any Excel chart, including the built-in box plots.