Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Excel Macro slows up fast!


  • Please log in to reply

#1
mupaco007

mupaco007

    Member

  • Member
  • PipPip
  • 14 posts
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.

  • 0

Advertisements


#2
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
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://groups.google...@lure.pipex.net


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
  • 0

#3
mupaco007

mupaco007

    Member

  • Topic Starter
  • Member
  • PipPip
  • 14 posts
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! :blink: Thanks again, and have a great day! :whistling:
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP