Minimize traffic between VBA and the worksheet Application.ScreenUpdating = FALSEĪt the end of the macro use the following command to turn screen updates back on. The following command turns off screen updates. Refreshing the image is a considerable drag on performance. Application.Calculation = xlCalculationAutomatic.Įvery time VBA writes data to the worksheet it refreshes the screen image that you see. If the macro ends prematurely before this command is processed, you will need to manually reset calculation to automatic in EXCEL. When the macro is done, automatic calculation needs to be turned back on using the following command. The third command only recalculates a specific Range. The second command only recalculates a specific sheet. The first command recalculates the entire workbook. If you need to recalculate spreadsheet values while the macro is running you can use any of the following commands. Application.Calculation = xlCalculationManual I highly recommend turning off automatic calculation using the following command at the beginning of the macro. The impact of leaving automatic calculation turned on can be dramatic. If the macro is writing values into the worksheet, VBA will need to wait until the worksheet is done recalculating each entry before it can resume. When a new value is entered into a worksheet cell, Excel will recalculate all the cells that refer to it. This rule is well known, but it is the most important rule. Turn off automatic spreadsheet calculation Please note that my estimates of time savings below may vary significantly for your specific application. Rules six through 11 have a marginal impact. The first five rules generally have the largest impact on macro performance. This article is primarily focused on Excel VBA macros, however many of these rules apply to Microsoft Access VBA macros as well. It is easy to lapse into bad programming habits when working with small macros, but with large macros and macros that run a long time it is critical to use efficient coding. This article lists my top rules for speeding up VBA.

As the proud owner of several large VBA macros, I have spent a considerable amount of time looking for ways to make macros run faster.

Vba auto generate code