Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart Select/Copy Performance
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart Select/Copy Performance
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. "MikeT" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart Select/Copy Performance
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. "MikeT" wrote in message ... 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. "MikeT" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart Select/Copy Performance
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/ _______ "MikeT" wrote in message ... 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. "MikeT" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you create an importance-performance chart in excel 2003? | Charts and Charting in Excel | |||
web performance metrics chart help | Charts and Charting in Excel | |||
Select every other Row to copy/cut???? | Excel Discussion (Misc queries) | |||
Select copy | Excel Discussion (Misc queries) | |||
Excel Automation - Chart Performance | Excel Programming |