ChartSeries.vba

Excel VBA class to get Chart Series data.

The original version was created by John Walkenbach a loooong time ago. Actually, it is that old that I can’t remember when I have found it. The only thing that is sure is, that it must have been between 1999 and 2014. Many thanks for that and his allowance to publish it although in the meantime it is very likely that not a single line of the original code is left.

Features

• extends the Excel Series class

• progress any Series formula that is accessible, i.e. shows up in the formula bar

• extract the series formula parts (name, x values, y values, plot order and, if present, the bubble size)

• extract the series formula parts as string or range (if it is a range)

• should work also for the “hard” stuff, e.g.

  • series names containing double quotes

  • workbook or worksheet names that contain spaces, single quotes, double quotes

  • multi-area ranges

Prerequisites / Dependencies

Since this is an Excel VBA class/module you obviously need Microsoft Excel.

How to install / Getting started

Add the CLS and BAS files from the src folder to your project.

If you need some more advice on how to do that, expand me.

1. Open Microsoft Excel.

2. Open the Visual Basic Editor (VBE) (Alt+F11).

3. Add the file(s) in the src folder to your VBA project.

   • With Rubberduck:

     1. Right-click on the project to which you want to add the file(s) in the “Code Explorer” (to show it press Ctrl+R) and click on Add › Existing Files…​.

     2. Select all files in the src folder and click on Open.

   • Without Rubberduck:

     1. Select all files in the src folder in Windows File Explorer.

     2. Drag-and-drop them to the corresponding project in VBE’s “Project Explorer”.
        (To show it press Ctrl+R. Hit it twice if the Code Explorer shows up first.)

4. Check, if there are obvious errors by compiling the project (Debug › Compile ‹project name›).

5. Save the file/project.

   1. Be sure that the file/project you want to save is “active” in the VBE by checking, if its name is shown in VBE’s title bar.
      (If it’s not, open a (class) module of the corresponding project (and close it again).)

   2. Press the “Save” button (the disc symbol similar to 💾) in VBE’s toolbar.

   3. Check that the file (really) was saved by having a look at the “last modified date” of the (project) file in the Windows File Explorer.

Usage / Show it in action

Imagine you have the (valid) series formula

=SERIES("name: !'""b),",(',(''a"''!'!$A$4,',(''a"''!'!$A$5:$A$6,',(''a"''!'!$A$7),(',(''a"''!'!$C$4,',(''a"''!'!$C$5:$C$6,',(''a"''!'!$C$7),2)

I guess even you would need a while to figure out where the individual formula parts start and end, right? With this class you can easily identify them. For example the code

Sub ExampleUsage()
    Dim srs As Series
    Set srs = ActiveChart.FullSeriesCollection(2)

    Dim MySeries As IChartSeries
    Set MySeries = ChartSeries.Create(srs)
    With MySeries
        If Not .IsSeriesAccessible Then Exit Sub

        Debug.Print "FullFormula:        " & .FullFormula

        With .SeriesName
            Debug.Print "SeriesName:"
            If .EntryType = eEntryType.eRange Then
                Debug.Print "  RangeString:      " & .RangeString
            Else
                Debug.Print "  FormulaPart:      " & .FormulaPart
                Debug.Print "  CleanFormulaPart: " & .CleanFormulaPart
            End If
        End With

        With .XValues
            Debug.Print "XValues:"
            If .EntryType = eEntryType.eRange Then
                Debug.Print "  RangePath:        " & .RangePath
                Debug.Print "  RangeBook:        " & .RangeBook
                Debug.Print "  RangeSheet:       " & .RangeSheet
                Debug.Print "  RangeString:      " & .RangeString
            Else
                Debug.Print "  CleanFormulaPart: " & .CleanFormulaPart
            End If
        End With

        With .PlotOrder
            Debug.Print "PlotOrder:"
            Debug.Print "  Value:            " & .Value
        End With
    End With
End Sub

will write

FullFormula:        =SERIES("name: !'""b),",(',(''a"''!'!$A$4,',(''a"''!'!$A$5:$A$6,',(''a"''!'!$A$7),(',(''a"''!'!$B$4,',(''a"''!'!$B$5:$B$6,',(''a"''!'!$B$7),2)
SeriesName:
  FormulaPart:      "name: !'""b),"
  CleanFormulaPart: name: !'"b),
XValues:
  RangePath:
  RangeBook:        DummyWorkbook.xlsx
  RangeSheet:       ,('a"'!
  RangeString:      A4,A5:A6,A7
PlotOrder:
  Value:            2

to VBE’s Immediate Window. (I leave it up to you as an exercise to find out how to get the y values.) Thus,

• the series name is given as string with the value name: !'"b),,

• the x data come from range A4,A5:A6,A7 on sheet ,('a"'!,

• the y data come from range C4,C5:C6,C7 on sheet ,('a"'!, and

• the (total) plot order is 2.

Running Tests

Yes, Unit Tests in Excel are possible. For that you need to have the awesome Rubberduck AddIn installed (and enabled).

The procedure is very similar to the one given in the How to install / Getting started section, but this time add the files from the tests folder (instead of the files from the src folder). When you are done with that, additionally perform the following steps.

1. Check that the files from the src folder are present in the project as well or that you have a reference to project with the source files.^[1]
   (Otherwise tests will/should fail.)

2. Add a reference to the “Rubberduck AddIn” to the project (with the test modules).

   If you don’t have a clue how to do that, expand me.

   • With Rubberduck:

     1. Right-click somewhere on the project in the “Code Explorer” and click on Add/Remove References…​.

     2. Add the reference.

        1. Type (parts of) the library name in the search box until you see it in below (left) list or it is empty.

           • If the AddIn file name is in the lower left list box:

             1. Select the library in the (left) list and

             2. click on the ➨ button to add it to the project references.
                

           • If the the AddIn file name is not in the list:

             1. Click on the Browse…​ button.

             2. Browse to the folder where the AddIn is located.

             3. Select the AddIn and

             4. press the Open button.

     3. Click on the OK button to close the window.

   • Without Rubberduck:

     1. Open the Reference manager in the VBE (Tools › References…​).

        • If the AddIn project name is in the list of available references:

          1. Add a checkmark to the corresponding library.

        • If it’s not in the list:

          1. Click on the Browse…​ button.

          2. Browse to the folder where the AddIn is located.

          3. Select the AddIn and

          4. press the Open button.

     2. Click on the OK button to close the window.

     3. Save the file/project.

3. Open the “Test Explorer” (Rubberduck › Unit Tests › Test Explorer).

4. Run the tests by clicking Run › All Tests.

Because not all the test series (and corresponding data) can be created via code you will find them in the Excel files in the tests folder.

Used By

This project is used by (at least) these projects:

• https://github.com/VBA-tools2/SeriesEntriesInCharts.vba

If you know more, we will be happy to add them here.

Known issues and limitations

I almost exclusively work with scatter charts. So I am not sure if the ChartSeries class also (fully) works for other chart/series types.

Contributing

All contributions are highly welcome!!

If you are new to git/GitHub, please have a look at https://github.com/firstcontributions/first-contributions where you will find a lot of useful information for beginners.

I recently was pointed to https://www.conventionalcommits.org which sounds very promising. I’ll use them from now on too (and hopefully don’t forget it in a hurry).

FAQ

1. What are the '@... comments good for in the code?

   You should really have a look at the awesome Rubberduck project!

License

MIT

---

1. See the collapsible in the next point on how to do that. If you use the Rubberduck way, before adding the reference you first need to switch to the Projects tab.