4.9 Clean Test Data out of a Database When You re Ready to Ship It | Access Data Analysis Cookbook (Cookbooks)

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 15-fold increases 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

figs/acb_0814.gif

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

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
```
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.
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
```
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 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 faster significantly 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 6 times slower than using an object variable, but still much faster than using neither an object variable nor With...End With.
Don't worry about comments. In VBA the use of comments exacts no measurable performance penalty, so there's no excuse for omitting them!
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
```
Don't use fixed-length strings. Although it was faster to use fixed-length strings than variable-length strings in Access 2.0, in subsequent versions fixed-length strings are almost twice as slow as variable-length strings.

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!