Monday, November 5, 2007

Programming with Charts In Excel

Chart programming is a little difficult in Excel because Excel itself has some procedures to update charts automatically after any event is fired. For example, After deleting a series, the axes values are automatically changed. Like this, there are lots of cases when you need to trace the Excel Behavior in order to write an efficient Chart Code. I have got some tips, which I learnt which doing the Chart Programming in my recent project:

1. If you dont want to plot 0's in your chart, then use NA() for Blank cells. It will remove the 0's in the chart. If you use "" or blank cells, it is considered as 0 in the charts.

2. If you have to assign xRange at run time, you will always have to assign it to a series. Assigning xRange goes like this:

chartobject.seriescollection(1).XValues = Some Range

SeriesCollection(1) refers to the First series of the chart. You need to assign the xaxis range to any of the active series in the chart. By Active series, I mean, that the assigned Series should have atleast a non error value. Incase if series1 doesnt have any value(either numeric/blank) in any of its ranged cells, then VBA will not be able to find where it needs to plot the xrange and will throw an error.

3. If you are assigning the values to a series on RunTime, and the first cell contains Error, VBA throws back an error. So while assigning some values to a chart on RunTime, u have to make sure that the First Element in the series is not any Error Refrences.

Hope, that was helpful!

Thanks,
Vikas