Excel VBA Vs Chart Data Tables

[Clinton M James]

Hi All,

I am programmatically creating a chart and I believe it is cleaner to do it
by having vba throw values into the chart rather than throw the values into
cells for the chart to read off.

I have no probem creating the chart and having it display as required but I
am meeting with frustration with the associated data table.

I format he numbers of the graph to be a percentage through:

ActiveChart.Axes(xlValue).TickLabels.NumberFormat = "0.00%"

This is fine but this does not transfer to the data table. The table will
display the values in their decimal form instead. Is there a way to fix
this?

Also is there a way to label the data table columns? Without referencing
cells. I know when creating the series it only lets me title the values
through a cell range, can i manually add the series titles through vba
instead?

eg

my data table depics 1 2 3 4 as the titles but I want to change these to
meaningful names.

Apart from this nuisance vba has done well for creating a graph without
direct reference to cells for the values.

Help is appreciated and I thank any helps in advance.

Regards,,
Clint

 

[Andy Pope]

Hi,

No VBA will not allow you to extend formatting of the tables.

You may currently being doing okay with static values rather than cell
references but keep in mind there is a limit to the series formula
length, ~1024 characters. With precision numbers it is very easy to
exceed this limit with only a few data points.


Cheers
Andy

 

[Jon Peltier]

"... it is cleaner ..."

It does seem that way, doesn't it? You're not cluttering up a worksheet with
data.

Let me give you a counterpoint or two:

* Excel was designed so that charts use data in worksheets, and while there
is the alternative of generating an array of values, the chart is more
efficient using cells.

* As Andy points out, there is a limit to the length of a SERIES formula,
1024 characters. More important, there is a limit to how many characters can
be assigned to X and Y value arrays. This limit is around 250 characters.
This means that some of the 1024 characters are really not usable in the
SERIES formula, because the Plot Order argument is never more than 3
characters (maximum 255 series in a chart), and the Series Name argument is
also usually much less than 250 characters.

* It's easier to understand what is happening in your code if you place the
data into cells.

* As you've discovered, the only way to affect the format of the chart's
data table is to format the cells containing the data.

* Worksheet space is cheap. What's a new hard drive cost, under $1/GB these
days, isn't it? How about RAM? Your time spent working around the benefits
of using a worksheet (which can be hidden from prying eyes, though it's easy
enough to extract data from a chart) is much more expensive.

- Jon