Dynamic Range Names And Charts In Excel 2010

(Update 05/27/11)  Please be advised that there is a more recent post than this that completely negates the need for the technology mentioned in this Post.  It does not mean this is not a Cool solution (IMHO) but it is nonetheless not necessary if you wish to use Dynamic Ranbges in Excel 2007 and 2010 Charts.  Sorry for the inconvenience……  Dick

Over the years I have developed several cool techniques to drive Excel Charts using Dynamic Range names.  One of the reasons I don’t know much about using VBA to drive Charts is this fact, that I have been able to design my charts with things like Dynamic Ranges that do all the work for me.

Just yesterday I started doing a spreadsheet in Excel 2010 that includes several Chart objects.  (That is pathetic IMHO because I wish more clients would ask me for charts than actually do).  but when I went in to create one of my trademark Dynamic range names (which I will discuss later here) I discovered that you cannot use Range Names in Excel 2010 Chart Object Series Formulae anymore in Excel 2010 !!!!  Huh !!!! 

After searching the Internets about this issue, I found very little information on this and very few questions or complaints about this fact.  That is REALLY disappointing to me and frankly I think this is unacceptable and an example of the Excel Team maybe not realizing enough about what people are really doing with their technology (but that’s another story).

So being me, I just simply HAVE to be able to drive these chart data ranges with Dynamic Formulae so I have figured out a way to do so.

Dynamic Formulae

This formula is in y Workbook and will be used as one of the sources in my SERIES formula in my chart:

This range will vary in length depending on the number of items in the list.  in previous versions of Excel I could use this Name directly in my Chart SERIES formula … not anymore.  I have to somehow push the range this name is currently referring to into the SERIES, and this has to be done by VBA code.

So I want to build as simple and generic a way to feed these ranges into my Charts in a way that can be reused over and over again across any file.  So this is how I do it:

The Solution

The Worksheet data being charted is this:

the Chart is this (it is named “Chart 9”):

1. Create the Dynamic Range Names as required using the syntax above.  This is tedious but is very much worth the effort and is a recyclable technique that gets easier with time.

2. Create the charts using standard cell references so they look right.  A SERIES formula would look like this:

=SERIES(Analysis!$C$5,Analysis!$B$7:$B$16,Analysis!$C$7:$C$16,1)

3. Create a Worksheet in the file that looks like this (and hide it):

This Worksheet is in Formula View BTW.  The first column is the Chart name on the Worksheet, the second is the Data Series Name cell reference exposed as a string using the CELL function.  The third is the name of the Titles Range and the fourth is the Data range name. The fifth is the Series number that this data is to be the source for in the Chart.

Enter a row for every data range you want to feed from Dynamic Formulae in all Charts on the Charts Worksheet.

4. Use this generic VBA Code:

Sub SetChartFields()

    Dim TC As Range
    Dim CH As ChartObject
    Dim ASH As Worksheet
    Dim CSH As Worksheet
    Dim SER As Series

    Set ASH = ActiveSheet
    Set CSH = Worksheets("Charts")
    Set c = Worksheets("ChartList").Cells(2, 1)
    Do While Not IsEmpty(c)
    Set CH = CSH.ChartObjects(c.Value)
    Set SER = CH.Chart.SeriesCollection(c.Offset(0, 4).Value)
    If IsEmpty(c.Offset(0, 1)) Then
        txtTitle = ""
    Else
        txtTitle = c.Offset(0, 1).Value
    End If
    SER.Formula = "=series(" & txtTitle & "," & ASH.Name & "!" & Names(c.Offset(0, 2).Value).RefersToRange.Address & "," & ASH.Name & "!" & Range(c.Offset(0, 3).Value).Address & "," & c.Offset(0, 4).Value & ")"
    Set c = c.Offset(1, 0)
    Loop

End Sub

 

This code assumes that it is being run from the Worksheet that the Dynamic Ranges are referring to but you could accomodate a more complex layout if you want by editing the code (but I like simple).

What this code does is cycle through the cells in Column A of the ChartList Worksheet redefining the SERIES formulae of each Chart from the names in the row.  This may seem complex to you,  but to me it is actually kinda simple to use and to implement.

Conclusion

By using this technique I will be able to develop Charts in my Workbooks that will still rely on Dynamic Range names and that will allow me to continue to be efficient in my Chart implementations on my spreadsheets. 

If anyone has a better way to accomplish this PLEASE let me know, but this is working for me and seems to resolve a problem that I simply should not have but am just going to have to live with I’m afraid.

Dick