VBA Tips for improving speed

This sticker has been transferred from the EH forum, I have modified myself

[Editor's note] speed is the most popular topic of programming, although the speed of the language can be used to learn from each other, but the difference is sometimes very large, such as VC because of the way the string storage determines the use of the Len function is faster, similarly, because C built up most of the API functions, Using API functions can also be faster. In addition, from different angles, often will produce or draw a different effect and orientation, all these, require the program personnel in the practical application to be flexible, there is no absolute can or not. Here is a collection of some of the most commonly used speed-up techniques, for everyone's reference.
First, let the code "focus" run
1. Application.ScreenUpdating = False Turn off screen update
2. Application.enableevents = False Disables triggering of events
3. Application.interactive = False Disables interactive mode
The first sentence we all know to use, nothing to say, the second sentence is sometimes necessary to use, but also can not use the situation, remember the use of Oh, the third sentence is not to be noticed, although the effect is not as obvious as the previous two sentences, but also has a certain effect.
Regardless of which one you use, remember to change it back to true at the end of the program.
Second, the selection of the cell (area) notation
There are 3 types of cells (regions), with A1 cell as an example, you can use
1. Cell Object notation: cells (+)
2. Range Object notation: Range ("A1")
3. Name wording: [A1]
There are advantages and disadvantages to each of the three formulations, but with the speed theory, cells (max) is the quickest, [A1] is the slowest, and Range ("A1") is slightly slower than cells (the advantage is that you can enter information quickly in the VBE to display property methods, etc.).
In addition, to express object properties, this is a good programming habit, do not rely on the object's default properties, the following two sentences, the second sentence is much faster than the first sentence:
x= cells (a)
x= cells (a). Value
Third, comparison statements
1. Explode if statement
1) IF A and B then write
IF A Then
IF B Then
(2) IF A OR B is written
IF A Then
...
ELSE IF B Then
Or
IF A then GOTO D1
IF B then GOTO D1
GOTO D2
D1:
....
2. Side-by-side conditions use ElseIf or Select,elseif to write up trouble, but faster than select
3. Put the most probable and easy-to-calculate conditions ahead
1) The above decomposition of a should be greater than the chance that B appears, or need to calculate more easily calculated results
2) SELECT statements in the previous case should also be
4. IIf speed is slower than if/else
Iv. Circular Statements
1. The For statement is faster than Do/while, because the increment portion of the For statement is the VBA self-contained, especially with a step size of 1.
2. For/each faster than for/to
V. Using the WITH statement
The WITH statement is not just a simplified notation, but also a great increase in speed.
Six, try not to use variant type
Needless to say, using Option Explicit is a good habit.
A definite type of return value will be good for speed-up. Although sometimes we find it not obvious, this is why many people use left$ instead of left, using int% instead of int (Are you curious about this type of writing?). ）。 But if you don't have a dim at first, it's necessary.
Seven, focus on memory
For object variables that consume more memory, don't remember set=nothing
If you have no confidence in the capacity of your machine's memory, you should avoid using recursive procedures, which can consume a lot of memory.
Viii. using Worksheet functions (methods)
Some worksheet functions (methods) are very fast, such as find,vlookup and so on, remember to use them, do not spend the effort to do not please.
When working with worksheet functions, the operands should avoid using memory variables, which is slower.
Nine, zero for the whole
When you need to do the same for a larger area, you can use a method such as union to collect it once. In the Range object is particularly obvious, the content, formatting changes, deletions, and so on one of the processing is much slower than the selection after processing a lot more slowly.
X. Using an array of memory
1. Memory variables operate much faster than the Range object.
1) write the range data to an array of memory. The following two sentences will generate an array of 65536 rows and 6 columns. Arrays produced in this way are all two-dimensional arrays, even if the referenced range has only one row or column. The subscript starts at 1 and is not affected by the option Base setting. Arr must be declared as a variant type.
Dim arr ()
Arr=range ("a1:f65536")
2) write memory array data to range. After the memory array has been computed, it can be written back with just the next sentence.
Range ("a1:f65536") = arr
2. Non-array variables are faster than array variables. When the array is large, extracting the values from the subscript is much slower than from a single variable, so you can assign the array values you want to use multiple times to the memory variables first.
3. Reduce the number of times you use ReDim. ReDim is the most time-consuming action in an array operation. You can adjust the budget first, not enough, or extra.
Xi. set the calculation mode to manual
Application.calculation.=xlcalculationmanual
Sometimes, worksheets have many formulas, and when the value of a single calculation mode is changed to prompt, all recalculation is done, so the calculation mode is changed to manual, and the speed can be increased on the other hand.

VBA Tips for improving speed