Word Hacks [Electronic resources] نسخه متنی

Hack 64 Optimize Your VBA Code

As you create more complex macros, minor delays
caused by poor coding can really add up. This hack will help your VBA
code run faster.

This hack demonstrates six specific programmatic techniques you
can apply to accelerate your code. The improvement can range from
modest increases to increases of several orders of magnitude in
performance.

7.9.1 Use Integer Division

Your application probably performs a majority of its division
operations on integer values. Many developers use the slash
(/) operator to divide two numbers, but this
operator is optimized for floating-point division. If you divide
integers, you should use the backslash (\)
operator instead. With \, Word works at the
integer level instead of the floating-point level, so computation
happens faster. (Of course, this is useful only if you assign the
results of the division operation to an integer. If you care about
the fractional portion of the division, you need to use
floating-point math and the / operator.) For
example, instead of:

intX = intY / intZ

use:

intX = intY \ intZ

7.9.2 When Possible, Avoid Variants

Variants offer convenience at the expense
of performance. When you use
variants, Word often needs to perform type conversion to ensure the
data is in the correct format. If you match the data type to your
variable, you eliminate the need for type conversion, and your code
runs faster. In addition, a variant variable is twice as large as an
integer (on a 32-bit operating system), and thus takes longer to
manipulate.

7.9.3 Test for Blank Strings with Len

You probably have code that tests
for empty strings by comparing them to another empty string
("). However, because Word stores the length of
the string as the first byte in the string, testing for a length of
zero using the Len function is always faster.
Instead of:

If strTemp = " Then
MsgBox "The string is blank"
End If

you can use this:

If Len(strTemp) = 0 Then
MsgBox "The string is blank"
End If

Don't use a literal value (")
when you initialize a string. Instead, use the built-in
vbNullString constant.

7.9.4 Assign Objects to Variables

If you refer to an object more
than once in a section
of code, assign it to an object variable. Every time you reference an
object, Word has to perform some work to figure out which object you
are referring to. This adds overhead to your code each time you
reference the object. But if you assign the object to an object
variable, Word "finds" the object
once and caches the reference in memory. After the first reference,
you can refer to the object through the object variable, and your
code will run faster. For example, instead of this code:

Sub ReferencingTestSlowWay( )
Dim k As Long
Dim str As String
For k = 1 To 100000
str = ActiveDocument.Paragraphs(1).Range.Characters(1).Text
Next k
End Sub

you can use this:

Sub ReferencingTestFastWay( )
Dim k As Long
Dim str As String
Dim rng As Range
Set rng = ActiveDocument.Paragraphs(1).Range.Characters(1)
For k = 1 To 100000
str = rng.Text
Next k
End Sub

The difference between the two versions is dramatic: with a 2.6-GHz
Celeron processor, the first macro took 62.16 seconds; the second
took just 0.26 seconds.

7.9.5 Don't Skimp on Comments

Don't worry about
comments.
In VBA, the use of comments exacts no measurable performance penalty,
but they will help you (and others who might use the code) understand
how your code works.

7.9.6 Avoid IIf

Replace the IIf function with If...
Then... Else to make your code
run faster. For example, instead of:

MsgBox IIf(intX = 1, "One", "Not One")

you can use this:

If intX = 1 Then
MsgBox "One"
Else 
MsgBox "Not One"
End If

Adapted from Access Cookbook
(O'Reilly)