Here is a very simple and easy-to-use tip to make all of your Excel macros run A LOT faster. It is very simple and all that is needed is to turn of "Screen Updating" in Excel.
This will allow your macros to run without having to update the Excel worksheet/workbook after each change the macro makes to that worksheet/workbook. As such, a lot less computing power is required.
Here is the code below:
Application.ScreenUpdating is what controls whether the Excel worksheet/workbook will update after each time the macro affects the Excel workspace. You simply set this to False before the start of your code in the macro and set it to True at the end of your maco.
Make sure to set this to True at the end though or Excel will not update when you make changes in the worksheet by hand too. This is very important not to forget because you will not notice that this is turned-off at first and it may take you hours of trouble-shooting to find out why nothing changes when you change the data in a cell that is linked to other cells in the spreadsheet.
Note that this trick will not have any noticeable affect on macros that already run in the blink of an eye. Where you really see the benefit of turning off ScreenUpdating is when you run a macro that will loop through many cells, rows, columns, sheets, etc. and perform some action on each of them. In that and similar cases, you will notice a HUGE improvement in how fast your macro runs.
This will allow your macros to run without having to update the Excel worksheet/workbook after each change the macro makes to that worksheet/workbook. As such, a lot less computing power is required.
Here is the code below:
Private Sub YourMacro()
' Turn off screen updating
Application.ScreenUpdating = False
'All of your macro code goes here!
'Turn screen updating back on
Application.ScreenUpdating = True
End SubApplication.ScreenUpdating is what controls whether the Excel worksheet/workbook will update after each time the macro affects the Excel workspace. You simply set this to False before the start of your code in the macro and set it to True at the end of your maco.
Make sure to set this to True at the end though or Excel will not update when you make changes in the worksheet by hand too. This is very important not to forget because you will not notice that this is turned-off at first and it may take you hours of trouble-shooting to find out why nothing changes when you change the data in a cell that is linked to other cells in the spreadsheet.
Note that this trick will not have any noticeable affect on macros that already run in the blink of an eye. Where you really see the benefit of turning off ScreenUpdating is when you run a macro that will loop through many cells, rows, columns, sheets, etc. and perform some action on each of them. In that and similar cases, you will notice a HUGE improvement in how fast your macro runs.
没有评论:
发表评论