Macro Slows with the same data...

[Nocal]

Hi all,

New here so I'm just getting to grips with VBA. Be gentle ! ;o)

Using Excel 2007 I have a VBA macro that manipulates data in a Table. I have tested the speed of the macro and when I first open the workbook it takes about 5 second to run. If I run it again and again, even using the same data, the macro slowly gets slower and slower. After running it 20 times it's up to about 13 seconds. There are not a lot of workbook functions in the sheets, the code does not use any 'objects' that should be set to 'Nothing', the Excel.exe service uses about 60,000K. I need this marco to iterate quickly through about 500 times. Autocalulate is turned off. Each iteration it copies over the table with the orginal table and starts again. The table is sorted in a few different ways and a few rows added to it. The table is not big - just an 8 by 100 table in a worksheet. I have even turned off all add-ins. Still the same.

I'm at the beginning of a project that will grow this workbook. If it's gets slower and slower at each iteration at the early stage things will get very difficult.

I declare all variables and there are no global or module variables.

Any suggestions ? It's driving me crazy !!!

Any help or idea appreciated !!

C

[Kenneth Hobs]

Welcome to the forum!

Screen updates slow it as well. For that and other tips see: http://www.vbaexpress.com/kb/getarticle.php?kb_id=1035

Coding can cause those issues as well. Recorded code will use Select methods where selecting is not always the most efficient method.

[Nocal]

Hi Kenneth,

Many thanks for the reply. Unfortunately I have already turned off automatic calculations and I have turned off screen updates.

It's very strange. The code does exactly the same thing to the table each time I run the macro. Every time I run the macro it gets about half a second slower than the previous time. So, when I open the workbook it runs in 5 seconds. After running the macro 10 times it now takes 10 seconds. To run it in 5 seconds again I just need to close the workbook and open it again. I don't need to close and open Excel itself.

I'm scratching my head ...

If the code would run in a fixed 8 seconds I would be happy!

Thanks again !

Conal

[Paul_Hossler]

Can you post a sample workbook, or at least the macro?

Paul

[frank_m]

I had that issue before and was able to resolve it by using the command ActiveSheet.DisplayAutomaticPageBreaks = False before the routine runs.

It's a long shot, but worth a try.