Extremely slow macros in Excel 2007

[wouter.vanmeert]

Hi there!


I've recently updated my excel 2003 to version 2007. Since the update, macros in an exceldashboard I created run extremely slow.


The dashboard consists of 2 types of pages:

1. Data pages that are updated monthly (just using links to other files).

2. The dashboard with a number of dynamic charts (only line and bar charts and combinations of both).


Using named ranges and offset, I give users the possibility to expaned the time range of my graphs (select a month they want and then see a historical evolution of a number of months of a given variable). The macros change the named ranges in the dataseries in my charts.


Since the update to excel 2007 themacros that manipulate these graphs run extremely slowly.


I don't think the problem is that my laptop is too slow, see the specs below:

Windows XP Professional

Intel Core i 3-2310M CPU @ 2GHz

795 MHz, 3.16 GB RAM


P.S. I'm a bit of a VBA beginner, so one of the problems could be that there's a lot of unnecessary code in the macros as I've recorded them. Still, manipulation of the graphs used to take about 1 or 2 seconds and will now take up to 30 seconds...


Has anyone encountered this kind of problem? Any help would be very much appreciated!

 

[Hui]

Check that all links are valid, slow or missing links really slow things down

See if there are spare or unused Named Formula or Named Formula linking to non-existent files

Try the file on an Excel 2010 PC, 2007 was a poor version.

 

[wouter.vanmeert]

I've removed useless links and named ranges. It helped a little, but the problem hasn't been solved completely yet. I still have to test the file on Excel 2010 though. Thanks for the quick reply and the help!

 

[Hui]

If you have too many Data Tables you may want to remove a few of them also

That is Data, what Ida, Data Tables. Not normal data tables

 

[coke2011]

wouter.vanmeert:

> 1. Data pages that are updated monthly (just using links to other files).

btw these files placed in your laptop or somewhere in local network?


i think you can open macro and insert some debug messages in suspect places

like:

Debug.Print ("mark 1")

...

Debug.Print ("mark 2")

...

Debug.Print ("mark 3")


then open Immediate window (Ctrl+G) and start macro

you will find out place where your code slow down

then you can dig deeper inside functions etc

 

[Hui]

You can also step through the macro lone by line and see where it is slowing down

If it's that slow you should be able to spot it

Set a break point F9 just inside the start of the code and then step through it line by line F8

 

[wouter.vanmeert]

I cleared up some of the excess VBA code and the delay is a lot less now. Thanks a lot for the tips!