Chart Series using non-contiguous calls in VBA

[Richo via OfficeKB.com]

I'm trying to create charts in Excel from a bunch of non-contiguous cells
using VBA.

The cells are Sheet1!$C1:C2,Sheet1!$C4:C5,Sheet1!$C6:C7,Sheet1!$C9:C10, etc.

I first tried to set Series.Formula with a string that I generated from the
sheet & cell names, but I had enough cells that I hit that 250 character
limit in the Series.Formula arguments.

I tried a number of other tactics, including:
Trying SeriesCollection.Extend (didn't work, since I'm trying to put
data from >1 sheet into the chart)
Trying to just set Series.Values & Series.XValues separately
Applying a name to the renge I wanted to plot, & feeding that into .
Formula, then .Values
(I can't remember what else)

After all this, I found out something absolutely maddening

So maddening indeed that I may be without teeth & hair by the end of the day.

I can:
1 select the series manually with the mouse, and select the "Source
Data" context menu
2 type "=Sheet!NamedRange" into the Values field & hit OK
3 see that the plot has the right data
4 manually select the series again & copy the text of the series
fomula from the formula bar
5 paste said text into VBA code that sets Series.Formula
6 FLIP MY FREAKIN LID BECAUSE THE CODE DOESN'T WORK!!!!

Richo.Breathe.Value = xlSlowly
Richo.Wait(10)

Anyway, does anyone know why this doesn't work? Am I doing something wrong,
or do multi-area ranges somehow work in Excel, but not in VBA?

Obviously I can always make a new sheet specifically for the chart data, but
that somehow seems inelegant...

Any help / kind words are appreciated,

Richo

 

[Jon Peltier]

Richo -

Obviously I can always make a new sheet specifically for the chart
data, but that somehow seems inelegant...

My point of view on this is: if it works, it's elegant. I much prefer
making a separate sheet or range for the data. It makes it easier to run
in the first place, and figure out what's wrong in the second, and it
usually is much faster than working through the more classically elegant
formulations. You can always hide the worksheet if you think you'll be
ashamed!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______