Improve VBA code performance

[Rudi]

Hi,

I recieved this email requesting help, and want to get your advice on it before I send an answer....

I wrote a rather large macro, but now whenever I run the macro, my computer struggle with performance and keeps on giving me messages such as ‘Excel cannot complete this task with available resources. Choose less data or close other applications’.

My macro consist out of a lot of formulas, but also opening and closing files in order to get data from fix reports and a lot of copy and paste values.

Is there any code I can use to speed up my performance?

I have already change the screenupdating=false and tried to ‘save’ more often, but it still seem to ‘bomb' out.

Would clearing variables help in this case too?

TX

[HansV]

It may help to set

Application.Calculation = xlCalculationManual
Application.EnableEvents = False

at the beginning, and

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

at the end of the macro.

[Rudi]

If there is looping with open close workbooks would there be a case of : Set Variable to Nothing

Could this also free up resources within a loop?

TX

[HansV]

I doubt it, since the variable(s) would be assigned a value again immediately afterwards, in the next iteration of the loop. But it won't hurt to try.

[rory]

If there's a lot of copy and paste going on, he/she may want to set Application.CutCopyMode = False after pasting each one. (or just do a direct value assignment instead.)

[Rudi]

Thanks for your input guys. I'll forward the advice and see if it helps this persons code.
Cheers!