Chart Select/Copy Performance

[MikeT]

Hi!

I would like to select 8 embedded charts on one sheet, then copy and paste
them to another sheet. I am experiencing severe performance issues with this
code:

Sheets("Templates").Select
ActiveSheet.Shapes.Range(Array("Chart1", "Chart2", "Chart3", "Chart4", _
"Chart5", "Chart6", "Chart7",
"Chart8")).Select
Selection.Copy
Sheets("Results").Select
ActiveSheet.Range(ChartColumn & ChartRow).Select
ActiveSheet.Paste

The code works fine (eventually), but the "Selection.Copy" statement takes
about 10-15 minutes to complete. I have to loop through between 20 and 40
results sets, so updating the workbook takes 3-6 hours! The charts are
combination charts that plot scattered points in one series, and a line
through the points in another series. Also, the charts are small, only about
2" x 2" with only 10 points plotted per chart. I turned off the
AutoFontScaling, having hit the limit there.

What can I do to improve performance? I need the entire process to take no
more than 3-6 seconds, not 3-6 hours!

Thanks in advance for any advice and suggestions.

 

[MikeT]

Additional info - I monitored my Local Settings/Temp folder while excuting
the code below. Executing the code created 120MB in 9 new files!
Incredibly, the entire workbook is only 8MB. This seems rather inefficient.

 

[Tim Williams]

try

application.screenupdating=false
application.calculation=xlmanual

'do stuff

'set back screenupdating/calculation

If you don't need "live" graphs then you could try the chart's "copypicture"
method (and so just paste a picture of the chart in the other sheet) instead
of copying the complete graph...
Sounds like something wierd is going on with your workbook though: have you
tried copying the charts individually?


Tim.

 

[Jon Peltier]

Your code is doing a lot of selecting and activating and other inefficient
actions. You can generally avoid most of these.

For fun I also did a little test of how quickly different objects are
copied. The code is at the end. I compared copying a chart object, the chart
area within it, and the shape containing the chart, and I tested on an
active sheet and on an inactive sheet. For each combination I ran 50
repetitions and output the total in the Immediate Window. The results:

ActiveSheet ChartObject.Copy 61.28515625
ActiveSheet Chart.Copy 0.140625
ActiveSheet Shape.Copy 55.98046875
Inactive Sheet ChartObject.Copy 61.71875
Inactive Sheet Chart.Copy 0.140625
Inactive Sheet Shape.Copy 58.22265625

There's not too much difference between active sheet and inactive sheet, so
there's no benefit to activating a sheet, and actually a price paid in terms
of Excel doing the activation and redrawing the screen. There's not much
difference between chart object and shape, which makes sense, since they are
two ways to refer to the same thing. The important thing I learned is that
copying a chart is hugely faster than copying a chart object or shape: it
takes 400 times as long to copy a shape or chart object! The chart I tested
is pretty simple, a one-series column chart with three points.

I tested a more complex XY chart, 26 series (A to Z) and 1000 points each.
Fearing that it would take longer, I only ran ten repetitions.

Here is the result of ten reps of the simple chart:
ActiveSheet ChartObject.Copy 11.15625
ActiveSheet Chart.Copy 0.03125
ActiveSheet Shape.Copy 12.7578125

Here is the result of ten reps of the complex chart:
ActiveSheet ChartObject.Copy 146.44921875
ActiveSheet Chart.Copy 0.05859375
ActiveSheet Shape.Copy 145.1796875

Now the difference between copying a chart and the shape is a factor of
2500!!

Now, pasting. Let's save some time here too. Let's not activate the target
sheet and select the target cell. We can use Worksheets("Sheet4").Paste to
paste the chart, and it goes wherever the active cell is. Then we can move
the chart. You already knew where you wanted it, because you selected a cell
prior to pasting the shaperange. So simply use something like this to locate
the chart:

With Worksheets("Sheet4")
.Shapes(.Shapes.Count).Left = .Range(ChartColumn & ChartRow).Left
.Shapes(.Shapes.Count).Top = .Range(ChartColumn & ChartRow).Top
End With

You'll have to calculate ChartColumn and ChartRow for 8 charts, not 1
shaperange, but you've saved vital fractions of a second (or seconds, or
more) by changing the copy method, and VB's pretty quick at math.

Code to measure Copy speed:


Sub TestCopyTime()
Dim i As Integer
Dim t As Double
Const iMax As Integer = 10 ' 50

t = Timer
For i = 1 To iMax
Worksheets(1).ChartObjects("Chart 1").Copy
Next
Debug.Print "ActiveSheet ChartObject.Copy " & Timer - t
t = Timer

t = Timer
For i = 1 To iMax
Worksheets(1).ChartObjects("Chart 1").Chart.ChartArea.Copy
Next
Debug.Print "ActiveSheet Chart.Copy " & Timer - t
t = Timer

For i = 1 To iMax
Worksheets(1).Shapes("Chart 1").Copy
Next
Debug.Print "ActiveSheet Shape.Copy " & Timer - t

t = Timer
For i = 1 To iMax
Worksheets(2).ChartObjects("Chart 2").Copy
Next
Debug.Print "Inactive Sheet ChartObject.Copy " & Timer - t
t = Timer

t = Timer
For i = 1 To iMax
Worksheets(2).ChartObjects("Chart 2").Chart.ChartArea.Copy
Next
Debug.Print "Inactive Sheet Chart.Copy " & Timer - t
t = Timer

For i = 1 To iMax
Worksheets(2).Shapes("Chart 2").Copy
Next
Debug.Print "Inactive Sheet Shape.Copy " & Timer - t

End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______