Turn off screen updating
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.Note that if you choose not to use "Option Explicit" at the beginning of the macro any undefined variable will be a Variant.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.Avoid evaluating Strings in code like this: Enumeration assigns a constant numerical value to a variable.VBA can process enumerated values quickly while maintaining readable code.
Avoid reading or writing worksheet data within loops whenever possible.I had naively assumed that common worksheet functions would be efficiently processed by VBA. For example, most VBA users are probably aware that VBA does not have a Max() or Min() function. It is common to use the following code that uses the Excel version of Max(): I found an open source version of a VBA Max() function on the Internet.It was 10 times faster than the Excel based counterpart above. I concede that the function below only works with two arguments and does not support arrays, but the improvement in speed is substantial.I suggest caution when using worksheet functions in large, time consuming macros.You should evaluate the impact of rewriting the function.