Access Cookbook

Accelerate VBA Code

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?

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

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 ...