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
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
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.
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.