Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 8.5 Accelerate VBA Code

8.5.1 Problem

You've optimized your
forms and queries, but now you need to look at the entire
application. Your application contains a lot of VBA code. What
optimizations can you perform to make it run faster?

8.5.2 Solution

This solution demonstrates seven 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.

To see the optimizations in action, open and run frmShowOptimizations
from

08-05.MDB , shown in Figure 8-14. Click the Run Tests button, and the tests
will run one by one, displaying the results in milliseconds. The
tests compare two different methods of using VBA to achieve a result.

Figure 8-14. The frmShowOptimizations form

Follow these steps to apply the optimizations suggested by these
tests to your applications:

1. When dividing integers, use integer
   division. A majority of the division operations performed by your
   application are probably done on integer values. Many developers use
   the slash (/) operator to divide two numbers, but this operator is
   optimized for floating-point division. If you're
   dividing integers, you should use the backslash (\) integer division
   operator instead. With \, Access works at the integer level instead
   of the floating-point level, so computation is faster. (Of course,
   this is useful only if you're assigning the results
   of the division operation to an integer. If you care about the
   fractional portion of the division, you'll need to
   use floating-point math and the / operator after all.) For example,
   instead of:

   intX = intY / intZ

   use:

   intX = intY \ intZ

2. Use specific data types
   instead of variants. Variants offer convenience at the expense of
   performance. Every time you refer to a variant, Access needs to
   perform type conversion to ensure the data is in the correct format.
   By using the data type that matches your variable, you eliminate the
   need for this 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.

3. Test for blank strings using the
   

   Len function. You probably have code that tests
   for blank strings by comparing them to an empty string
   ("). However, because Access
   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

   use:

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

4. 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, Access has to perform some work to figure out which object
   you are referring to. This adds overhead to your code each time the
   object is referenced. But if you assign the object to an object
   variable, Access "finds" the object
   once and caches the reference in memory. So 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:

   Dim strTmp As String
   Dim lngCount As Long
   For lngCount = 0 To acbcMaxIterations / 2
   strTmp = DBEngine.Workspaces(0).Groups(0).Name
   Next lngCount

   use:

   Dim grp As DAO.Group
   Dim strTmp As String
   Dim lngCount As Long
   Set grp = DBEngine.Workspaces(0).Groups(0)
   For lngCount = 0 To acbcMaxIterations / 2
   strTmp = grp.Name
   Next lngCount

   We
   created two variations of this test. First, we changed the function
   to refer to a control on an open form instead of a DAO group. The
   cached reference version of the code was 2.8 times
   fastersignificantly improved, but not of the same magnitude as
   the DAO group comparison. Second, we compared using an object
   variable against using the VBA With...End
   With construct (without an object reference).
   With...End With was slower than
   using an object variable, but still much faster than using neither an
   object variable nor With...End
   With.

5. Don't worry about comments. In VBA the use of
   comments exacts no measurable performance penalty, so
   there's no excuse for omitting them!

6. Use
   If...Then...Else instead of the
   

   IIf function. By replacing
   

   IIf statements with the equivalent
   If...Then...Else statement, your code will run
   faster. For example, instead of:

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

   use:

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

7. When initializing a string so
   that it's empty, don't use a
   literal value ("). Instead, use
   the built-in vbNullString constant. You'll get
   better performance, as the test demonstrates.

8.5.3 Discussion

Many optimizations that apply
to other languages can also apply to VBA. For example, checking for
blank strings using the

Len function is a common
optimization in other languages. Don't be afraid to
try new techniques. The small performance improvements you get from
optimizing VBA code can add up if you are running code in a
repetitive loop, and many small improvements may result in a
noticeable overall difference in your application.

Optimization
techniques for programming languages are a vital part of your
toolbox. But don't sacrifice other vital elements
for the sake of speed. First, make sure your code works correctly
before you optimize. Second, write your code so that
it's easily understood; it can be very difficult to
optimize code you don't understand. Finally,
don't break working code when optimizing it. By
optimizing code that works correctly (albeit slowly), you may
introduce bugs. Follow the three rules of optimization:

• Make it right before you make it faster.

• Make it clear before you make it faster.

• Keep it right as you make it faster.

You may find that there are no easy optimizations for a particular
piece of code. No matter what you do, it just won't
run fast enough. A favorite saying in software design is
"Don't diddle code to make it
faster; find a better algorithm." Often you need to
step back from a piece of slow code. Maybe there is a better overall
approach or a better algorithm you can employ. A good way to get over
a hurdle such as this is to ask other programmers how they handle the
same situation. Overall, you will find that code optimizations have a
much smaller impact on your application's
performance than optimizations to your data access; for example,
adding one extra index can have a greater impact than hours and hours
of time spent optimizing VBA.

As they say in the auto commercials, "Your mileage may vary." Don't assume anything is faster until you've proven it yourself on the machine that will run your application!