Turn off screen updating
It is also more efficient to perform all numerical calculations in VBA.
It is frequently tempting to leave formulas in the spreadsheet and call them from the macro.
But, I have written or downloaded versions of Min(), Max(), Average(), Match(), Norm SInv() and St Dev() that are much faster than the Excel versions. Avoid using Variants in formulas Do not declare a numerical variable as Variant unless necessary.
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.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 is primarily focused on Excel VBA macros, however many of these rules apply to Microsoft Access VBA macros as well. Turn off automatic spreadsheet calculation This rule is well known, but it is the most important rule.The impact of leaving automatic calculation turned on can be dramatic.I highly recommend turning off automatic calculation using the following command at the beginning of the macro.