Adding Graph Using VBA - Extremely Slow

[HedgePig]

I'm adding a graph to a worksheet using VBA. It's working...but it (usually) seems to be extremely slow as it takes around 20 seconds

   Dim newChartObj As ChartObject
   Dim newChart As Chart
   Set newChartObj = ActiveSheet.ChartObjects.Add(endRange.Left, endRange.Offset(2, 0).Top, 360, 240)
   Set newChart = newChartObj.Chart
  
   'Now add all the chart elements
   With newChart
   'Add titles
   'Add data series, etc.
   End With

It's the third line (Set newChartObj = ...) where VBA usually hangs for around 20 seconds before finally inserting the chart. I then go on to add titles, insert the data series, etc. and this seems to work quickly without any problem.

The spreadsheet itself is quite large (22Mb) as it contains a lot of data but not huge and I have no problems adding graphs manually.

Any suggestions as to why VBA might be taking so long to insert an empty chart?

 

[mikejvir]

I had problems a long time ago when Excel was completely rewritten. Assuming you are using Windows, try setting the default printer to a PDF printer. This may or may not work.

Mike

 

[HedgePig]

Hello Mike

Yes, I'm using Windows. That's a pretty bizarre suggestion - why would the run time be affected by the default printer, especially if no printing is involved?

And yes, I've tested it just in case, and no, it makes no difference. But I'm really curious as to why you thought it might?

Regards
HedgePig

 

[mikejvir]

Hello Mike

Yes, I'm using Windows. That's a pretty bizarre suggestion - why would the run time be affected by the default printer, especially if no printing is involved?

And yes, I've tested it just in case, and no, it makes no difference. But I'm really curious as to why you thought it might?

Regards
HedgePig

They may have fixed it, but back when I transitioned from Excel 2003 to Excel 2007. This was around 2009 and there was a lot that did not get translated properly for this release (e.g. the Macro recorder did not work) because it was a major rewrite (1 Million rows). What happen (for me) is my macro generated a graphs. Track part of the problem down to the printer with the help of the forums.
Sorry it did not work out.
Have you tried
Application.ScreenUpdating = False ' True