Removing auto-calc – You can switch off auto calculations in Excel manually by switching your calculation options to manual (found under tools menu in Excel 1997). Another strategy would be to switch this off within your code through the command Application.Calculation = xlCalculationManual. Be sure to include the command Application.Calculation = xlCalculationAutomatic at the end of your code to avoid user confusion when the calculations do not perform after the script is run. When testing you need to remember this will switch to manual calculations will remain in affect long after you have finished your testing.
Removing refresh – Excel is constantly refreshing your screen. When running a macro, it is not necessary to display your results after each step that is taken. Every second that Excel attempts to refresh your screen to show your latest results is a second taken away from processing your script. To remove this anchor you will need to switch this off through the command Application.ScreenUpdating = False and switch it back on at the end of your code with the command Application.ScreenUpdating = True.
Declaring Variables - When using scripts that contain variables such as for I = 1 to 100 you can improve your speed by declaring that variable first. Declaring I as an integer will tell your computer what to expect when it encounters this variable I and wont reserve too much memory by treating it as a potential 256 character variable.
No comments:
Post a Comment