Thread: Dynamic charting problems (events)

I am in the process of implementing some dynamic charting and have run
into some problems that I cannot see to get past. I was hoping someone
else had seen this issue before. I have distilled my problem down to a
simple example, though. Given the following sample sheet:


Jan Feb Mar
Gross 1 10 100
Cogs 2 20 200
Exp. 3 30 300
Misc 4 40 400


If I chart that sheet range and choose to PlotBy (Series in) rows
(default), thus putting the row titles on the legend. The chart type
doesn't matter too much, but for the sake of this exercise, I chose
Clusterd Column. Everything at this point is fine and the chart looks
as it should. I then register this chart with a ChartEvent class so
that I can "see" the chart events. No problem there.


Now, in order to facilitate dynamic charting, I have code that does,
say, a delete of some of the rows in the above sheet (Cogs & Exp). When
I do that, I also "kick" the chart and reset the data source range with
the SetSourceData method and nothing else. The PlotBy parameter to that
method is supposed to be Optional. However, the sequence of events
hoses things.


When the Delete of the Cogs and Exp rows happens, the Legend containing
the row titles changes to #REF errors for the deleted rows, which I
would expect. The Chart_Calculate event fires as a result of the delete
operation. If I look at the PlotBy property when the Chart_Calculate
event fires it is set to some bogus value (2042). Legal values are
1=Rows, 2=Cols.


I then "kick" the chart, resetting the data source range (and only the
range, not the PlotBy property). This again fires the Chart_Calculate
event. The end result is that Excel swaps the titles that appear in the
legend on the sheet. The column titles suddenly appear in the legend
and the row titles now appear on the x-axis of the graph. Looking at
the PlotBy property indicates that it is now magically set to columns
(2). I never explicitly set it to columns, though. And although in this
simple example I could pre-read the PlotBy value and just reset it when
I change the data source range, in my real code where we have
implemented some dynamic spreadsheets (and are attempting dynamic
charting) we do all of the "maintenance" on the sheet range first and
then when the changes have been made to the range we then go update all
of the charts that point to that range. But by the time we get to do
that on an event that we can trap (i.e. Chart_Calculate) it is too
late. The PlotBy property is already corrupted with a 2042 and
we can never recover the pre-delete value of this paramenter without
keeping it stored in some global data structure. While that is
certainly an option, it is not preferred. My main question is:

Why is Excel automatically deciding that the PlotBy value should change
from Rows to Cols? Thanks in advance for any info on this issue.
roy

I don't have any specific comments or suggestions, but if your intent
is to provide a chart that adjusts as data are added/subtracted, you
might want to create something along the lines of Dynamic Charts
http://www.tushar-mehta.com/excel/ne...rts/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <1106661601.727048.76500@z14g2000cwz.googlegroups.com>,
roybrew@att.net says...
> I am in the process of implementing some dynamic charting and have run
> into some problems that I cannot see to get past. I was hoping someone
> else had seen this issue before. I have distilled my problem down to a
> simple example, though. Given the following sample sheet:
>
>
> Jan Feb Mar
> Gross 1 10 100
> Cogs 2 20 200
> Exp. 3 30 300
> Misc 4 40 400
>
>
> If I chart that sheet range and choose to PlotBy (Series in) rows
> (default), thus putting the row titles on the legend. The chart type
> doesn't matter too much, but for the sake of this exercise, I chose
> Clusterd Column. Everything at this point is fine and the chart looks
> as it should. I then register this chart with a ChartEvent class so
> that I can "see" the chart events. No problem there.
>
>
> Now, in order to facilitate dynamic charting, I have code that does,
> say, a delete of some of the rows in the above sheet (Cogs & Exp). When
> I do that, I also "kick" the chart and reset the data source range with
> the SetSourceData method and nothing else. The PlotBy parameter to that
> method is supposed to be Optional. However, the sequence of events
> hoses things.
>
>
> When the Delete of the Cogs and Exp rows happens, the Legend containing
> the row titles changes to #REF errors for the deleted rows, which I
> would expect. The Chart_Calculate event fires as a result of the delete
> operation. If I look at the PlotBy property when the Chart_Calculate
> event fires it is set to some bogus value (2042). Legal values are
> 1=Rows, 2=Cols.
>
>
> I then "kick" the chart, resetting the data source range (and only the
> range, not the PlotBy property). This again fires the Chart_Calculate
> event. The end result is that Excel swaps the titles that appear in the
> legend on the sheet. The column titles suddenly appear in the legend
> and the row titles now appear on the x-axis of the graph. Looking at
> the PlotBy property indicates that it is now magically set to columns
> (2). I never explicitly set it to columns, though. And although in this
> simple example I could pre-read the PlotBy value and just reset it when
> I change the data source range, in my real code where we have
> implemented some dynamic spreadsheets (and are attempting dynamic
> charting) we do all of the "maintenance" on the sheet range first and
> then when the changes have been made to the range we then go update all
> of the charts that point to that range. But by the time we get to do
> that on an event that we can trap (i.e. Chart_Calculate) it is too
> late. The PlotBy property is already corrupted with a 2042 and
> we can never recover the pre-delete value of this paramenter without
> keeping it stored in some global data structure. While that is
> certainly an option, it is not preferred. My main question is:
>
> Why is Excel automatically deciding that the PlotBy value should change
> from Rows to Cols? Thanks in advance for any info on this issue.
> roy
>
>

I was able to work around this by implementing a Chart Event class and
catch the Chart_Calculate event. In the event of this bogus PlotBy
value of 2042, we ended up ignoring it. Also, I was able to retain the
Chart PlotBy value in a sheet variable. In the event that I get a
bonified change of the PlotBy parameter (through the UI), I handle that
case in the Chart_Calculate event handler and update my sheet variable
that holds the PlotBy. So, I was able to work around this problem.

The 2042 looks to me like a default, what is used if you specified
nothing. Excel will look at a range, and if there are more rows, it
plots by columns by default. By deleting rows then repeating the
SetSourceData without a parameter, Excel counts rows and columns again,
and comes up with a different result. Now that you're specifying the
PlotBy setting, you control the chart's appearance.

In limited testing I was unable to get a PlotBy value other than 1 or 2;
the default appearance of the chart did produce only these two values.
And default constants like this are usually negative and in the 4000's.

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

roybrew@att.net wrote:

> I was able to work around this by implementing a Chart Event class and
> catch the Chart_Calculate event. In the event of this bogus PlotBy
> value of 2042, we ended up ignoring it. Also, I was able to retain the
> Chart PlotBy value in a sheet variable. In the event that I get a
> bonified change of the PlotBy parameter (through the UI), I handle that
> case in the Chart_Calculate event handler and update my sheet variable
> that holds the PlotBy. So, I was able to work around this problem.
>

Well, the problem was that all we changed (and all we wanted to change)
was the source data range. However, cells in the range were
added/deleted iteratively by some XLL (C) code under the hood. I just
sometimes wish Excel wouldn't "think" for you. The result of Excel's
thinking is not always what you want to do.