I've created charts that plot a big block of monthly data. In my worksheet columns, I have the months listed from jan 2000 to dec 2010, i.e. around 132 months. Each column contains data to be plotted for each of these 132 months, around 100 data points. I need to create a line chart of these 100 data points (i.e. going down each column), and then I need to do this for every month, i.e. 132 charts.
I wrote some code that does exactly what I need to do, i.e. loops thru each column and plots a chart for that column's data series. I also set a whole bunch of properties for each chart (axis format etc), and get each chart positioned exactly how I want. So far so good. I haven't included the code, because it works fine.
The problem is that once the 132 charts have been created, the spreadsheet becomes large (20MB), and becomes so slow, I essentially can't use it. Seriously, I can barely touch the sheet without it having a fit. If I try to flick between worksheets that contain graphs, each graph is recontructed point by point before my eyes before I can do anything. Same thing happens if I try to copy a worksheet, set print area, scroll the worksheet - anything I do, even select a cell, and all the charts disappear, and are then reconstructed point by point. I find that most of my CPU is used by Excel when I try to do anything.
It's such a shame, because the code works fine, and I have done what I need, but I can't use the result.
I have investigated - apparently Excel 2007 (my version) is known to be slow with charts in general (rendering, apparently). So as a workaround, I tried running the code in Excel 2003. The charts that get created are definitely faster to view and scroll etc, however, after creating about 30 or so charts, the code crashes, and I get an error (no new fonts may be applied in this workbook). I have looked into this limitation of Excel 2003. For some reason, Jon Peltier's page on this can't be loaded
http://peltiertech.com/Excel/Charts/FixFonts.html
The MS support site recommends that I set the autoscale of fonts feature to FALSE. This has had no effect for me.
In short, I am stuck in 2007 cuz the workbook becomes too slow to use. I am stuck in 2003 because of the "no new fonts" error.
Any help would be gratefully received.
Thanks,
I.
Bookmarks