Macro slowing down with iteration - help needed

[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

 

[Charles Williams]

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

 

[Peter]

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

 

[Charles Williams]

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