esudden extreme slowdown of charting

  • Thread starter Thread starter --lzp
  • Start date Start date
L

--lzp

I've used a simple macro in Excel 2003/Windwos XP SP3 to create two x-y
charts, one with three x-y pairs based on max. 6000 rows; here is the start:

Dim ws As Worksheet
Set ws = ActiveSheet
ws.Select
ws.Name = "Data"
Application.ScreenUpdating = False
Sheets("Data").Select
Range("B3").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("Data").Range("A1:X3"),
PlotBy:= _
xlRows
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Data!R8C2:R6000C2"
ActiveChart.SeriesCollection(1).Values = "=Data!R8C9:R6000C9"
ActiveChart.SeriesCollection(1).Name = "=Data!R7C9"
ActiveChart.Location Where:=xlLocationAsNewSheet

Everything worked perfectly until recently (the charts would be created in
~1 sec) until Excel 2003 had to be reinstalled by IT due to a corrupted file.
Ever since, the macro started running exceedingly slow, up to 10 min, to
create two simple charts! Re-installation would not help, and the Core Due
processor show 60% load during that time. We can't find any way to fix it
(rewriting, reinstallation etc. would not help), but Fn-Break always shows
the program stopping at one of the ActiveChart.SeriesCollection(1).Values
lines of code...

Any idea what may be causing this behavior and how to fix it?
 
Such problems you describe are hard to solve. What I would do:

Try the program on another PC and see if it runs dead slow. If it does,
the problem is the code. If it runs fine, the problem is something on your
PC. For the first, you would have to post code or have someone look it
over. For the second:

Un-install Google desktop if it has gotten installed on your PC
Un-install any applications that are loading at startup that are not
absolutely needed.
Un-install any COM add-ins (do first so Excel doesn't remember them). Do by
using the buttson on the
http://www.add-ins.com/addins_information_lister.htm. Also un-install any
regular add-ins.
Un-install Office
If you have installed a test version of Excel 2010 un-install it
Run the Temp File Deleter found at
http://www.add-ins.com/temp_file_deleter.htm
Reboot
Re-install Office
Run the Temp File Deleter again
Run the latest service pack
Run the Temp File Deleter again
Go into Office and do a Repair under the Help menu or Diagnostic in Office
Button Resources if 2007
Add back only absolutely needed add-ins

Knowing what cures your problem is always important. Please post back and
let us know if any of the above helps and what you find that solves the
problem.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
Thanks for a clearly delineated problem-tracking procedure. I have already
tried a few intial steps, like cleaning all temp xla and xll files, removing
all add-ins form the path, re-pasting and re-installing only the offending
macro and trying to tun it on a new PC; nothing helped. I've tried to run
the macro in Excel 2007, but there are too many incompatibilities between the
two versions (an amazing fact on its own; the macro is not very complex at
all...)

I'll try to get the IT guys to follow the rest of the suggested steps--and
report if successful.
 
Back
Top