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
Improve VBA code performance
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Improve VBA code performance
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Improve VBA code performance
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.
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
at the beginning, and
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
at the end of the macro.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Improve VBA code performance
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
Could this also free up resources within a loop?
TX
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Improve VBA code performance
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: Improve VBA code performance
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.)
Regards,
Rory
Rory
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Improve VBA code performance
Thanks for your input guys. I'll forward the advice and see if it helps this persons code.
Cheers!
Cheers!
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.