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.
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.
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.
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.
This article lists my top rules for speeding up VBA.
The second command only recalculates a specific sheet.
The third command only recalculates a specific Range.
Note that this rule also applies to any functions you write.
Based on my tests the variable types from fastest to slowest in mathematical equations are: Constant, Single, Double, Long, Integer, Variant. Avoid evaluating Strings Strings (text) are slow to evaluate.
Variants are very flexible because they can be numerical or text, but they are slow to process in a formula.