Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#3
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect and dynamic ranges | Excel Worksheet Functions | |||
dynamic ranges | Excel Worksheet Functions | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
Charting Problems | Charts and Charting in Excel | |||
Charting time? help please... | Charts and Charting in Excel |