VBA Chart Manipulation Slow

Question

I wrote some Excel VBA code that generates a scatterplot and changes a few properties of the chart. (Code is below for reference.) The code moves slowly through tasks like deleting the chart legend, removing horizontal/vertical gridlines, and changing the X and Y series. Excel's timer gives me the following duration for each task:

insert scatterplot: 0.01171875 
delete series: 0 
plot x vs y: 0.55859375 
delete legend: 0.5703125 
delete chart title: 0.66015625 
remove grid: 1.3046875 
format axes: 0 
overall: 3.11328125

Removing the grid, changing the title, plotting the X and Y series, and deleting the legend seem to take a long time. I've googled for alternative ways to write the code, but haven't been able to find anything useful. The code works entirely as expected, except for the slow speed. Any ideas as to what's causing the bad performance, and how I can speed this up? Thanks in advance.

EDIT: I've already turned off screen updating while working with the chart. The chart is generated/formatted while a userform is open, if that makes any difference.

Here is the relevant snippet of code:

With ActiveChart
    'Delete all series currently in plot
    Do While .FullSeriesCollection.Count > 0
        .FullSeriesCollection(1).Delete
    Loop

    'Plot Actual (Y) vs. Inverse Distribution (X)
    .SeriesCollection.NewSeries
    .FullSeriesCollection(1).XValues = "=" & tempSheetName & "!$C:$C"
    .FullSeriesCollection(1).Values = "=" & tempSheetName & "!$A:$A"

    'Delete legend
    .Legend.Delete

    'Delete chart title
    .SetElement (msoElementChartTitleNone)

    'Remove gridlines
    .SetElement (msoElementPrimaryValueGridLinesNone)
    .SetElement (msoElementPrimaryCategoryGridLinesNone)

    'Format axes
    Dim xAxis As Axis, yAxis As Axis
    Set xAxis = .Axes(xlCategory)
    Set yAxis = .Axes(xlValue)

    With yAxis
        'Title y axis "actual"
        .HasTitle = True
        .AxisTitle.Caption = "Actual"

        'Add tick marks
        .MajorTickMark = xlOutside
    End With

    With xAxis
        'Title x axis by dist type
        .HasTitle = True
        .AxisTitle.Caption = dist.getDistType

        'Add tick marks
        .MajorTickMark = xlOutside
    End With
End With

Answer 1

Without the data and machine specifics it can be hard to say why this is slow, although here are some alternatives to some of the code you have.

The first and foremost thing I'd change is not to Activate the chart. If you are creating the chart through code, do so but set it to a variable, eg Set wcChart = ThisWorkbook.Charts.Add. Then change With ActiveChart to With wcChart.

Also, delete the FullSeriesCollection and then delete the chart title, remove the gridlines and change the axes before filling up the new data. The chart manipulation should be quicker with less data in the chart. Be careful here though because changing aspects of the chart in different orders can produce different outputs (as an example the layout of a legend).

You fill the new FullSeriesCollection with the entire columns of A and C, specify the exact range of the data rather than the whole column.

Other changes to try, I'm not saying these will work but they are worth a shot if you haven't tried. Instead of checking for a FullSeriesCollection each time:

Do While .FullSeriesCollection.Count > 0
    .FullSeriesCollection(1).Delete
Loop

The following may be quicker:

For ii = .FullSeriesCollection.Count To 1 Step -1
    .FullSeriesCollection(ii).Delete
Next ii

Also, instead of .SetElement for the Chart title and Gridlines I use the following:

'You have to set the title to 'True' before it'll work with 'False'.  Go figure.
.HasTitle = True
.HasTitle = False

.HasMajorGridlines = False
.HasMinorGridlines = False