Nocal
04-15-2011, 10:39 AM
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
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