Macro slowing down with iteration - help needed

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

I have a macro which sequentially calls up spreadsheets
from a folder then runs a macro in each sheet which
involves iteration of the fourier transform function
about 1,000 times in each.

This works well for the first 10 spreadsheets, then seems
to progressively slow down, eventually running only at
snail's pace. I presume this is because a memory buffer
fills up.

Is there any way I can clear the memory buffer after each
spreadsheet, or any other way around this problem?

Thanks for any advice.

Peter
 
Hi Peter,

I assume you are closing each workbook after you have calculated it?

make sure you are destroying any object variables you create.

Otherwise you would probably have to close and restart Excel every 10
spreadsheets or so.

Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com
 
Hi Charles

Thanks for your response.

Yes each workbook is closed after recalculation.

How would I make sure object variables are destroyed?

thanks again

Peter
 
Hi Peter,

If you had 3 object variables: a range, a sheet and a book then something
like:

Set oRange=Nothing
Set oWksheet=nothing
set oWkBook=nothing

Destroy in inside-to-outside sequence: range before sheet etc

Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com
 
Back
Top