Excel 2010 VBA Deletes Chart

3
votes

I support an Excel VBA application in my spare-time. I am manufacturing process engineer, not a professional developer.

The charting portions of the applicaiton have worked without issue in Excel 2007. My company is upgrading to Excel 2010 and that same application now has problems with the interaction with the charts on worksheets.

Thie issue is with a bar chart. The application, when reseting, deletes the series from the chart using the code below. I did this so that while new data is being imported / processed there isn't a time where a chart is presented to the user that is not up-to-date with the latest data.

        'select the histogram chart
        Sheets(sChartSheet).Select
        ActiveSheet.ChartObjects("Chart 15").Activate

        Call PBarCaption("Delete Existing Histogram Series")
        'remove any existing series
        For i = 1 To ActiveChart.SeriesCollection.Count
            ActiveChart.SeriesCollection(1).Delete
        Next i

then creates new series as new data to be charted is imported from an external data file:

    'add series for histogram
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Values = "=HistogramData!$B$5:$B$29"
    ActiveChart.SeriesCollection(1).XValues = "=HistogramData!$A$5:$A$29"
    ActiveChart.SeriesCollection(1).charttype = xlColumnClustered

The issue with 2010 is that occasionally when the VBA code is ran, the entire bar-chart is 'lost' (deleted) rather than the chart being present without any defined series.

The 'lost' chart seems to happen when concsecutive executions of the base code is performed via another section of VBA code that automatically creates a series of charts and copies them to PowerPoint.

When the sequence of charts is executated manually, it works without issue. When ran automatically, on the generation of the second chating sequence, the chart is getting deleted.

I am hoping that someone is familiar with the changes in the charting from Excel 2007 to 2010 version and will be able to help.

Thanks,

Len

2
excelvbacharts
Are you able to confirm that the charts are actually being deleted, or is it possible the chart is just turning invisible? Does the length of the ChartObjects collection for the current sheet actually decrease? When we upgraded existing workbooks that have chart manipulation code from Excel 2003 to Excel 2007 we found that our code would occationally cause the charts to vanish from view, but they were still there in the background. If they are still there, this might sound odd, but if you go to the Control Panel and stop and then start the service called "Print Spooler" they may reappear. – Michael Kingsmill
We found the best solution to this problem was to write code that dropped and recreated all the charts from scratch as fresh 2010 charts to remove any latent "corruption" from the Excel 2003 version. – Michael Kingsmill
Michael, Thanks. I'll hopefully have some time next week to get back on this. With the charting object being exposed during macro recording with 2010, I may do just that and delete the entire chart and recreate from scratch every time to be able to have full control. I initally wanted to do that when migrating to 2007 version but the charting object changed and with the object model not being exposed in macro recording it was a little too much to bite off. I'll post update when I get somewhere with this. – NitroLen

2 Answers

2
votes

It might help to rework your code so you're not activating/selecting and then depending on the "active" object not changing: that can break if some other code activates some other object when you're not expecting it...

Dim cht As Chart

Set cht = ActiveWorkbook.Sheets(sChartSheet).ChartObjects("Chart 15").Chart
Do While cht.SeriesCollection.Count > 0
    cht.SeriesCollection(1).Delete
Loop

With cht.SeriesCollection
    .NewSeries
    With .Item(1)
        .Values = "=HistogramData!$B$5:$B$29"
        .XValues = "=HistogramData!$A$5:$A$29"
        .ChartType = xlColumnClustered
    End With
End With
0
votes

I reformated all interactions with the chart to use the "with" rather than selecting. Its better programming.

Unfortunately that didn't solve the problem.

The comments regarding the Print Spooler were helpful and it looks like the issue is related to the interaction with how charts appear on the screen and the printer.

Reference

As many other users within my organization have different printers and when trying the MS Office print drivers as default didn't improve the restuls another solution had to be found.

The issue arose when using VBA code to copy worksheet elemetnts to PowerPoint. Was copying as printer, bitmat using:

rSlideArea.CopyPicture (2)

Changed to copying as screen bitmap using:

rSlideArea.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

Interestingly resulting PowerPoint files with bitmap formatting were easier to work with and were smaller.

The result is that the pictures in PowerPoint aren't as 'pretty' with screen formatting but the application now works in Excel v2010 which was the immediate priority.

Thanks to all who helped.