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

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Uncategorized and tagged , , , , , , , , . Bookmark the permalink.

20 Responses to Dynamic Range Names And Charts In Excel 2010

  1. Paul McVicker says:

    I am using dynamic ranges in charts in Excel 2010 64-bit with no issues. I use a different technique where the dynamic range name used in each graph stays the same. I modify dynamic range formula via VBA and a set of formulas.

    • Biggus Dickus says:

      Hmmmm. But you can’t use Named ranges at all in my 32 Bit Excel … go figure.

      I’d better look into this some more.

      Thanx

      • Biggus Dickus says:

        Paul:

        Maybe it works for you because you do it right🙂 ….Check out my latest Post.

        Sorry.
        Dick

  2. Giorgio says:

    Hi Dick,
    I’m trying(me leaning much more towards Access) to see how your code works so I copied and pasted your
    into an Excel 2003 worksheet module but debug tells me the syntax of the following isn’t right:
    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 & “)”

    • Biggus Dickus says:

      Giorgio:

      Yes … I see the problem. The fact is that I can’t just paste VBA code into my blog using the default Font because it insists on changing my generic double-quotes to opening and closing quotes (??) It’s too smart (or too English – sorry – had to say that ;-)).

      Sooooo …. I have changed the font for that section of the Blog and now the quotations appear correctly and if you copy and paste them into your Module it should work now.

      I also copied my code into Notepad, saved that file and then reopened it as the source of my code sample. I noticed that there was also a problem with carriage-returns in the original if you got past changing all the double-quotes to te generic format (??) which I HAVE seen before.

      Sorry for any inconvenience on this and let me knw asap if it works for you this way.

      Dick

      • Biggus Dickus says:

        Giorgio … better check my newest post on this before you waste any more time on this …. Sorry

        Dick

  3. Giorgio says:

    I meant to say I copied and pasted your Sub SetChartFields() 🙂

  4. AlexJ says:

    Can’t replicate the original problem. In 2010 (32) I can plot ranges defined as dynamic range names. Can you share part of the sheet which exhibits the problem? I’d like to verify. This would be a REALLY big problem.

    Another solution idea:
    How about defining the dynamic range names, build the charts based on static range names, then copy the dynamic range to the static range names using VBA based on an appropriate event? That way you don’t have to play with series in VBA, and the routine could be generic

  5. Andy Pope says:

    Hi,

    Entering the named range via the formula bar is a problem in both xl2007 and xl2010.

    You need to use the Select Data dialog to enter your named ranges.

    • Biggus Dickus says:

      Hey Andy ….

      I see that you can type the Range Name if you Add a new Series but that seems to just capture the range it is referring to at that moment in time. It is no longer a “Dynamic” range reference (if I see this properly).

      • Andy Pope says:

        It will do if you enter it in the source box.
        You need to add each series and set the Values and Category references using the named ranges.

      • Biggus Dickus says:

        Hey Andy:

        I must be missing something here (??). When I go to the Values and Categories in the Source Box it wont let me type in a named range at all (let alone a dynamic range)… only cell refs.

        Could you send me a picture of where and how you’re doing that? Then I can post the picture here to show how it can be done … and then I guess I’ll have to rescind my Post here and eat some crow😦 ..

        Thanx a lot for your input on this.
        Dick

  6. Misange says:

    Hi Dick
    In the formula bar, you can type a range name but I found that you must indicate the name of the workbook ‘mybook’!myrange. Otherwise you get an error using names. You can also type the name of the sheet and when you validate, excel will replace it with the name of the workbook. But with this modification you can still use offset, index… and so on to define your dynamic ranges.

  7. Chris says:

    Is anyone else still having problems? This 2010 update is killing me! Excel is now such a pain to use, despite me having a decade of experience with the earlier, better versions…
    My issue is this: I can enter the dynamic range names into the “Select Data Source” dialog box, however it then refers to the wrong range. When I go into the Names Manager and select my name, the correct range is shown!! This should be impossible, so I have no idea what the hell is going on!!? Also, if you try to enter a dynamic named range into the “Chart Data Range” it reverts to an absolute reference as soon as you click on “OK”. Seriously. Why doesn’t it just stick to the named range I want it to use!!? Whoever was in charge of updating excel should be shot. I’ve already wasted weeks of work time trying to repair sheets that previously worked perfectly.
    It appears that MS have really messed excel up because things that used to be simple now no longer work.

    • Chris says:

      Ok, it seems the problem is that you need to specify within the Named Range formula that you want the CountA to be absolute. If you enter it as a relative reference then referring to the Defined Name will effectively change the Named Range formula. Adding the $ fixed the problem….but I’m still not happy. This behaviour just seems stupid.

      This seems like a mistake to me. When would this behaviour ever be useful? Perhaps I’m missing something?

    • Biggus Dickus says:

      I am very concerned about the route MS is taking with both Excel and Access. It seems that they have lost interest in the established product and its millions of users and while reducing their resources on the products, thos e resources are more interested in new and exciting directions for the technology that nobody asked for😉 ..

      I know that MS thinks it has to drive innovation into new areas but there is a point at which you become the phone company and your “cash-cow” becomes making sure people get dial-tone when they pick up their phone rather than the cool toys that you can use on your smart-phone.

      IMHO MS is definitely dropping the ball on Excel and Access (and even Word) and they had better get back on track soon for the sake of the user-base that THEY created and that THEY have profited from hiugely for years and years…

  8. MSimms says:

    Can someone tell me the chances of immitating the functionality found at this URL:
    http://fintools.com/calcs/volamatrix/
    I know this can be done in Access on a workstation, but the question is can it be done with the Access webforms/browser-only feature ? I am also wondering about the graphics capability…..

    • Biggus Dickus says:

      I can’t see why not. You could probably create the chart too and embed it as an Office Web Chart Component. Haven’t tried it but it should be possible. I guess the big question is “Where does your data come from?” Is it a WebService of some kind in which case you may be able to call it from an Access Web form but again I haven’t tried that.

      Sorry my answers are so vague😦

      Dick

      • MSimms says:

        No Dick, I was waiting for that one. And that’s the rub..to get at the Yahoo finance database requires a huge pile of VBA code which employes a reference to the HTTP object library. I guess what I am trying to determine is how robust the “new” macro language is.
        My feeling is that it is too limited to even do what this small app is accomplishing.
        On the other hand, I think this app easily could be performed with the “hybrid” model with a accde file that does the “work” and then the webforms do the data presentation. So in that case, the only difference is: users would need a download link with an MSI installer package that would optionally install the Access 2010 runtime and then copy over the accde run-time access module. Come to think of it, why even use a webform in that case ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s