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