Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default 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

  #2   Report Post  
Tushar Mehta
 
Posts: n/a
Default

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 . com,
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


  #3   Report Post  
 
Posts: n/a
Default

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.

  #4   Report Post  
Jon Peltier
 
Posts: n/a
Default

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/
_______

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.

  #5   Report Post  
 
Posts: n/a
Default

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Indirect and dynamic ranges Sam Excel Worksheet Functions 3 January 24th 05 08:01 AM
dynamic ranges Sam Excel Worksheet Functions 2 January 21st 05 08:46 PM
Dynamic Formulas with Dynamic Ranges Ralph Howarth Excel Worksheet Functions 5 January 21st 05 09:44 AM
Charting Problems Quacknot Charts and Charting in Excel 0 January 12th 05 04:43 PM
Charting time? help please... Gustavo Monteverde Charts and Charting in Excel 1 November 28th 04 05:53 AM


All times are GMT +1. The time now is 12:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"