How to Improve VBA Performance in Word
Optimize VBA performance in Microsoft Word by familiarizing yourself with how much memory different data types, operators and functions use. For example, "integer" variables are automatically converted to "long" variables, meaning it would be wiser when possible to just declare "long" variables instead of "integer" variables. Similar tricks exist for many other VBA components.
Other People Are Reading
Instructions
-
Variables
-
1
Specify a data type whenever you declare a variable, because unspecified variables are assigned a variant type, and this type uses more memory than the other types.
-
2
Utilize a currency data type over the floating-point data type whenever possible. It offers practically the same functionality as the latter, but it can be processed faster.
-
-
3
Take advantage of object variables if you must refer to an object more than once within a module, as it stores the object variable in memory and thus prevents VBA from having to look it up every single time.
-
4
Store array elements in temporary variables before running them through a loop, as it is much slower to retrieve them through an array.
-
5
Declare empty variables by using "vbNullString" instead of double quotes. Since "vbNullString" is a function instead of a string, it can therefore be processed slightly faster.
Miscellaneous
-
6
Reduce concatenation operations when possible by using the "mid" function instead. Keep in mind that the replacement string must be the same length as the substring you intend to replace.
-
7
Loop through a collection with the "for each" statement rather than by using an index. For instance, it is better to type "for each VARIABLE as VARIABLE_SUB" than it is to type "for i = 1 to VARIABLE.count."
-
8
Perform integer division with the "\" integer division operator because the standard "/" floating-point division operator always calculates a double type value.
-
9
Convert string characters to ANSI values when comparing string variables. For instance, the expression "if asc(strText) = 32 then" processes faster than the expression 'if left(strText, 1) = " " '.
-
1
