[mupaco007]

I am new to the world of Excel macros, and I needed to write one for work to process data. The macro basically creates up to four XY-scatter plots of data for each worksheet of data, but the plots each have about 13,000 data points.

There are two columns that get populated which are used to create the graphs. When my macro is running, these columns populate after only a few seconds for the first graph....but then the columns for the second graph will take about 3-4 minutes to come up. Once that finishes, the columns for the third graph can take up to 6-7 minutes to be created. Let's not even talk about the fourth graph. Is there some sort of memory restriction in Excel that is causing the population of the columns for the second, third, and fourth graphs to slow down tremendously? Or does it sound like I'm getting caught in a For loop or an If statement somewhere (a lot of those are used in the code)?

Any help would be greatly appreciated! Thanks!

Edited by mupaco007, 13 December 2006 - 08:36 AM.

[Advertisements]

mupaco007,
I understand your frustration, up until a few weeks ago I had some VBA code in an Access module that took from 30 to 110 minutes dependant on network activity. I found a few simple fixes which brought the time down below 4 minutes.


In the past my excel code has been improved by turning off screen updating and automatic calculation until I was finished.

eg.
Application.ScreenUpdating = False before your code
Application.ScreenUpdating = True after your code

Application.Calculation = xlCalculationManual before your code
Application.Calculation = xlCalculationAutomatic after your code


these links may provide further ideas for you;
http://www.mvps.org/...el/slowresp.htm
http://www.avdf.com/.../art_ot003.html
http://www.cpearson....el/optimize.htm
http://[email protected]


If this doesn't help and you can provide a sample of the data plus code I would be happy to look at it and see if there is anything that I can see


dsm

[dsm]

mupaco007,
I understand your frustration, up until a few weeks ago I had some VBA code in an Access module that took from 30 to 110 minutes dependant on network activity. I found a few simple fixes which brought the time down below 4 minutes.


In the past my excel code has been improved by turning off screen updating and automatic calculation until I was finished.

eg.
Application.ScreenUpdating = False before your code
Application.ScreenUpdating = True after your code

Application.Calculation = xlCalculationManual before your code
Application.Calculation = xlCalculationAutomatic after your code


these links may provide further ideas for you;
http://www.mvps.org/...el/slowresp.htm
http://www.avdf.com/.../art_ot003.html
http://www.cpearson....el/optimize.htm
http://[email protected]


If this doesn't help and you can provide a sample of the data plus code I would be happy to look at it and see if there is anything that I can see


dsm

[mupaco007]

Thank you very much, dsm! That's a nifty little trick there. 4 lines of code, and it cut down the time from about 15-20 minutes total to more like 20-30 seconds. I appreciate the help, and so do my co-workers! Thanks again, and have a great day!